Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17824

Convert between UTC datetime and UNIX timestamps without referencing timezone

    Details

      Description

      MariaDB 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 doing a SET @@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.





      Compare this statement, broken for an hour at the end of DST each year :

      Assume LastModTime is a DATETIME field storing a UTC value...

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




      With this fix for it:

      This works... but seriously, waaay overcomplicated!!!

      SET @oldTZ:=@@time_zone;
      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 instead:

      Simple, clear, and no non-obvious once-per-year bugs relating to timezones!

      SELECT DateAdded, UTC_UNIX_TIMESTAMP(LastModTime) FROM customer_records; 
      




        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              Dean T Dean Trower
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: