Cum să folosești timestamp-uri Unix pentru interogări pe intervale de date

Interogarea înregistrărilor dintr-un interval de date — „arată-mi tot din ultimele 30 de zile”, „găsește comenzile între 1 ianuarie și 31 martie” — este una dintre cele mai frecvente operațiuni din orice aplicație cu bază de date. Modul în care stochezi și interoghezi timestamp-urile are un impact major asupra simplității și fiabilității acestor interogări.

Timestamp-urile Unix (numere întregi care reprezintă secunde de la epoca Unix) sunt adesea cea mai bună alegere pentru asta, dar numai dacă înțelegi cum să le folosești corect. Iată ghidul practic.

De ce timestamp-urile Unix fac interogările pe interval mai simple

Când timestamp-urile sunt stocate ca numere întregi, o interogare pe interval de date devine o comparație numerică simplă:

SELECT * FROM events
WHERE created_at >= 1704067200
  AND created_at < 1706745600;

Atât. Fără parsare de stringuri, fără conversii de fus orar, fără să te complici cu semantica lui BETWEEN. Baza de date compară două numere întregi.

Compară asta cu o abordare în care stochezi o dată/ora formatată ca string:

SELECT * FROM events
WHERE created_at >= '2024-01-01 00:00:00'
  AND created_at < '2024-02-01 00:00:00';

Arată similar, dar introduce câteva întrebări: în ce fus orar este „2024-01-01 00:00:00”? Îl interpretează serverul aplicației la fel ca serverul bazei de date? Ce se întâmplă când utilizatori din fusuri orare diferite rulează aceeași interogare?

Timestamp-urile Unix evită toate aceste probleme. Sunt mereu UTC, mereu neambigue, mereu un număr. Conversia în ora locală se face la nivel de afișare, nu în interogare.

Cum calculezi timestamp-urile pentru intervale uzuale

Ca să scrii o interogare pe interval, ai nevoie de timestamp-urile Unix pentru începutul și sfârșitul intervalului. Unix timestamp converter face asta ușor — introdu data și primești timestamp-ul.

Iată calcule pentru tipare comune:

Ultimele N zile: ` start = current_timestamp - (N × 86400) end = current_timestamp `

Ultimele 7 zile: start = now - 604800 (7 × 86,400 secunde) Ultimele 30 zile: start = now - 2592000 (30 × 86,400) Ultimele 90 zile: start = now - 7776000

O lună calendaristică specifică (de ex., martie 2024): ` start = Unix timestamp pentru 2024-03-01 00:00:00 UTC = 1709251200 end = Unix timestamp pentru 2024-04-01 00:00:00 UTC = 1711929600 `

Interogare: WHERE created_at >= 1709251200 AND created_at < 1711929600

Notă: folosește < (mai mic decât) în loc de <= pentru limita de final când folosești miezul nopții din ziua următoare. Asta exclude curat orice înregistrări din 1 aprilie, fără să te bazezi pe „ultima secundă” din 31 martie.

De la începutul anului până acum (year to date): ` start = Unix timestamp pentru 1 ianuarie din anul curent, 00:00:00 UTC end = current timestamp `

Interval fix între două date: Convertește ambele date la timestamp-uri UTC de la miezul nopții folosind converterul și folosește-le direct ca limite.

Gestionarea corectă a fusurilor orare

Cea mai comună greșeală în interogările pe interval cu timestamp-uri Unix este confuzia de fus orar când generezi timestamp-urile limită.

Timestamp-urile Unix sunt întotdeauna UTC. Dacă aplicația ta trebuie să interogheze „comenzi plasate pe 8 aprilie în ora New York (UTC-4)”, atunci ziua de 8 aprilie în New York acoperă:

2024-04-08 00:00:00 EDT = 2024-04-08 04:00:00 UTC = Unix timestamp 1712545200
2024-04-09 00:00:00 EDT = 2024-04-09 04:00:00 UTC = Unix timestamp 1712631600

Dacă folosești naiv miezul nopții UTC în schimb:

2024-04-08 00:00:00 UTC = Unix timestamp 1712534400

