Робота з Unix-часом: EPOCH

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

Якщо спростити, Unix-час — це як таймер, який почав тикати у 1970 році і не зупиняється. Бачиш дивне число типу 1697222400? Не лякайся — це просто скільки секунд минуло з початку відліку. Хочеш зрозуміти, яка це дата? Зараз навчимось це рахувати!

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

Конвертація в Unix-час

У PostgreSQL є спеціальна функція EXTRACT(EPOCH FROM ...), яка дозволяє конвертувати дату або timestamp у 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, де ми зберігаємо інфу про події, включаючи їхні timestamp-и у форматі Unix-часу.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name TEXT,
    event_time BIGINT -- для зберігання Unix-часу
);

Можемо вставити дані в цю таблицю, вказавши timestamp-и у форматі 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-час ідеально підходить для зберігання часових даних при передачі значень між системами, порівняння timestamp-ів та інших задач, де треба мінімальне навантаження на процесор. Його активно юзають в API, веб-розробці, аналітиці, а також для синхронізації серверних і клієнтських додатків. Але важливо завжди враховувати його особливості: UTC-орієнтованість, різні одиниці виміру (секунди чи мілісекунди) і формат зберігання.

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