Как преобразовать дату в MySQL unix timestamp и обратно?

MySQL - одна из самых популярных открытых СУБД. Часто возникает необходимость работать с датами и временем. Как же преобразовать дату в удобный для вычислений формат Unix Timestamp и обратно в MySQL? Узнайте в этой статье!

Основы работы с датами и временем в MySQL

Для хранения дат и времени в MySQL используются специальные типы данных:

• DATE - только дата, без времени
• DATETIME - дата и время
• TIMESTAMP - также дата и время, с автоматическим обновлением

Эти типы хранят значения в определенных текстовых форматах. Например, для DATE это YYYY-MM-DD, где YYYY - год, MM - месяц, DD - день. У каждого типа данных есть свой диапазон допустимых значений.

В некоторых случаях удобнее работать с датами и временем не в текстовом представлении, а в виде чисел. Для этого используется формат Unix Timestamp.

Unix Timestamp - это количество секунд, прошедших с 1 января 1970 года 00:00:00 по UTC.

То есть дата и время выражаются одним числом. Это упрощает вычисления и сравнения.

Функция UNIX_TIMESTAMP()

Чтобы преобразовать значение даты/времени в MySQL в Unix Timestamp, используется функция UNIX_TIMESTAMP(). Рассмотрим ее подробнее.

Синтаксис функции:

UNIX_TIMESTAMP([date])

Здесь в квадратных скобках указан необязательный параметр date - преобразуемая дата и/или время. Если параметр не указан, то функция вернет текущий Unix Timestamp.

Параметр date может быть передан в виде строки (в формате DATETIME или TIMESTAMP) или числа (в формате YYMMDD, YYMMDDHHMMSS и т.д.).

Рассмотрим примеры использования UNIX_TIMESTAMP().

Получение текущего времени в Unix Timestamp:

SELECT UNIX_TIMESTAMP();

Преобразование конкретной даты:

SELECT UNIX_TIMESTAMP('2023-01-05 14:25:10');

Результат функции UNIX_TIMESTAMP():

• Возвращается целое число, если исходная дата не содержит дробной части секунд
• Возвращается число с плавающей точкой, если дата содержит дробные секунды
• NULL при передаче некорректной даты
• 0 при передаче недопустимой даты (например, 01.01.2040)

Обратное преобразование

Чтобы преобразовать значение из Unix Timestamp обратно в формат даты и времени, используется функция FROM_UNIXTIME().

Например:

SELECT FROM_UNIXTIME(1672945710);

Результат:

2023-01-05 14:15:10

Рассмотрим типичные задачи, которые решаются в MySQL с помощью Unix Timestamp.

Хранение временных данных

В таблицах MySQL можно хранить числовые значения Unix Timestamp. Для этого используются целочисленные типы данных INT, BIGINT и т.д. Например, так можно хранить время событий в каком-либо приложении:

 CREATE TABLE events ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), event_time BIGINT ); 

Затем данные можно записывать, используя UNIX_TIMESTAMP():

 INSERT INTO events (name, event_time) VALUES ('page_open', UNIX_TIMESTAMP('2023-01-07 10:03:25')), ('add_to_cart', UNIX_TIMESTAMP(NOW())); 

А для вывода применить обратное преобразование:

 SELECT id, name, FROM_UNIXTIME(event_time) AS event FROM events; 

Группировка и агрегация данных по периодам времени

Числовой формат позволяет эффективно группировать записи по периодам времени. Например, можно посчитать количество событий помесячно:

 SELECT YEAR(FROM_UNIXTIME(event_time)) AS year, MONTH(FROM_UNIXTIME(event_time)) AS month, COUNT(*) AS events FROM events GROUP BY year, month ORDER BY year, month; 

Аналогично можно группировать по дням, часам и т.д.

Вычисления и сравнение

Можно выполнять арифметические операции над значениями дат и интервалов времени. Например, найти время через 5 дней от текущего момента:

 SELECT FROM_UNIXTIME(UNIX_TIMESTAMP() + 5*24*60*60); 

Или определить разницу во времени между двумя событиями:

 SELECT e1.name AS event_1, e2.name AS event_2, e2.event_time - e1.event_time AS seconds_diff FROM events e1 JOIN events e2 ON e1.id = e2.id - 1; 

То есть между событиями прошло 360 секунд.