CURRENT_TIME Timestamp is incorrect when inserting row in MySQL, I'm using AWS RDS

Question

edit: this was a problem with the mysql library I was using node-mysql2

I am using AWS RDS to host a MySQL database.

There is a column in my database with the following definition

createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP

When I inserted a row at 4:43 pm EST I get the following value for that column

2018-12-27T02:43:32.000Z

When I try to convert this value to EST I get

12/26/2018, 9:43:32 PM

which is incorrect.

Am I doing something incorrect or is there something I need to configure?

@DanFarrell I believe it should be -5, I think the conversion is correct. Anyway, if using a 7 hour offset still wouldn't account for the error. — vicg, Dec 26 '18 at 21:58
Youre right i was going backwards :p. Almost sounds like the time zone conversion was double applied but with current_timestamp default i dont know how that might happen — erik258, Dec 26 '18 at 22:09

erik258 · Answer 1 · 2021-10-26T15:46:24.233

I thought this test was interesting, and though it doesn't answer your question, it does take the node library out of the equation.

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TEMPORARY TABLE t (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created DATETIME DEFAULT CURRENT_TIMESTAMP,
  tz varchar(32)
);

select "datetime";

SET time_zone = "CST6CDT";
insert into t (tz) values (@@SESSION.time_zone);

SET time_zone = "EST5EDT";
insert into t (tz) values (@@SESSION.time_zone);

SET time_zone = "UTC";
insert into t (tz) values (@@SESSION.time_zone);

SET time_zone = "CST6CDT";
select *, @@SESSION.time_zone FROM t;

SET time_zone = "EST5EDT";
select *, @@SESSION.time_zone FROM t;

SET time_zone = "UTC";
select *, @@SESSION.time_zone FROM t;

DROP TEMPORARY TABLE t;

select "timestamp";

CREATE TEMPORARY TABLE t (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  tz varchar(32)
);

SET time_zone = "CST6CDT";
insert into t (tz) values (@@SESSION.time_zone);

SET time_zone = "EST5EDT";
insert into t (tz) values (@@SESSION.time_zone);

SET time_zone = "UTC";
insert into t (tz) values (@@SESSION.time_zone);

SET time_zone = "CST6CDT";
select *, @@SESSION.time_zone FROM t;

SET time_zone = "EST5EDT";
select *, @@SESSION.time_zone FROM t;

SET time_zone = "UTC";
select *, @@SESSION.time_zone FROM t;

Here I create a temporary table with a DEFAULT CURRENT_TIMESTAMP column. I set the session variables to each of CST, EST, and UTC to test some different local time zones, then insert the session time zone to track which time zone created which value. I then select them out again with each of the timezones set as my session time zone to show how the time zone of the insert session and the time zone of the select session interact.

Then I do the whole thing again, exactly as before, but instead of DATETIME I create the column with TIMESTAMP field the second time.

I run it in a docker container just for testing, which I start up with:

docker run --rm -d -e MYSQL_ALLOW_EMPTY_PASSWORD=true --name mysql mysql

But it should be pretty easy to run from any mysql client that can reach a server; here's my docker exec invocation:

$ docker exec -i mysql mysql -t  < t.sql
+----------+
| datetime |
+----------+
| datetime |
+----------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 11:07:05 | CST6CDT | CST6CDT             |
|  2 | 2018-12-27 12:07:05 | EST5EDT | CST6CDT             |
|  3 | 2018-12-27 17:07:05 | UTC     | CST6CDT             |
+----+---------------------+---------+---------------------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 11:07:05 | CST6CDT | EST5EDT             |
|  2 | 2018-12-27 12:07:05 | EST5EDT | EST5EDT             |
|  3 | 2018-12-27 17:07:05 | UTC     | EST5EDT             |
+----+---------------------+---------+---------------------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 11:07:05 | CST6CDT | UTC                 |
|  2 | 2018-12-27 12:07:05 | EST5EDT | UTC                 |
|  3 | 2018-12-27 17:07:05 | UTC     | UTC                 |
+----+---------------------+---------+---------------------+
+-----------+
| timestamp |
+-----------+
| timestamp |
+-----------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 11:07:05 | CST6CDT | CST6CDT             |
|  2 | 2018-12-27 11:07:05 | EST5EDT | CST6CDT             |
|  3 | 2018-12-27 11:07:05 | UTC     | CST6CDT             |
+----+---------------------+---------+---------------------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 12:07:05 | CST6CDT | EST5EDT             |
|  2 | 2018-12-27 12:07:05 | EST5EDT | EST5EDT             |
|  3 | 2018-12-27 12:07:05 | UTC     | EST5EDT             |
+----+---------------------+---------+---------------------+
+----+---------------------+---------+---------------------+
| id | created             | tz      | @@SESSION.time_zone |
+----+---------------------+---------+---------------------+
|  1 | 2018-12-27 17:07:05 | CST6CDT | UTC                 |
|  2 | 2018-12-27 17:07:05 | EST5EDT | UTC                 |
|  3 | 2018-12-27 17:07:05 | UTC     | UTC                 |
+----+---------------------+---------+---------------------+

As you can see, DATETIME is always stored in the session's local time. It's not obvious to me how to know whether to convert this to a different time zone, as the time zone is apparently stored as UTC internally but obviously doesn't get corrected for the SELECT sesson's time zone.

The time stamp, on the other hand, behaves differently. In these cases the timestamp, when selected, is correctly converted to the session time of the SELECT. I would think this would typically be the behavior a database client would expect ( I set a session time zone, so show me the dates in that session timezone ).

In short, if you want to store local time, use datetime. If you want to store "absolute time", use timestamp. Not sure if that's a good rule but based on my test it seems like a good approach.

Of course, as you pointed out, the client does still have to set the time zone they wish to use. Setting everything to use the same time zone kind of obviates the whole issue :)

score 0 · Accepted Answer · answered Dec 26 '18 at 23:24

This was not a problem with AWS or MySQL

I figured out what the problem was, node-mysql2 decided to convert the timestamp. I don't understand what conversion it was doing or why.

The setting I used to fix it was

let options = {
    ...,
    timezone: 'UTC', // Interpret all received timestamps as UTC. Otherwise local timezone is assumed.
    dateStrings: [
        'DATE', // DATE's are returned as strings (otherwise they would be interpreted as YYYY-MM-DD 00:00:00+00:00)
        'DATETIME' // DATETIME's return as strings (otherwise they would interpreted as YYYY-MM-DD HH:mm:ss+00:00)
    ]
}

score 0 · Answer 3 · answered Aug 07 '20 at 01:59

You should NEVER use any database date, time, or timestamp format. Oracle, MySQL, and MS SQL Server do you "favors" as described in this question. I learned this the very hard way when transmissions we shipped from the transmission plant were arriving at the assembly plant before they were shipped. Logistics is HUGE in a big auto company - more than a million miles of 18-wheeler traffic PER DAY. Scheduling well is BIG BUCKS, so we were told to FIX IT NOW!

Turned out that the operating system thought it was in one time zone, the database was convinced it was in another, and when we logged in, each user ID had been set to a different time zone.

The ONLY solution is to record ALL times as GMT milliseconds. That way, you can retrieve events in an interval, compare times, compute time intervals, and merge data from many sources. YOU have to worry about displaying time in a way that makes sense to each user, but at least you won't have to worry about silliness in your underlying calculations.

Never, Ever use database time stamps - they mess you up in funny ways.

CURRENT_TIME Timestamp is incorrect when inserting row in MySQL, I'm using AWS RDS

3 Answers3