Unix-часи в базах даних — зберігання, запити та найкращі практики

Більшість схем баз даних потребують зберігання дат і часів. Вибір зазвичай стоїть між зберіганням Unix-часу (цілих чисел — кількість секунд від 1970) або використанням рідного типу datetime. Обидва працюють. Різниця проявляється в тому, як ви робите запити, сортуєте, порівнюєте та показуєте дані — і наскільки складним буває керування часовими поясами.

Unix Timestamp Converter допомагає з окремими перетвореннями. Ця стаття розглядає компроміси на рівні баз даних і як кожна основна база даних обробляє обидва підходи.

Цілі числа проти рідного типу Datetime: основний компроміс

Зберігання як ціле число (Unix-час):

Тип стовпця: INT або BIGINT
Зберігає: кількість секунд (або мілісекунд) від 1970-01-01 00:00:00 UTC
Часовий пояс: відсутній — число завжди в UTC
Арифметика: тривіальна (created_at > NOW_UNIX - 86400 для "останніх 24 годин")
Читабельність: потребує перетворення (1714000000 → потрібно перевірити, яка це дата)

Рідне зберігання datetime:

Тип стовпця: TIMESTAMP, TIMESTAMPTZ, DATETIME залежно від бази даних
Зберігає: структурований представлення дати та часу
Часовий пояс: варіюється залежно від типу й бази даних (див. нижче)
Арифметика: потрібні функції конкретної бази (DATEDIFF, EXTRACT, інтервальна арифметика)
Читабельність: людиночитаний у результатах запитів (2024-04-25 11:46:40)

Жоден не є універсально правильним. Вибір залежить від вашого сценарію, паттернів запитів і того, як ви обробляєте часові пояси в додатку.

PostgreSQL: TIMESTAMP проти TIMESTAMPTZ проти Integer

PostgreSQL має три практичних варіанти:

TIMESTAMP WITHOUT TIME ZONE — зберігає точно те, що ви передали. Без перетворення часового поясу. Якщо вставити '2024-04-25 09:00:00' з програмного сервера у Нью-Йорку, це і зберігається — хоча це представляє 13:00 UTC. При зчитуванні ви отримаєте 2024-04-25 09:00:00 без інформації про часовий пояс. Небезпечно для систем, де часовий пояс сервера може змінитися або де задіяні кілька часових поясів.

TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) — завжди зберігає UTC всередину, незалежно від того, що ви передали. При записі PostgreSQL перетворює часовий пояс сесії на UTC. При зчитуванні він перетворює назад на часовий пояс сесії. Це рекомендований тип для більшості виробничих систем. Назва оманлива — він не зберігає часовий пояс, а зберігає UTC і робить перетворення.

-- Часовий пояс сесії: America/New_York (UTC-4)
INSERT INTO events (created_at) VALUES ('2024-04-25 09:00:00');
-- Зберігається як: 2024-04-25 13:00:00 UTC

SELECT created_at FROM events;
-- Повертає: 2024-04-25 09:00:00-04 (перетворено назад на часовий пояс сесії)

BIGINT (Unix-час) — повністю незалежний від часового поясу. Додаток відповідає за перетворення. Корисно, коли ви хочете повністю обійти обробку часових поясів PostgreSQL і контролювати все на рівні додатку.

-- Запит діапазону (останні 7 днів)
SELECT * FROM events WHERE created_at > EXTRACT(EPOCH FROM NOW())::bigint - 604800;

-- Перетворення на читабельну дату
SELECT TO_TIMESTAMP(created_at) FROM events;

Для більшості нових додатків на PostgreSQL: використовуйте TIMESTAMPTZ. Обробка часових поясів коректна, тип читабельний, і функції роботи з datetime PostgreSQL працюють з ним безпосередньо.

MySQL: DATETIME проти TIMESTAMP проти INT

Назва типів у MySQL заплутана, тому що TIMESTAMP в MySQL — не те саме, що TIMESTAMPTZ у PostgreSQL.

DATETIME — зберігає літеральну дату й час, які ви передали. Без перетворення часового поясу. Схоже на PostgreSQL TIMESTAMP WITHOUT TIME ZONE. Діапазон: 1000-01-01 до 9999-12-31.

