Unix laiko žymos duomenų bazėse — saugojimas, užklausos ir gerosios praktikos

Daugumai duomenų bazių schemų reikia saugoti datas ir laiką. Dažniausiai tenka rinktis: saugoti Unix laiko žymą (sveikasis skaičius — sekundės nuo 1970 m.) arba naudoti natyvų datos/laiko stulpelio tipą. Abu variantai veikia. Skirtumai išryškėja tada, kai reikia filtruoti, rūšiuoti, lyginti, grupuoti ir rodyti duomenis — bei tada, kai prasideda problemos su laiko juostomis.

Unix laiko žymų keitiklis tinka pavienėms konversijoms. Šiame straipsnyje aptariami duomenų bazės lygmens kompromisai ir kaip pagrindinės DB tvarkosi su abiem metodais.

Sveikasis skaičius vs natyvus datetime: esminis kompromisas

Sveikasis skaičius (Unix timestamp)

Stulpelio tipas: INT arba BIGINT
Saugo: sekundžių (ar milisekundžių) skaičių nuo 1970-01-01 00:00:00 UTC
Laiko juosta: nėra — skaičius visada yra UTC mastelyje
Aritmetika: labai paprasta (pvz. „paskutinės 24 val.“)
Skaitomumas: reikia konvertuoti, kad suprastumėte (1714000000 → reikia paversti į datą)

Natyvus datetime tipas

Stulpelio tipas: TIMESTAMP, TIMESTAMPTZ, DATETIME (priklauso nuo DB)
Saugo: struktūruotą datos ir laiko reprezentaciją
Laiko juosta: priklauso nuo tipo ir DB (žr. žemiau)
Aritmetika: reikia DB funkcijų (DATEDIFF, EXTRACT, intervalų aritmetika)
Skaitomumas: rezultatuose matomas žmogui suprantamas laikas (2024-04-25 11:46:40)

Nėra universaliai „teisingo“ pasirinkimo. Viskas priklauso nuo naudojimo scenarijaus, užklausų pobūdžio ir to, kaip aplikacijoje tvarkote laiko juostas.

PostgreSQL: TIMESTAMP vs TIMESTAMPTZ vs sveikasis skaičius

PostgreSQL praktiškai turi tris variantus:

TIMESTAMP WITHOUT TIME ZONE — saugo tiksliai tai, ką įrašote. Jokios laiko juostos konversijos. Jei įrašote '2024-04-25 09:00:00' iš Niujorke esančio serverio, DB būtent taip ir išsaugos — nors tai galėjo reikšti 13:00 UTC. Skaitydami atgal gausite 2024-04-25 09:00:00 be konteksto. Tai pavojinga sistemoms, kur gali keistis serverio laiko juosta arba dalyvauja kelios laiko juostos.

TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) — viduje visada saugo UTC, nesvarbu, ką paduodate. Įrašymo metu PostgreSQL konvertuoja iš sesijos laiko juostos į UTC. Skaitymo metu konvertuoja atgal į sesijos laiko juostą. Daugumai produkcinių sistemų tai rekomenduojamas tipas. Pavadinimas kiek klaidinantis — jis nesaugo „laiko juostos“, jis saugo UTC ir konvertuoja.

-- Sesijos laiko juosta: America/New_York (UTC-4)
INSERT INTO events (created_at) VALUES ('2024-04-25 09:00:00');
-- Išsaugoma kaip: 2024-04-25 13:00:00 UTC

SELECT created_at FROM events;
-- Grąžina: 2024-04-25 09:00:00-04 (konvertuota į sesijos laiko juostą)

BIGINT (Unix timestamp) — visiškai nepriklausoma nuo laiko juostų. Konversiją turi atlikti aplikacija. Patogu, jei norite apeiti PostgreSQL laiko juostų mechaniką ir viską kontroliuoti aplikacijos sluoksnyje.

-- Intervalo užklausa (paskutinės 7 dienos)
SELECT * FROM events WHERE created_at > EXTRACT(EPOCH FROM NOW())::bigint - 604800;

-- Paversti į skaitomą datą
SELECT TO_TIMESTAMP(created_at) FROM events;

Daugumai naujų PostgreSQL aplikacijų: rinkitės TIMESTAMPTZ. Laiko juostos tvarkomos teisingai, rezultatai skaitomi, o datos/laiko funkcijos veikia tiesiogiai.

MySQL: DATETIME vs TIMESTAMP vs INT

MySQL terminai painūs, nes TIMESTAMP MySQL nėra tas pats, kas TIMESTAMPTZ PostgreSQL.

DATETIME — saugo literalų datą ir laiką, kurį pateikiate. Nekonvertuoja laiko juostos. Panašu į PostgreSQL TIMESTAMP WITHOUT TIME ZONE. Diapazonas: 1000-01-01 iki 9999-12-31.

