Работа с датами и временем

Для работы с датой и временем в MySQL есть несколько типов данных: DATE, TIME, DATETIME и TIMESTAMP.

Тип	Описание	Диапазон значений	Размер
DATE	Хранит значения даты в виде ГГГГ-ММ-ДД. Например, 2022-12-05	от 1000-01-01 до 9999-12-31	3 байта
TIME	Хранит значения времени в формате ЧЧ:ММ:СС. (или в формате ЧЧЧ:ММ:СС для значений с большим количеством часов). Например, 800:50:50	от -838:59:59 до 838:59:59	3 байта
DATETIME	Хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2022-12-05 10:37:22	от 1000-01-01 00:00:00 до 9999-12-31 23:59:59	8 байта
TIMESTAMP	Хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2022-12-05 10:37:22	от 1970-01-01 00:00:01 до 2038-01-19 03:14:07	4 байта

Для работы с датой и временем в PostgreSQL есть несколько типов данных: DATE, TIME, TIMESTAMP, TIMESTAMPTZ и INTERVAL.

Тип	Описание	Диапазон значений	Размер
DATE	Хранит значения даты в виде ГГГГ-ММ-ДД. Например, 2022-12-05	от 4713 BC до 5874897 AD	4 байта
TIME	Хранит значения времени в формате ЧЧ:ММ:СС. Например, 14:30:45	от 00:00:00 до 24:00:00	8 байт
TIMESTAMP	Хранит значение даты и времени без часового пояса. Например, 2022-12-05 10:37:22	от 4713 BC до 294276 AD	8 байт
TIMESTAMPTZ	Хранит значение даты и времени с часовым поясом. Например, 2022-12-05 10:37:22+03	от 4713 BC до 294276 AD	8 байт
INTERVAL	Хранит интервал времени. Например, 1 year 2 months 3 days 4 hours	от -178000000 лет до 178000000 лет	16 байт

Отличие TIMESTAMP и DATETIME

Типы данных DATETIME и TIMESTAMP в MySQL похожи друг на друга, так как оба направлены на хранение даты и времени. Но между ними есть ряд существенных отличий, определяющих какой из этих типов данных когда лучше использовать.

DATETIME

Хранит значения в диапазоне от 1000-01-01 00:00:00 до 9999-12-31 23:59:59 и при этом занимает 8 байт. Этот тип данных не зависит от временной зоны, установленной в MySQL. Он всегда отображается ровно в таком виде, в котором был установлен и в котором хранится в базе данных. То есть при изменении часового пояса, отображение времени не изменится.

MySQL 8.1
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;

datetime_field
2022-06-16 16:37:23

TIMESTAMP

Хранит сколько прошло секунд с 1970-01-01 00:00:00 по нулевому часовому поясу и занимает 4 байта. При выборках отображается с учётом текущего часового пояса. Часовой пояс можно задать в настройках операционной системы, где работает MySQL, в глобальных настройках MySQL или в конкретной сессии. В базе данных при создании записи с типом TIMESTAMP значение сохраняется по нулевому часовому поясу.

MySQL 8.1
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_field
2022-06-16 19:37:23

Также стоит помнить о существующем ограничении 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 не знает, в каком часовом поясе была записана дата, и не производит никаких преобразований.

MySQL 8.1
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 для хранения и обратно в локальный часовой пояс при выводе.

MySQL 8.1
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 используется для хранения периодов времени. Он может представлять промежутки в годах, месяцах, днях, часах, минутах и секундах.

MySQL 8.1
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 для указания только даты

При указании даты допускается использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Также возможно задавать дату вообще без разделительного знака, слитно.

MySQL 8.1
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;

datetime
2022-06-16 16:37:23
2022-05-31 08:15:04
2014-02-22 16:37:22
2022-06-16 16:37:23
2021-02-12 00:00:00

Значения типов даты и времени могут быть заданы в различных форматах:

Как строка в формате ISO 8601: 'YYYY-MM-DD HH:MM:SS'
Как строка с указанием часового пояса: 'YYYY-MM-DD HH:MM:SS+TZ'
Только дата: 'YYYY-MM-DD'
Только время: 'HH:MM:SS'

MySQL 8.1
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 и предпочитает использовать стандартные форматы дат.

Telegram