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.
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.
PostgreSQL is much better, but there is a footgun. If you want to store timestamps in PostgreSQL you should use the timestamptz type, not the timestamp type. Both types are very well designed and work excellently but the names are confusing.
And there's the Postgres fanboy I knew was coming eventually. :)
Could you elaborate on why timestamptz should be used? The name would imply it stores a time zone; is that correct, and the normal timestamp type does not as in MySQL?
Yes, the name does imply that but it does not actually store any time zone. The timestamptz type stores a point in time in the UTC time zone with microsecond resolution while the timestamp type stores a point in time in an unknown time zone (which time zone is left up to the application/user) also in microsecond resolution. These types are really useful and closely match the LocalDateTime (=timestamp) and Instant (=timestamptz) classes in Java, but in java they have better names.
You almost always want to use timestamptz in columns and only use timestamp when doing certain types of timestamp arthmetics.
> 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).
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.
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.
> 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.
From what I understand, this would mean that you would always have to set the value of datetime columns manually (with a UTC-adjusted value) instead of being able to use 'ON UPDATE current_timestamp'.
Of course, that's unless you set the server time to use UTC (as you suggested above), which strikes me as an extreme and not-very-portable approach.
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/LocalDat...)
I'm pretty sure that format is also string comparable for sorting.
I wish they add a real strict type for dates and times too.