5 ms·

I inherited a PHP/SQLite project years ago. I found timestamps with three different formats in a column because different PHP pages had different ideas of what

I inherited a PHP/SQLite project years ago. I found timestamps with three different formats in a column because different PHP pages had different ideas of what a timestamp is. OK, the problem was the original developer that wrote all of that code but the database made it too easy to write that horror. I wish they add a real strict type for dates and times too.

simonw 5y ago
Ouch, that sounds nasty! There's a pattern for enforcing datetime formats using a CHECK constraint which I've not tried myself yet but which looks like it could work: https://sqlite.org/forum/forumpost/4f4c96938f4bef32?t=h https://sqlite.org/forum/forumpost/4f4c96938f4bef32?t=h
Cyberdog 5y ago
That's hardly unique to SQLite. I'm a huge fanboy of SQLite, though I have to use MySQL in pretty much all of my client work since I more often get hired to work on existing projects than start new ones. There's three common ways to store timestamps in MySQL and they all stink; - Use an integer column with a Unix timestamp. Not human-readable and no sub-second accuracy if you need it (unless you store the time in milliseconds instead of full seconds, requiring larger int columns, or just get completely crazy and use a float field of some sort. - Use a `datetime` field. Human-readable, supports milliseconds natively, and supports any time between years 1000 and 10,000, but doesn't store a time zone - so if you define the column with something like `'created' TIMESTAMP NOT NULL DEFAULT current_timestamp`, who knows what will actually be inserted in that field when the row is created? - Use a `timestamp` field. Like datetime fields, you get a human-readable field with sub-second precision, but the range is only from the Unix epoch to Y2K38, which is seventeen years away now. These fields store the date unambiguously as UTC - hooray! But… when you retrieve the date, the time will be adjusted "back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis." You can make an unwieldy cast to get the date as UTC - `SELECT CONVERT_TZ(created, @@session.time_zone, "+00:00") AS created_utc` - but obviously this is easy to forget about and performs an unperformant double-cast. All three of these options suck in their own distinct ways. All this rant is to say that date storage in at least two of the most common databases is a bit of a mess right now (it's been long enough since I've used Postgres that I can't recall if things are any better there, but I'm sure one of its fanboys will let us know soon enough if it is). So don't get too hard on your previous developer or blame SQLite in particular.
- throwaway2037 5y ago
  I also struggle with datetimes. One idea that is much worse by space is store UTC only as string in this format: A date-time without a time-zone in the ISO-8601 calendar system, such as "2007-12-03T10:15:30". (See: https://docs.oracle.com/javase/8/docs/api/java/time/LocalDateTime.html https://docs.oracle.com/javase/8/docs/api/java/time/LocalDat...) I'm pretty sure that format is also string comparable for sorting.
- SPBS 5y ago
  > There's three common ways to store timestamps in MySQL and they all stink I used to think that, but now I realize those problems would disappear if you set server time to UTC and only work with UTC values. It's not an excuse for MySQL's absolutely flawed approach to datetime values, but it's a pretty sane workaround. - it doesn't matter that DATETIME lacks timezone information, since you implicitly treat all timezones as UTC. - TIMESTAMP would add still the timezone offset, but it wouldn't do anything since the timezone is UTC +00. With server timezone set to UTC, TIMESTAMP functions identically to DATETIME but with massively reduced capacity, so I don't see any reason to use TIMESTAMP anymore. Compared to BIGINT unix epoch, DATETIME has the advantage of being compatible with MySQL's datetime-manipulating functions (such as ON UPDATE CURRENT_TIMESTAMP).
  - wruza 5y ago
    Explicit timezone is information that cannot be resurrected once lost. “Select all employees around the world who left after 18:00 too often to advise them to care more of their health”. Of course in practice most employers could not care less, so most time libraries feel free to lose it, and developers are aware of that.
    - SPBS 5y ago
      You don't drop the timezone information. You manually normalize the timezone to UTC time, application side, before storing it in the database. This is to get around MySQL's crap handling of datetime types: - TIMESTAMP 'stores' timezone, but it has a limit of year 2038. - DATETIME has a limit of year 9999, but it does not store timezone at all. There really is no better way. Pick DATETIME for its higher capacity, then only ever use UTC time in the database to get around the lack of timezone storage in DATETIME.

simonw 5y ago

Ouch, that sounds nasty! There's a pattern for enforcing datetime formats using a CHECK constraint which I've not tried myself yet but which looks like it could work: https://sqlite.org/forum/forumpost/4f4c96938f4bef32?t=h https://sqlite.org/forum/forumpost/4f4c96938f4bef32?t=h

Cyberdog 5y ago

That's hardly unique to SQLite. I'm a huge fanboy of SQLite, though I have to use MySQL in pretty much all of my client work since I more often get hired to work on existing projects than start new ones. There's three common ways to store timestamps in MySQL and they all stink; - Use an integer column with a Unix timestamp. Not human-readable and no sub-second accuracy if you need it (unless you store the time in milliseconds instead of full seconds, requiring larger int columns, or just get completely crazy and use a float field of some sort. - Use a `datetime` field. Human-readable, supports milliseconds natively, and supports any time between years 1000 and 10,000, but doesn't store a time zone - so if you define the column with something like `'created' TIMESTAMP NOT NULL DEFAULT current_timestamp`, who knows what will actually be inserted in that field when the row is created? - Use a `timestamp` field. Like datetime fields, you get a human-readable field with sub-second precision, but the range is only from the Unix epoch to Y2K38, which is seventeen years away now. These fields store the date unambiguously as UTC - hooray! But… when you retrieve the date, the time will be adjusted "back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis." You can make an unwieldy cast to get the date as UTC - `SELECT CONVERT_TZ(created, @@session.time_zone, "+00:00") AS created_utc` - but obviously this is easy to forget about and performs an unperformant double-cast. All three of these options suck in their own distinct ways. All this rant is to say that date storage in at least two of the most common databases is a bit of a mess right now (it's been long enough since I've used Postgres that I can't recall if things are any better there, but I'm sure one of its fanboys will let us know soon enough if it is). So don't get too hard on your previous developer or blame SQLite in particular.

throwaway2037 5y ago
I also struggle with datetimes. One idea that is much worse by space is store UTC only as string in this format: A date-time without a time-zone in the ISO-8601 calendar system, such as "2007-12-03T10:15:30". (See: https://docs.oracle.com/javase/8/docs/api/java/time/LocalDateTime.html https://docs.oracle.com/javase/8/docs/api/java/time/LocalDat...) I'm pretty sure that format is also string comparable for sorting.
SPBS 5y ago
> There's three common ways to store timestamps in MySQL and they all stink I used to think that, but now I realize those problems would disappear if you set server time to UTC and only work with UTC values. It's not an excuse for MySQL's absolutely flawed approach to datetime values, but it's a pretty sane workaround. - it doesn't matter that DATETIME lacks timezone information, since you implicitly treat all timezones as UTC. - TIMESTAMP would add still the timezone offset, but it wouldn't do anything since the timezone is UTC +00. With server timezone set to UTC, TIMESTAMP functions identically to DATETIME but with massively reduced capacity, so I don't see any reason to use TIMESTAMP anymore. Compared to BIGINT unix epoch, DATETIME has the advantage of being compatible with MySQL's datetime-manipulating functions (such as ON UPDATE CURRENT_TIMESTAMP).
- wruza 5y ago
  Explicit timezone is information that cannot be resurrected once lost. “Select all employees around the world who left after 18:00 too often to advise them to care more of their health”. Of course in practice most employers could not care less, so most time libraries feel free to lose it, and developers are aware of that.
  - SPBS 5y ago
    You don't drop the timezone information. You manually normalize the timezone to UTC time, application side, before storing it in the database. This is to get around MySQL's crap handling of datetime types: - TIMESTAMP 'stores' timezone, but it has a limit of year 2038. - DATETIME has a limit of year 9999, but it does not store timezone at all. There really is no better way. Pick DATETIME for its higher capacity, then only ever use UTC time in the database to get around the lack of timezone storage in DATETIME.