Ai include 4 ore de înregistrări de la finalul zilei de 7 aprilie în ora New York și ai pierde 4 ore de la finalul zilei de 8 aprilie.

Abordarea corectă: convertește întotdeauna intervalul la UTC înainte să generezi timestamp-urile limită. În cod:

import datetime, pytz

tz = pytz.timezone('America/New_York')
start_local = tz.localize(datetime.datetime(2024, 4, 8, 0, 0, 0))
start_utc_ts = int(start_local.utctimetuple().tm_sec)  # or .timestamp()

Sau folosește o bibliotecă de date care gestionează timestamp-uri „timezone-aware”. Principiul cheie: generează limitele în timpul local al utilizatorului, convertește în UTC, apoi folosește numărul întreg rezultat în interogare.

Indexare pentru performanță

Ca interogările pe interval să fie rapide, coloana de timestamp trebuie indexată. Pe un tabel cu milioane de rânduri, un scan neindexat pe un interval de timp va fi lent indiferent cât de „simplă” arată interogarea.

PostgreSQL: `sql CREATE INDEX idx_events_created_at ON events (created_at); `

MySQL: `sql ALTER TABLE events ADD INDEX idx_created_at (created_at); `

Pentru interogări care combină frecvent intervale de timp cu alte filtre (de ex. WHERE user_id = 123 AND created_at >= X), un index compus cu coloana cea mai selectivă prima este de obicei mai rapid:

CREATE INDEX idx_events_user_created ON events (user_id, created_at);

Indexurile compuse permit bazei de date să restrângă mai întâi după utilizator, apoi să scaneze doar înregistrările acelui utilizator (ordonate pe timp) pentru interval.

Generarea limitelor în limbaje uzuale

JavaScript: `javascript const now = Math.floor(Date.now() / 1000); const last30Days = now - (30 * 86400); // Query: WHERE created_at >= last30Days AND created_at <= now `

Python: `python import time now = int(time.time()) last_30_days = now - (30 * 86400) `

Pentru o dată UTC specifică: `python import datetime dt = datetime.datetime(2024, 1, 1, tzinfo=datetime.timezone.utc) ts = int(dt.timestamp()) # 1704067200 `

PHP: `php $now = time(); $start = mktime(0, 0, 0, 1, 1, 2024); // Jan 1 2024 midnight local time // Use strtotime for UTC: strtotime('2024-01-01 00:00:00 UTC') `

SQL (PostgreSQL) — generare inline a limitelor: `sql SELECT * FROM events WHERE created_at >= EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days')::int AND created_at <= EXTRACT(EPOCH FROM NOW())::int; `

Greșeli comune de evitat

Folosirea miezului nopții local în loc de miezul nopții UTC. Dacă serverul aplicației tale e într-un fus orar diferit de UTC, new Date().setHours(0,0,0,0) în JavaScript îți dă miezul nopții în fusul orar local al serverului — nu UTC. Fii întotdeauna explicit cu fusul orar.

Off-by-one la limita de final. created_at <= 1711929599 (ultima secundă din 31 martie) și created_at < 1711929600 (miezul nopții din 1 aprilie) sunt echivalente, dar a doua formă e mai curată și evită cazurile limită cu sub-secunde dacă treci vreodată la milisecunde.

Ignorarea tranzițiilor DST. Când convertești intervale în fusuri orare cu ora de vară, o zi pe an are 23 de ore și una are 25. Asta afectează calculele de tip „ultimele 24 de ore”: now - 86400 nu este întotdeauna „ieri la aceeași oră” în timpul local. Pentru interogări precise pe zile locale, generează limitele din data calendaristică locală explicit, în loc să scazi un număr fix de secunde.

Neindexarea coloanei de timestamp. Evident în principiu, dar des ratat în dezvoltarea timpurie când tabelele sunt mici și interogările lente nu se observă încă.

Folosește Unix timestamp converter ca să verifici rapid limitele înainte să pui o interogare în producție — lipește numărul întreg și confirmă că se convertește la data și ora pe care le aștepți.