TIMESTAMP — saugo UTC viduje, o skaitymo/rašymo metu konvertuoja į/iš ryšio (connection) laiko juostos. Panašu į PostgreSQL TIMESTAMPTZ. Diapazonas: 1970-01-01 00:00:01 UTC iki 2038-01-19 03:14:07 UTC. Tai 2038 metų problema — MySQL TIMESTAMP viduje remiasi 32 bitų skaičiumi, todėl pasiekia perpildymo ribą.

INT arba BIGINT — rankinis Unix timestamp valdymas. BIGINT leidžia saugoti laiką gerokai po 2038 m., todėl išvengia 2038 problemos.

-- 2038 problema su TIMESTAMP stulpeliu
-- Tai nepavyks arba bus įrašyta neteisingai:
INSERT INTO events (created_at) VALUES (FROM_UNIXTIME(2147483648)); -- 2038-01-19 + 1 sek.

-- INT/BIGINT to išvengia
INSERT INTO events (created_at) VALUES (2147483648); -- įrašoma be problemų
SELECT FROM_UNIXTIME(created_at) FROM events; -- konvertuoja rodymui

MySQL sistemoms, kurios turės duomenų ir po 2038 m.: rinkitės DATETIME arba BIGINT, o ne TIMESTAMP.

SQLite: viskas yra tekstas arba skaičius

SQLite neturi tikrų natyvių datetime tipų. Ji saugo datas kaip:

Tekstą (ISO 8601: '2024-04-25T11:46:40')
Sveikąjį skaičių (Unix timestamp)
Real (Julian day number)

SQLite datų funkcijos veikia su visais trim, bet viduje tai vis tiek tekstai ir skaičiai. Unix integer dažnai yra natūraliausias ir mažiausiai dviprasmiškas pasirinkimas:

-- Saugoti kaip sveikąjį skaičių
CREATE TABLE events (created_at INTEGER);
INSERT INTO events (created_at) VALUES (strftime('%s', 'now'));

-- Paskutinės 7 dienos
SELECT * FROM events WHERE created_at > strftime('%s', 'now') - 604800;

-- Rodyti žmogui suprantamai
SELECT datetime(created_at, 'unixepoch', 'localtime') FROM events;

SQLite atveju Unix timestamp’ai dažnai yra „švariausias“ sprendimas — mažiau dviprasmybių ir nereikia parse’inti teksto.

Dažnos užklausos: Unix integer vs datetime sintaksė

Operacija	Unix integer (PostgreSQL)	TIMESTAMPTZ (PostgreSQL)
Įrašai per paskutines 24 val.	`WHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 86400`	`WHERE ts > NOW() - INTERVAL '24 hours'`
Įrašai per paskutines 7 d.	`WHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 604800`	`WHERE ts > NOW() - INTERVAL '7 days'`
Įrašai nuo šiandienos	`WHERE ts >= date_trunc('day', NOW())` (pirma reikia konversijos)	`WHERE ts >= date_trunc('day', NOW())`
Grupavimas pagal dieną	Sudėtingiau — reikia konvertuoti ir tik tada trunc’inti	`GROUP BY date_trunc('day', ts)`
Rodymas žmogui	`TO_TIMESTAMP(ts)`	`ts` (jau skaitoma)

Sveikųjų skaičių atveju tenka daugiau aiškiai konvertuoti, ypač kai reikia trunc’inti pagal dieną/savaitę/mėnesį. Su natyviais datetime tipais SQL’e tai dažniausiai paprasčiau.

Ką rinktis: sprendimo gidas

Rinkitės TIMESTAMPTZ (PostgreSQL) arba DATETIME (MySQL), kai:

Komanda daug rašo SQL tiesiogiai (užklausos skaitomesnės)
Reikia DB užklausose grupuoti pagal dieną/savaitę/mėnesį
Norite naudoti DB laiko funkcijas be konversijų

Rinkitės Unix integer (timestamp), kai:

Naudojate MySQL ir duomenys turi gyventi po 2038 m.
Laiko juostų logiką tvarkote aplikacijos sluoksnyje ir norite apeiti DB konversijas
Naudojate SQLite
Kuriate API, kuri perduoda timestamp’us JavaScript frontend’ui (sveikieji skaičiai aiškūs, kai dokumentuojate sekundes vs milisekundes)
Saugojate labai daug laiko žymų ir norite kompaktiškumo

Nemaišykite metodų toje pačioje aplikacijoje. Kai vienose lentelėse laikai yra datetime, o kitose — integer, kiekvienam kūrėjui tenka nuolat perjunginėti mąstyseną ir atlikti konversijas.

Kad ir ką pasirinktumėte, dokumentuokite: prie stulpelio aprašymo nurodykite, ar tai UTC Unix sekundės, UTC Unix milisekundės, ar lokalus datetime. O konkrečių reikšmių patikrai derinant ar migruojant duomenis naudokite Unix laiko žymų keitiklį.

Įdiegti šią programą