MySQL Bugs: #83852: timestamp types can not be used safely due to timezone changes

Bug #83852	timestamp types can not be used safely due to timezone changes
Submitted:	16 Nov 2016 18:12	Modified:	12 Dec 2019 22:44
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	8.0 and ealier	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	clocks go back., summer time, timestamp, winter time

Description:
The MySQL timestamp have been unusual in that it has the following characteristics:
(1) the 0 epoch value of 1970-01-01 00:00:00 has been hijacked" by the zero timestamp "0000-00-00 00:00:00" so the former value can't be stored explicitly.
(2) Values are stored from a string representation which is in the server or local time zone representation. If the session timezone or server timezone is UTC / +00:00 then things are fine, otherwise read-on.
(3) all values stored in a timestamp type hold the unix epoch (number of seconds) since 1970-01-01 00:00:00 UTC, and as of MySQL 5.6 this may contain fractional seconds.

So far so good.

For anyone using a local timezone which changes, usually between summer to winter time (the clocks go back) there is an hour during this period where the same "local time" can happen twice. Consequently if this value is ever used it's completely undefined which actual time will be used by MySQL.

One solution is to run your systems with MySQL in UTC. If you do that from the start the issues above should not happen but it's easy in hindsight to see this but to have not setup existing systems this way.

However, not having done that leads to the following types of consequence described below:

How to repeat:
Imagine moving from CEST to CET as happened last month from 03:00:00 CEST to 02:00:00 CET. Any system which stores the local time (with no explicit timezone information) in MySQL will have a problem, especially if any time/date maths are done on this value.

e.g. Several people use a heartbeat injected on a master server and updated every second. You can then check on any slave in the replication chain the difference in time from NOW() [ also in local time ] from the time retrieved from the table where the event was stored.

Yet at 02:00:00 CEST MySQL will it seems try to store '2016-10-30 02:00:00', it knows it's in "local time in Madrid" (my timezone) but which time does it store? It does not know and it looks to me like it actually stored 2016-10-30 02:00:00 CET which was an hour later.

A check on a downstream slave retrieves this value and compares it against the local time and notices a 1 hour difference, thus triggering monitoring alerts. Of course nothing has actually happened.

Suggested fix:
This seems to manifest itself by the need for the following features:

(1) a way to store (if needed) an explicit UTC string representation of the time. This would avoid any timezone ambiguity. I would like to be able to do:

UPDATE my_heartbeat_table SET my_timestamp = UTC_STRING_TO_TIMESTAMP( '2016-10-30 02:00:00') and ensure the appropriate value were stored in the table.

(2) Make things easy and let me directly store the epoch value bypassing any conversion routines. I guess something like this:

UPDATE my_heartbeat_table SET my_timestamp = UTC_EPOC( 1477792800 ) -- or whatever the correct value is.

(3) WHEN defining tables we currently don't have a way of defining a UTC timestamp as a default. This has lead to a lot of people using the zero-timestamp which is ugly and also overrides the usage of the 0 epoch value. I live in Europe so given I can't use epoch 0 as a default I want to use "epoch 1" but that time in the Europe timezone has to be written as

CREATE TABLE t ( some_timestamp DEFAULT '1970-01-01 01:00:01' ) ....

which to put it mildly is ugly and triggers people to ask me what this "magic value" is and where it comes from.

If I can't use DEFAULT '1970-01-01 00:00:00' and I think this is impossible then perhaps allow me to use something like DEFAULT _UTC '1970-01-01 00:00:01' which would make the intent much clearer, and the value would be the same on your server even if local time is PST.

(4) Finally if we are going to have "UTC representations" of timestamps then it would be good to have the "date/time match functions" which would work on them. There already exist some of the functions but they can't be used directly on these strings for the reasons specified, so something similar to TIMESTAMP{ADD,SUB,DIFF}.