TIMESTAMP — зберігає UTC всередину, перетворює на/з часовий пояс з'єднання при читанні/записі. Схоже на PostgreSQL TIMESTAMPTZ. Діапазон: 1970-01-01 00:00:01 UTC до 2038-01-19 03:14:07 UTC. Обмеження на 2038 — це проблема року 2038. Тип стовпця TIMESTAMP MySQL використовує 32-бітне ціле число всередину, тому він досягає переповнення у цю дату.

INT або BIGINT — ручне керування Unix-часом. Уникає проблеми 2038, тому що BIGINT може зберігати часи далеко за 2038.

-- Проблема 2038 зі TIMESTAMP стовпцем
-- Це не вдасться або зберігатиметься неправильно:
INSERT INTO events (created_at) VALUES (FROM_UNIXTIME(2147483648)); -- Jan 19, 2038 + 1 sec

-- Підхід INT/BIGINT уникає цього
INSERT INTO events (created_at) VALUES (2147483648); -- зберігається нормально
SELECT FROM_UNIXTIME(created_at) FROM events; -- перетворює для показу

Для додатків MySQL з даними, які мають пережити 2038: використовуйте DATETIME або BIGINT замість TIMESTAMP.

SQLite: все це текст або числа

SQLite не має рідних типів datetime. Він зберігає дати як:

Текстові рядки (ISO 8601 формат: '2024-04-25T11:46:40')
Ціле число (Unix-час)
Real (номер дня за юліанським календарем)

Функції дат SQLite працюють з усіма трьома форматами, але всередину це просто текст і числа. Цілий підхід природний для SQLite й уникає двозначності:

-- Зберігайте як ціле число
CREATE TABLE events (created_at INTEGER);
INSERT INTO events (created_at) VALUES (strftime('%s', 'now'));

-- Запит останніх 7 днів
SELECT * FROM events WHERE created_at > strftime('%s', 'now') - 604800;

-- Показ як людиночитаний
SELECT datetime(created_at, 'unixepoch', 'localtime') FROM events;

Для додатків SQLite цілі Unix-часи часто найчистіший вибір, оскільки SQLite обробляє їх природно й уникають накладних витрат на розбір текстових часів.

Часті запити: синтаксис Integer проти Datetime

Операція	Unix-ціле число (PostgreSQL)	TIMESTAMPTZ (PostgreSQL)
Записи за останні 24 години	`WHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 86400`	`WHERE ts > NOW() - INTERVAL '24 hours'`
Записи за останні 7 днів	`WHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 604800`	`WHERE ts > NOW() - INTERVAL '7 days'`
Записи від сьогодні	`WHERE ts >= date_trunc('day', NOW())` (потребує попереднього перетворення)	`WHERE ts >= date_trunc('day', NOW())`
Групування по дням	Складно — перетворити, потім обрізати	`GROUP BY date_trunc('day', ts)`
Людиночитаний показ	`TO_TIMESTAMP(ts)`	`ts` (уже читабельний)

Цілий підхід потребує більше явних перетворень для операцій обрізання дат (групування по дням, тижням, місяцям). Рідні datetime-стовпці обробляють це чистіше в SQL.

Що використовувати: посібник для вибору

Використовуйте TIMESTAMPTZ (PostgreSQL) або DATETIME (MySQL), коли:

Ваша команда часто пише SQL безпосередньо (запити більш читабельні)
Вам потрібно групувати по дням/тижням/місяцям у запитах бази даних
Ви хочете використовувати рідні функції часу без накладних витрат на перетворення

Використовуйте Unix-часи як цілі числа, коли:

Ви використовуєте MySQL й потребуєте дані за 2038
Ваш рівень додатку обробляє всю логіку часових поясів і ви хочете обійти обробку часових поясів бази даних
Ви використовуєте SQLite
Ви будуєте API, де часи передаватимуться на JavaScript фронтенди (цілі числа однозначні щодо масштабу, як тільки ви документуєте секунди проти мілісекунд)
Ви зберігаєте дуже великі об'єми часів і хочете компактного зберігання (4-байтний INT проти змінного datetime-представлення)

Не змішуйте підходи в одному додатку. Наявність одних таблиць з рідними datetimes і інших з Unix-часами створює накладні витрати на перетворення і когнітивне навантаження при перемиканні для кожного розробника команди.

Що б ви не зберігали, документуйте: додайте коментар до визначення стовпця, визначивши, чи це UTC Unix-секунди, UTC Unix-мілісекунди, чи локальний datetime. Використовуйте Unix Timestamp Converter для перевірки конкретних значень часу під час відлагодження чи міграції даних.

Встановити цей додаток