Работа с датами и временем
Для работы с датой и временем в MySQL есть несколько типов данных: DATE, TIME, DATETIME и TIMESTAMP.
Для работы с датой и временем в PostgreSQL есть несколько типов данных: DATE, TIME, TIMESTAMP, TIMESTAMPTZ и INTERVAL.
Отличие TIMESTAMP и DATETIME
Типы данных DATETIME и TIMESTAMP в MySQL похожи друг на друга, так как оба направлены на хранение даты и времени. Но между ними есть ряд существенных отличий, определяющих какой из этих типов данных когда лучше использовать.
DATETIME
Хранит значения в диапазоне от 1000-01-01 00:00:00 до 9999-12-31 23:59:59 и при этом занимает 8 байт. Этот тип данных не зависит от временной зоны, установленной в MySQL. Он всегда отображается ровно в таком виде, в котором был установлен и в котором хранится в базе данных. То есть при изменении часового пояса, отображение времени не изменится.
CREATE TABLE datetime_table (datetime_field DATETIME); SET @@session.time_zone="+00:00"; -- сбрасываем часовой пояс в MYSQL INSERT INTO datetime_table VALUES("2022-06-16 16:37:23"); SET @@session.time_zone="+03:00"; -- меняем часовой пояс в MYSQL SELECT * FROM datetime_table;
TIMESTAMP
Хранит сколько прошло секунд с 1970-01-01 00:00:00 по нулевому часовому поясу и занимает 4 байта. При выборках отображается с учётом текущего часового пояса. Часовой пояс можно задать в настройках операционной системы, где работает MySQL, в глобальных настройках MySQL или в конкретной сессии. В базе данных при создании записи с типом TIMESTAMP значение сохраняется по нулевому часовому поясу.
CREATE TABLE timestamp_table (timestamp_field TIMESTAMP); SET @@session.time_zone="+00:00"; -- сбрасываем часовой пояс в MYSQL INSERT INTO timestamp_table VALUES("2022-06-16 16:37:23"); SET @@session.time_zone="+03:00"; -- меняем часовой пояс в MYSQL SELECT * FROM timestamp_table;
Также стоит помнить о существующем ограничении TIMESTAMP в диапазоне возможных значений от 1970-01-01 00:00:01 до 2038-01-19 03:14:07, что ограничивает его применение. Так, данный тип данных не подойдёт для хранения дат рождения пользователей.
Отличие TIMESTAMP и TIMESTAMPTZ
В PostgreSQL есть два основных типа для хранения даты и времени: TIMESTAMP (без часового пояса) и TIMESTAMPTZ (с часовым поясом).
TIMESTAMP
Тип TIMESTAMP (или TIMESTAMP WITHOUT TIME ZONE) хранит дату и время без информации о часовом поясе. Это означает, что PostgreSQL не знает, в каком часовом поясе была записана дата, и не производит никаких преобразований.
CREATE TABLE timestamp_table (timestamp_field TIMESTAMP); SET timezone = 'UTC'; -- устанавливаем часовой пояс UTC INSERT INTO timestamp_table VALUES('2022-06-16 16:37:23'); SET timezone = 'Europe/Moscow'; -- меняем часовой пояс SELECT * FROM timestamp_table;
Результат: 2022-06-16 16:37:23 (значение не изменилось)
TIMESTAMPTZ
Тип TIMESTAMPTZ (или TIMESTAMP WITH TIME ZONE) хранит дату и время с информацией о часовом поясе. PostgreSQL автоматически преобразует время в UTC для хранения и обратно в локальный часовой пояс при выводе.
CREATE TABLE timestamptz_table (timestamptz_field TIMESTAMPTZ); SET timezone = 'UTC'; -- устанавливаем часовой пояс UTC INSERT INTO timestamptz_table VALUES('2022-06-16 16:37:23'); SET timezone = 'Europe/Moscow'; -- меняем часовой пояс на Moscow (+3) SELECT * FROM timestamptz_table;
Результат: 2022-06-16 19:37:23+03 (время автоматически скорректировано)
INTERVAL
Тип INTERVAL используется для хранения периодов времени. Он может представлять промежутки в годах, месяцах, днях, часах, минутах и секундах.
SELECT '1 year 2 months 3 days 4 hours 5 minutes 6 seconds'::INTERVAL; SELECT '2 weeks'::INTERVAL; SELECT '90 minutes'::INTERVAL;
Способ задания значений
Значения DATETIME, DATE и TIMESTAMP могут быть заданы одним из следующих способов:
- Как строка в формате YYYY-MM-DD HH:MM:SS или в формате YY-MM-DD HH:MM:SS для указания даты и времени
- Как строка в формате YYYY-MM-DD или в формате YY-MM-DD для указания только даты
При указании даты допускается использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Также возможно задавать дату вообще без разделительного знака, слитно.
CREATE TABLE date_table (datetime TIMESTAMP); INSERT INTO date_table VALUES("2022-06-16 16:37:23"); INSERT INTO date_table VALUES("22.05.31 8+15+04"); INSERT INTO date_table VALUES("2014/02/22 16*37*22"); INSERT INTO date_table VALUES("20220616163723"); INSERT INTO date_table VALUES("2021-02-12"); SELECT * FROM date_table;
Значения типов даты и времени могут быть заданы в различных форматах:
- Как строка в формате ISO 8601: 'YYYY-MM-DD HH:MM:SS'
- Как строка с указанием часового пояса: 'YYYY-MM-DD HH:MM:SS+TZ'
- Только дата: 'YYYY-MM-DD'
- Только время: 'HH:MM:SS'
CREATE TABLE date_table ( date_field DATE, time_field TIME, timestamp_field TIMESTAMP, timestamptz_field TIMESTAMPTZ ); INSERT INTO date_table VALUES( '2022-06-16', '16:37:23', '2022-06-16 16:37:23', '2022-06-16 16:37:23+03' ); SELECT * FROM date_table;
PostgreSQL строго следует стандарту ISO 8601 и предпочитает использовать стандартные форматы дат.