(5) We have UTC_TIMESTAMP() to get the current time but we don't have a way to get a UTC TEXT representation of a timestamp column so this function would be needed too. e.g. TIMESTAMP_TO_STRING(some_timestamp_col)

(6) Modify the documentation to make people aware that using local times in MySQL has some consequences and may cause confusion especially if the applications are unable to cope with the timezone changes. These things happen that often that people forget and only get reminded once or twice a year.

Finally I believe that support for a timestamp + timezone type would help avoid a lot of this type of confusion. I would like to see such a new type. Even with that type existing MySQL users would need to migrate their data so the current Feature request stands until or if that functionality ever happens.

Thank you for the feature request.

A similar request in bug#6742. There is also a worklog created for this feature.

Related: bug#80863

Roy, could you provide a url for the worklog entry?

Thanks. There's no reference to it in the "original" bug that you mention.

Look up worklog number 3744 in https://dev.mysql.com/worklog/.

I thought it was private so I did not see any use in providing the WL number.

Most likely it needs changes to serve your purpose, though.

The issues described here are about how to be able to handle the current TIMESTAMP type especially related to being able to extract and store the "native/internal" values (UTC epoch seconds) and then how to handle this correctly during winter/summer time changes where as described problems currently occur.

So thanks for providing the worklog reference. That's good information for a related but different feature request to what I'm talking about here.

Also suggested by a colleague:

It is a pity that MySQL doesn't support UTC-based timestamp literals in its SQL and tsv (mysqldump) yet.

Suggestions:
'2018-10-14Z10:45:00'
1539468000e0

and then also:
'2018-10-14Z10:45:00.123456'
1539468000.123456e0
1539468000123456e-6

I think that similar problems can also occur due to leap-seconds, though it probably doesn't affect as many people as seriously.

