MySQL Bugs: #93328: Need ability to convert between unix timestamp values and dates without DST bugs

Bug #93328	Need ability to convert between unix timestamp values and dates without DST bugs
Submitted:	25 Nov 2018 1:28	Modified:	14 Nov 2019 0:09
Reporter:	Dean Trower	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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

Fix:

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!

Hi, 

I do not think that your request makes any sense ....... You can make this function as a stored routine. There is no need for yet another DATETIME function of that sort.

Also, keep in mind the following:

-----------------------------------

If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between values in a non-UTC time zone and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes such as Daylight Saving Time (DST), it is possible for UNIX_TIMESTAMP() to map two values that are distinct in a non-UTC time zone to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original values. Here is an example, using values that are distinct in the MET time zone:

mysql> SET time_zone = 'MET';
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00       |
+---------------------------+

This is not a feature request that would be acceptable.

Sinisa, you're right that my example code didn't make sense.  I should have stated that DateAdded was a timestamp column (so that the value retrieved depends on @@time_zone), and I also got the order of arguments to CONVERT backwards in the first line, it should have been:

SELECT DateAdded, UNIX_TIMESTAMP(CONVERT(LastModTime,'+00:00',@@time_zone))

...But I think you missed the main point:  It's entirely because timezone conversion is lossy that these functions are needed!

There are at least 3 kinds of date/time storage in common use:

*  timestamps (which get retrieved as local datetime, based on current timezone settings)
*  datetime fields, storing LOCAL date/time
*  datetime fields, storing UTC date/time

My point is that for the latter of these, it should be easy to convert back and forth between the UTC datetime and its Unix-timestamp numerical representation, WITHOUT involving lossy roundtrip conversion through the local timezone.  Yes, you could write a couple of stored functions to do it, but:

(a)  This is VERY basic functionality that is widely needed, not the sort of thing you ought to need to write yourself, and,

(b)  It's probably not obvious to a lot of programmers that UNIX_TIMESTAMP(CONVERT(someUTCdatetimeField,'+00:00',@@time_zone)) is buggy when   
someUTCdatetimeField falls during the 1-hour annual window at the end of local DST.

That's why I propose UTC_FROM_UNIXTIME and UTC_UNIX_TIMESTAMP (or perhaps UNIX_TIMESTAMP_FROM_UTC) should be built-in functions.

Hello Mr. Trower,

We are truly not convinced that this is a feature request that is needed. In addition to what we wrote, why don't we make a separate function for each timezone , not just UTC ???

However, if number of impacted users grows rapidly, we could reconsider this.

You know Sinisa, I realize your suggestion was sarcastic (or at least, intended as deliberately impractical) but actually there SHOULD be a way of converting between UNIX timestamp integer (or decimal) values, and specific timezones, without the intermediate conversion to local time and the DST-related bugs that that introduces.

Accordingly, I'd like to *modify* this feature request... Perhaps you'll find the new version more palatable?

New version of feature request is as follows:

-------------------------------------------------------------------
UNIX_TIMESTAMP() and FROM_UNIXTIME() should both support an additional optional argument, "timezone", which if supplied overrides the local (session) timezone in the calculations these functions perform.
-------------------------------------------------------------------

This would provide MySQL with a non-buggy way of converting between UNIX timestamp values and dates - something it SHOULD be able to do out-of-the-box but currently can't - with no new functions required, and only minimal changes to the existing ones.

(Obviously if implemented, it's important that there be no intermediate conversion via any DST timezone internally in the function code).

Of course, you might still think that this new version of my feature request is still not worthwhile implementing.
If so, it's your call and I won't pester you any further on the matter.

Sorry Mr. Trower.

What you are asking for is not feasible, since it would brake so many existing applications.

SQL is not C++ and it can not have default parameters for its functions ......

Erm, well maybe they're not called "default parameters" in SQL, but you've *certainly* got them.  From the manual, v8.0:

*   FROM_UNIXTIME(unix_timestamp[,format])

..."The format string, if given, is used to format the result the same way as described in the entry for the DATE_FORMAT() function"...

*   UNIX_TIMESTAMP([date])

"If UNIX_TIMESTAMP() is called with no date argument"...
"If UNIX_TIMESTAMP() is called with a date argument"...

All I am suggesting is that the optional arguments be extended like so:

FROM_UNIXTIME(unix_timestamp[,format[,time_zone]])
UNIX_TIMESTAMP([date[,time_zone]])

This CANNOT break existing code, as no existing code would ever have been written to call these functions with the additional time_zone argument.

Indeed I've seen a number of cases where newer versions of MySQL have extended existing functions by adding extra arguments just like this, that weren't supported by older versions.  For example:  NOW([fsp]) - the fsp (fractional seconds precision) argument was added in v5.6.4, it didn't exist before then.