CURRENT_TIME Неверная отметка времени при вставке строки в MySQL, я использую AWS RDS
Редактировать : это была проблема с библиотекой mysql, которую я использовал node-mysql2
Я использую AWS RDS для размещения базы данных MySQL.
В моей базе данных есть столбец со следующим определением
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Когда я вставил строку в 16:43 EST, я получил следующее значение для этого столбца
2018-12-27T02:43:32.000Z
Когда я пытаюсь преобразовать это значение в EST, я получаю
12/26/2018, 9:43:32 PM
что неверно.
Я делаю что-то неправильно или мне нужно что-то настроить?
2 ответа
Я думал, что этот тест был интересным, и хотя он не отвечает на ваш вопрос, он выводит библиотеку узлов из уравнения, и я подумал, что это интересно.
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;
Здесь я создаю временную таблицу с DEFAULT CURRENT_TIMESTAMP колонка. Я установил переменные сеанса для каждого из CST, EST и UTC, чтобы протестировать несколько разных локальных часовых поясов, а затем вставил часовой пояс сеанса, чтобы отследить, какой часовой пояс создал какое значение. Затем я снова выбираю их с каждым часовым поясом, установленным в качестве часового пояса моего сеанса, чтобы показать, как взаимодействуют часовой пояс сеанса вставки и часовой пояс выбранного сеанса.
Затем я делаю все снова, точно так же, как и раньше, но вместо DATETIME я создаю столбец с полем TIMESTAMP во второй раз.
Я запускаю его в Docker-контейнере только для тестирования, с которого начинаю:
docker run --rm -d -e MYSQL_ALLOW_EMPTY_PASSWORD=true --name mysql mysql
Но это должно быть довольно легко запустить из любого клиента MySQL, который может достичь сервера; вот мой docker exec вызов:
$ 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 |
+----+---------------------+---------+---------------------+
Как видите, DATETIME всегда хранится по местному времени сеанса. Для меня не очевидно, как узнать, следует ли преобразовать это в другой часовой пояс, поскольку часовой пояс, очевидно, хранится как UTC внутри, но, очевидно, не корректируется для часового пояса SELECT-сессии.
Отметка времени, с другой стороны, ведет себя по-другому. В этих случаях отметка времени, если она выбрана, корректно преобразуется во время сеанса SELECT. Я бы подумал, что, как правило, такое поведение будет ожидать клиент базы данных (я установил часовой пояс сеанса, поэтому покажу мне даты в этом часовом поясе сеанса).
Короче говоря, если вы хотите хранить местное время, используйте datetime. Если вы хотите сохранить "абсолютное время", используйте отметку времени. Не уверен, что это хорошее правило, но на основании моего теста это кажется хорошим подходом.
Конечно, как вы указали, клиент все еще должен установить часовой пояс, который он хочет использовать. Настройка всего для использования одного и того же часового пояса устраняет проблему в целом:)
НИКОГДА не используйте формат даты, времени или метки времени в базе данных. Oracle, MySQL и MS SQL Server делают вам "покровительство", как описано в этом вопросе. Я узнал это очень на собственном горьком опыте, когда трансмиссии, которые мы отправляли с завода по производству трансмиссий, прибывали на сборочный завод перед отправкой. Логистика ОГРОМНА в большой автомобильной компании - более миллиона миль 18-колесного движения ЗА ДЕНЬ. Хорошее планирование - это БОЛЬШИЕ БАКИ, поэтому нам сказали ИСПРАВИТЬ ЭТО СЕЙЧАС!
Оказалось, что операционная система думала, что это один часовой пояс, база данных была убеждена, что это был другой часовой пояс, и когда мы вошли в систему, каждый идентификатор пользователя был установлен в другой часовой пояс.
ЕДИНСТВЕННОЕ решение - записывать ВСЕ время в миллисекундах по Гринвичу. Таким образом, вы можете извлекать события в интервале, сравнивать время, вычислять временные интервалы и объединять данные из многих источников. ВАМ нужно беспокоиться об отображении времени таким образом, чтобы это было понятно каждому пользователю, но, по крайней мере, вам не придется беспокоиться о глупостях в ваших основных вычислениях.
Никогда, никогда не используйте метки времени базы данных - они забавным образом портят вам жизнь.
Это не было проблемой с AWS или MySQL
Я выяснил, в чем проблема, node-mysql2 решил преобразовать метку времени. Я не понимаю, какое преобразование это делало или почему.
Настройка, которую я использовал, чтобы исправить это было
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)
]
}