But then again, I'm sure there are developers who assume that because their timezone is already set to UTC (or any non-daylight-savings timezone) they're OK, and that TIMESTAMPS will always work properly for them.  (Whereas, when a leap-second occurs, it might break certain assumptions -- for example, you'd assume that successively inserting NOW() into a TIMESTAMP(6) column would generate an ascending series of timestamps... but it might not if you hit a leap-second!)

Furthermore, unlike daylight savings time problems (which can be worked around by temporarily switching timezones), there doesn't seem to be any way around the leap-second issues.
 
So, I'd like to vote for this feature:  We need a way of writing an exact UNIX-time value into a timestamp column, without roundtrip date conversion!

Simon, in regard to doing this:

UPDATE my_heartbeat_table SET my_timestamp = UTC_EPOCH(1477792800);

There is actually a way to do it, albeit very clumsily.  You just do:

SET @@timestamp := 1477792800;
UPDATE my_heartbeat_table SET my_timestamp = NOW();
SET @@timestamp := DEFAULT;

...see the docs on Server System Variables!

[This is very limited, of course, as you can't update with an expression that evaluates to a different UNIX time value for each row, you'd have to do each value separately; it might be a huge bottleneck.  But it's do-able in principle.]

Dean,

While the suggestion you make works it requires 3 round trips to the server to complete the task and is not practical for the many locations that this might happen. Also be aware that when using pooled connections the first statement might go through a different connection to another one unless you take special measures so that requires more careful attention.

Yes, workarounds are possible but we're looking for something which just works rather than something which mainly works but can catch you when you least expect it.

I wasn't really suggesting it as a practical solution: It's completely horrible.  I only meant to point out that it wasn't actually impossible to achieve.

The UTC_EPOCH() function requested already exists: FROM_UNIXTIME() which in the context of storing seconds since epoch to a timestamp column works without conversions between any timezones.
Either by:
INSERT INTO t VALUES (FROM_UNIXTIME(<seconds since epoch>))
or
UPDATE t SET timestamp_column = FROM_UNIXTIME(<seconds since epoch>)

Mattias, are 100% you sure of that?  I mean, that MySQL internally omits doing the double conversion and just stores the value as-is?

This should be clearly documented, if so.
And it could well be regarded as a bug, because it would mean that:

INSERT INTO t VALUES (FROM_UNIXTIME(<seconds since epoch>))

will sometimes store a DIFFERENT value to:

SET @t:=FROM_UNIXTIME(<seconds since epoch>);
INSERT INTO t VALUES (@t)

...which violates every expectation I have as a programmer!

Dean, I'm sorry for not verifying first. It seems like the only correct way of doing this is actually change the session time_zone variable, even setting @@timestamp is not correct :(

My @@time_zone is set to SYSTEM which is 'Europe/Amsterdam'.

unix_timestamp and from_unixtime is not exactly the reverse of each other:
select unix_timestamp(from_unixtime(1572136200));
+-------------------------------------------+
| unix_timestamp(from_unixtime(1572136200)) |
+-------------------------------------------+
|                                1572139800 |
+-------------------------------------------+
1 row in set (0.01 sec)

Resulting in from_unixtime() does the wrong thing when trying to store seconds since epoch:
create table ts (id int primary key, ts timestamp);
insert into ts values(1, from_unixtime(1572136200)), (2, from_unixtime(1572139800));
select id, ts, unix_timestamp(ts) from ts;
+----+---------------------+--------------------+
| id | ts                  | unix_timestamp(ts) |
+----+---------------------+--------------------+
|  1 | 2019-10-27 02:30:00 |         1572139800 |
|  2 | 2019-10-27 02:30:00 |         1572139800 |
+----+---------------------+--------------------+

update ts set ts = from_unixtime(1572136200) where id = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0
(Changed: 0 means it did not change the value!)

And setting @@timestamp + using now() is not correct either:
set @@timestamp := 1572136200;
update ts set ts = now() where id = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0
(Changed: 0 means it did not change the value!)

So the last resort seems still to be changing @@time_zone:
set @@time_zone = '+00:00';
insert into ts values(3, '2019-10-27 00:30:00'), (4, '2019-10-27 01:30:00');
set @@time_zone = 'SYSTEM';
majonsson@test_majonsson (ro)> select id, ts, unix_timestamp(ts) from ts where id > 2;
+----+---------------------+--------------------+
| id | ts                  | unix_timestamp(ts) |
+----+---------------------+--------------------+
|  3 | 2019-10-27 02:30:00 |         1572136200 |
|  4 | 2019-10-27 02:30:00 |         1572139800 |
+----+---------------------+--------------------+
2 rows in set (0.01 sec)

Hmmm.  Well it would seem I'm guilty of not verifying properly either.  Ooops!

...
I just tested now using online db-fiddle.com.

It would seem you can't use NOW() (or its synonyms) to insert a specific @@timestamp value, but you CAN instead use insert/update to NULL instead, or insert the default value for timestamp columns defined with "DEFAULT CURRENT_TIMESTAMP"

The relevant fiddle is here:  https://www.db-fiddle.com/f/tB1fJM8P84mSUYBj3Xo3Su/2

This is how to fix my previous example... just replace NOW() with NULL:

SET @@timestamp := 1477792800;
UPDATE my_heartbeat_table SET my_timestamp = NULL;
SET @@timestamp := DEFAULT;

In the fiddle I also test *copying* the timestamp from one table to another, both via a 2-table UPDATE, and by an INSERT...SELECT.

In v5.7 the timestamps get copied exactly as they are stored.
BUT, if you switch to using v8.0, this is no longer the case: they undergo roundtrip conversion to datetime, and so get modified due to DST!

i.e. in MySQL v8.0, you can't even copy timezone data from one column or table to another reliably in a DST timezone!

For reference, should db-fiddle.com go down, the SQL I tested is:

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

CREATE TABLE t (id int unsigned NOT NULL AUTO_INCREMENT,descr varchar(30) NOT NULL DEFAULT '',ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY(id));
CREATE TABLE t2 (id int unsigned NOT NULL AUTO_INCREMENT,descr varchar(30) NOT NULL DEFAULT '',ts timestamp NOT NULL DEFAULT '2001-01-01',PRIMARY KEY (id));
SET @@time_zone := 'Australia/Melbourne';    ## A daylight-savings zone
INSERT INTO t (descr,ts) VALUES ('explicit FROM_UNIXTIME()',FROM_UNIXTIME(1522510200));   ## In the given time zone, both these timestamps map to the SAME clock time.
INSERT INTO t (descr,ts) VALUES ('explicit FROM_UNIXTIME()',FROM_UNIXTIME(1522513800));   ## Insertion does roundtrip conversion to a datetime and back, so the inserted values are identical.
INSERT INTO t (descr,ts) VALUES ('update to NULL','2001-01-01'),('update to NULL','2001-01-01');
SET @@timestamp := 1522510200;        ## Instead try inserting @@timestamp using NOW(), or using NULL.
INSERT INTO t (descr,ts) VALUES ('insert NOW()',NOW());
UPDATE t SET ts=NULL WHERE id=3;
SET @@timestamp := 1522513800;
INSERT INTO t (descr,ts) VALUES ('insert NOW()',NOW());
UPDATE t SET ts=NULL WHERE id=4;
SET @@timestamp := 1522510200;        ## Instead try inserting @@timestamp using DEFAULT CURRENT_TIMESTAMP column property
INSERT INTO t (descr) VALUES ('insert default value');
SET @@timestamp := 1522513800;
INSERT INTO t (descr) VALUES ('insert default value');
SET @@timestamp := DEFAULT;
INSERT INTO t2 (id) VALUES (7),(8);                  ## Try copying inserted timestamps:
UPDATE t INNER JOIN t2 USING (id) SET t2.descr = 'copy via UPDATE',t2.ts = t.ts;      ## via UPDATE
INSERT INTO t2 (descr,ts) SELECT 'copy via INSERT...SELECT',ts FROM t WHERE id>=7;    ## via INSERT...SELECT
SET @@time_zone = '+0:00';

SELECT id, descr, UNIX_TIMESTAMP(ts) FROM t;
SELECT id, descr, UNIX_TIMESTAMP(ts) FROM t2;

Is it no longer possible to add comments to the MySQL doc pages? Or edit existing comments (as opposed to just deleting them)?

I had posted the incorrect method:

SET @@timestamp := 1522510200;
INSERT INTO t (ts) VALUES (NOW());
SET @@timestamp := DEFAULT;

As a comment at the bottom of this docs page:  https://dev.mysql.com/doc/refman/8.0/en/datetime.html
NOW() needs to be changed to NULL in that comment, or people may be misled.

I spoke too soon about not being able to copy timestamps from one table to another in v8.0 vs v5.7

It would seem the problem here is actually the value of the explicit_defaults_for_timestamp boolean system variable, which defaults to ON in v8.02+, but previously defaulted to OFF, and not the version of MySQL per se.

Here's an updated fiddle using MySQL v8, that explicitly turns it OFF:  https://www.db-fiddle.com/f/tB1fJM8P84mSUYBj3Xo3Su/6

But here I leave it ON:   https://www.db-fiddle.com/f/tB1fJM8P84mSUYBj3Xo3Su/7

...and the result is most unexpected!

In fact, what appears to be happening is that the timestamps I'm trying to copy are *sometimes* undergoing round-trip conversion to local date/time values and back.  This happens whenever explicit_defaults_for_timestamp is ON, but also occasionally at other times, depending apparently on the query execution plan MySQL comes up with.

This is a bug, and I've filed a new bug report:   http://bugs.mysql.com/95797

Fixed in MySQL 8.0.19 by WL#10828 (support for explicit time zone offsets when inserting TIMESTAMP and DATETIME values).

Bear in mind that MySQL converts TIMESTAMP values from the current session time zone to UTC when storing them and from UTC to the current session time zone when retrieving them; it does not do this with DATETIME values, as per https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Closed.