Re:Temporal: Getting started with JavaScript's new date time...
by branko_d on Tuesday June 29, 2021 @01:18AM
(#27672087)
It's better to use a native date/time with a CHECK that ensures that the time portion is 0.
You'll have to do a CHECK on the YYYY-MM-DD string anyway to protect your database from bad data, so why not just do it on the native type?
The native type is likely to be more compact, performant, consistent across the database (with strings, some columns may be YYYY-MM-DD, some may be YYYY.MM.DD etc.) and more readily convertible to the client's type system.
You'll have to do a CHECK on the YYYY-MM-DD string anyway to protect your database from bad data, so why not just do it on the native type?
The native type is likely to be more compact, performant, consistent across the database (with strings, some columns may be YYYY-MM-DD, some may be YYYY.MM.DD etc.) and more readily convertible to the client's type system.