4

Context: MySQL version

On MySQL 5.7.17 (as shown by SHOW VARIABLES LIKE "%version%";)

Context: MySQL timezone settings

and with SELECT @@global.time_zone, @@session.time_zone; yielding

+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

...which means the server tracks the system timezone.

Context: System timezone

This being a Linux, we check the system timezone like this (output redacted):

$ timedatectl
      Local time: Wed 2018-02-14 13:21:30 GMT
  Universal time: Wed 2018-02-14 13:21:30 UTC
        RTC time: Wed 2018-02-14 13:21:30
       Time zone: Europe/London (GMT, +0000)

Context: What we know

  • Time and date values are not stored with timezone information in MySQL (there is no clear description of this in The DATE, DATETIME, and TIMESTAMP Types, except "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)"; note that "converts TIMESTAMP values to UTC for storage" actually makes sense only as an implementor's note, it's not useful to the user - what is important is that TiMEZONE always returns the correct value in whatever timezone one is in, while DATETIME will always return the same value in whatever timezone one is in)

Context: Current time

It's now around :

$ date +"The current time is %s alias %c"
The current time is 1518617572 alias Wed 14 Feb 2018 14:12:52 GMT

Problem

Let's run:

SELECT NOW(),FROM_UNIXTIME(0),UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());

The output (suitably pivoted by hand; there should be an option to do that):

NOW()                            2018-02-14 14:15:13  -- Display "now" in SYSTEM timetone: OK
FROM_UNIXTIME(0)                 1970-01-01 01:00:00  -- Display 1970-01-01 00:00:00 in SYSTEM timetone: NOT OK
UNIX_TIMESTAMP()                 1518617713           -- Correct unixtime
FROM_UNIXTIME(UNIX_TIMESTAMP())  2018-02-14 14:15:13  -- Same as NOW(): OK

In the above everything is fine, except the FROM_UNIXTIME(0) giving 1970-01-01 01:00:00, which is one hour off. If Unix Time 0 were displayed in UTC, it would give 1970-01-01 00:00:00 (or would it?)

This happens if I connect locally or from a remote machine.

To make doubly sure, I checked by coercing to DATETIME. The same happens:

CREATE TEMPORARY TABLE test (N DATETIME, FUX DATETIME, UXT LONG, NN DATETIME);
INSERT INTO test SELECT NOW(),FROM_UNIXTIME(0),UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT * FROM test;
+---------------------+---------------------+------------+---------------------+
| N                   | FUX                 | UXT        | NN                  |
+---------------------+---------------------+------------+---------------------+
| 2018-02-14 14:19:51 | 1970-01-01 01:00:00 | 1518617991 | 2018-02-14 14:19:51 |
+---------------------+---------------------+------------+---------------------+
0

3 Answers 3

2

This does depend on the session time zone. I'm in the same time zone as yourself:

$ timedatectl
    Local time: Wed 2018-02-14 21:25:44 GMT
Universal time: Wed 2018-02-14 21:25:44 UTC
      RTC time: Wed 2018-02-14 21:25:44
     Time zone: Europe/London (GMT, +0000)

The below results are from MariaDB, but I assume it's the same with MySQL.

SET time_zone = 'SYSTEM';
SELECT FROM_UNIXTIME(0), NOW();
+---------------------+---------------------+
| FROM_UNIXTIME(0)    | NOW()               |
+---------------------+---------------------+
| 1970-01-01 01:00:00 | 2018-02-14 21:30:11 |
+---------------------+---------------------+

And:

SET time_zone = '+1:00';
SELECT FROM_UNIXTIME(0), NOW();
+---------------------+---------------------+
| FROM_UNIXTIME(0)    | NOW()               |
+---------------------+---------------------+
| 1970-01-01 01:00:00 | 2018-02-14 22:30:33 |
+---------------------+---------------------+

However:

SET time_zone = '+0:00';
SELECT FROM_UNIXTIME(0), NOW();
+---------------------+---------------------+
| FROM_UNIXTIME(0)    | NOW()               |
+---------------------+---------------------+
| 1970-01-01 00:00:00 | 2018-02-14 21:34:41 |
+---------------------+---------------------+

I'm surprised that time_zone values SYSTEM and +0:00 give different results for FROM_UNIXTIME(0), given that my time zone is GMT, +0000. This looks like a bug to me, but maybe there's something I'm not understanding.

0
3

At time 0, "Time zone: Europe/London" was DST (or not DST?); you are currently not in DST (or you are?). That is probably the 1-hour discrepancy.

Europe/London is not the same as UTC.

3
  • No, DST is currently off, as it is applied in summer. Europe/London is "currently" the same as UTC. Unixtime 0 should be displayed as "1970-01-01 00:00:00" in timezone UTC. The inverse on the command line: date --utc --date="1970-01-01 00:00:00" +"%s" -> 0. Commented Feb 20, 2018 at 8:14
  • 1
    @DavidTonhofer - But was DST turned on on 1970-01-01? (I don't know, but it might explain the 1-hour diff.)
    – Rick James
    Commented Feb 20, 2018 at 16:22
  • 1
    Holy cow, you may well be right. At Time Zone History of the United Kingdom, we not only learn that a Brit is responsible for the DST misfeature, but also that: 3 Years of Summer Time: In a trial known as the British Standard Time experiment, the UK kept Daylight Saving Time hours permanently from February 1968 to November 1971. ... The experiment was abandoned in 1972 because of its unpopularity — particularly in the north of the country, where days are generally shorter. So Europe/London might mean ....? Commented Feb 20, 2018 at 19:02
3

I suppose your database is deployed in the United Kingdom. During the period from October 1968 to October 1971, the UK conducted an experiment with 'British Standard Time', which involved using GMT+1 throughout the year.

...A further inquiry during 1966–1967 led the government of Harold Wilson to introduce the British Standard Time experiment, with Britain remaining on GMT+1 throughout the year. This took place between 27 October 1968 and 31 October 1971, when there was a reversion to the previous arrangement. https://en.wikipedia.org/wiki/British_Summer_Time#Periods_of_deviation

My MySQL 8.0.34 is also affected by this issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.