Работа с Unix-временем: EPOCH

Unix-время — это просто способ записывать время как число секунд (а иногда миллисекунд), прошедших с полуночи 1 января 1970 года по UTC. Такой формат очень популярен в разработке: его легко хранить, передавать и сравнивать, ведь это всего лишь число.

Если упростить, Unix-время — это как таймер, который начал тикать в 1970 году и не останавливается. Видите странное число вроде 1697222400? Не пугайтесь — это просто сколько секунд прошло с начала отсчёта. Хотите понять, какая это дата? Скоро научимся это вычислять!

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

Преобразование в Unix-время

В PostgreSQL есть специальная функция EXTRACT(EPOCH FROM ...), которая позволяет преобразовать дату или временную метку в Unix-время.

Давайте преобразуем текущую дату в этот формат:

SELECT EXTRACT(EPOCH FROM NOW());

Результат будет чем-то вроде:

1697222400

Этот результат — просто текущая дата и время в секундах с 1970 года.

А вот как можно преобразовать фиксированную дату:

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2023-10-01 12:00:00');

Результат:

1696152000

Теперь мы знаем, что 1 октября 2023 года в 12:00 по UTC соответствует этому значению.

Преобразование из Unix-времени

Разумеется, есть и обратная функция преобразования. Если у вас есть Unix-время, и вы хотите преобразовать его обратно в удобный для человека формат, вам поможет функция TO_TIMESTAMP().

Давайте преобразуем Unix-время в дату и время

SELECT TO_TIMESTAMP(1697222400);

Результат:

2023-10-13 00:00:00+00

Теперь мы видим, что это дата 13 октября 2023 года в 00:00 по UTC.

Практическое применение Unix-времени

Допустим, у нас есть таблица events, где мы храним информацию о событиях, включая их временные метки в формате Unix-времени.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time BIGINT -- для хранения Unix-времени
);

Мы можем вставить данные в эту таблицу, указав временные метки в формате Unix-времени:

INSERT INTO events (event_name, event_time)
VALUES
('Запуск сервера', 1697222400),
('Обновление базы данных', 1697308800);

Чтобы преобразовать эту информацию в читаемый вид, достаточно использовать TO_TIMESTAMP:

SELECT event_name, TO_TIMESTAMP(event_time) AS readable_time
FROM events;

Результат:

Советы и типичные ошибки

1. Неправильный выбор точности

Если ваш Unix-временной формат имеет миллисекунды (например, 1697222400000), его нельзя просто напрямую передать в функцию TO_TIMESTAMP или использовать в EXTRACT. В таких случаях нужно делить значение на 1000:

SELECT TO_TIMESTAMP(1697222400000 / 1000);

Результат:

2023-10-13 00:00:00+00

2. Игнорирование временных зон

Unix-время представлено в UTC, поэтому результаты всегда будут без учёта часового пояса, если вы специально не преобразуете время. Например:

SELECT TO_TIMESTAMP(1697222400) AT TIME ZONE 'Europe/Moscow';

Результат:

2023-10-13 03:00:00

3. Проблемы со слишком большими числами

Иногда разработчики случайно передают большое значение Unix-времени (например, миллисекунды вместо секунд). Это приводит к ошибочным результатам:

SELECT TO_TIMESTAMP(1697222400000); -- Будет ошибка!

Чтобы исправить это, нужно корректно делить число на 1000.

Пример: расчет времени выполнения задачи

Возьмём пример из реальной жизни: у нас есть таблица задач tasks, и мы хотим вычислить, сколько времени прошло с момента создания каждой задачи.

Создаём таблицу:

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    task_name TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Добавляем несколько задач:

INSERT INTO tasks (task_name)
VALUES
('Задача 1'),
('Задача 2'),
('Задача 3');

Теперь мы можем добавить столбец с Unix-временем:

SELECT id, task_name, EXTRACT(EPOCH FROM created_at) AS created_epoch
FROM tasks;

Результат:

И вычислим, сколько времени прошло с момента создания в секундах:

SELECT id, task_name, EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM created_at) AS elapsed_seconds
FROM tasks;

Результат:

Когда и зачем использовать Unix-время?

Unix-время идеально подходит для хранения временных данных в процессе передачи значений между системами, сравнения временных меток и других задач, где требуется минимальная нагрузка на процессор. Оно активно используется в API, веб-разработке, аналитике, а также для синхронизации серверных и клиентских приложений. Однако важно всегда учитывать его особенности: UTC-ориентированность, различные единицы измерения (секунды или миллисекунды) и формат хранения.

Если вы столкнулись с необходимостью интеграции с внешними системами, которые "говорят на языке Unix-времени", теперь вы знаете, как с ним работать в PostgreSQL. Смело используйте TO_TIMESTAMP и EXTRACT(EPOCH) для преобразования туда и обратно, и все ваши временные задачи будут решены!