Bug #93328 Convert between UTC datetime and UNIX timestamps without referencing timezone
Submitted: 25 Nov 2018 1:28
Reporter: Dean Trower Email Updates:
Status: Open Impact on me:
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[25 Nov 2018 1:28] Dean Trower
MySQL desperately needs functions corresponding to FROM_UNIXTIME() and UNIX_TIMESTAMP, but that work strictly in UTC - that is, a:

UTC_FROM_UNIXTIME() that returns a UTC date from a unix timestamp, and
UTC_UNIX_TIMESTAMP(dt) that treats dt as being in UTC.

This can be achieved already by setting @@time_zone:='+00:00' before using the existing functions, but that is inconvenient if you also wish to use local times in the same query.

It's important because the conversion is not reliable in any timezone that has DST.

Programmers should be able to convert UTC datetimes to UNIX timestamps and back simply and easily, without the conversion breaking one hour out of every year in some locales, and without having to do anything to (or even think about!) session timezone settings.

How to repeat:

Suggested fix:
Compare, assuming LastModTime is a DATETIME field storing a UTC value:

SELECT DateAdded, UNIX_TIMESTAMP(CONVERT(LastModTime,@@time_zone,'+00:00')) FROM customer_records;   # Oops! broken for an hour at the end of DST


SET @oldTZ:=@@time_zone;   # This works... but seriously, waaay overcomplicated!!!
SET @@time_zone:='+00:00';
SELECT CONVERT_TZ(DateAdded,@@time_zone,@oldTZ), UNIX_TIMESTAMP(LastModTime) FROM customer_records;
SET @@time_zone:=@oldTZ;

What we should be able to write:

SELECT DateAdded, UTC_UNIX_TIMESTAMP(LastModTime) FROM customer_records;  # simple, clear, and no non-obvious once-per-year bugs relating to timezones!