Jak używać uniksowych znaczników czasu do zapytań o zakresy dat

Wyszukiwanie rekordów w danym zakresie dat — „pokaż mi wszystko z ostatnich 30 dni", „znajdź zamówienia między 1 stycznia a 31 marca" — to jedna z najczęstszych operacji w każdej aplikacji opartej na bazie danych. Sposób, w jaki przechowujesz i wyszukujesz znaczniki czasu, ma duży wpływ na to, jak proste i niezawodne są te zapytania.

Uniksowe znaczniki czasu (liczby całkowite reprezentujące sekundy od epoki Uniksa) są często najlepszym wyborem, ale tylko jeśli wiesz, jak ich prawidłowo używać. Oto praktyczny przewodnik.

Dlaczego uniksowe znaczniki czasu upraszczają zapytania o zakresy

Gdy znaczniki czasu są przechowywane jako liczby całkowite, zapytanie o zakres dat staje się prostym porównaniem numerycznym:

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

I tyle. Bez parsowania łańcuchów znaków, bez konwersji stref czasowych, bez martwienia się semantyką BETWEEN. Baza danych porównuje dwie liczby.

Porównaj to z podejściem wykorzystującym sformatowany łańcuch datetime:

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

Wygląda podobnie, ale rodzi kilka pytań: W jakiej strefie czasowej jest '2024-01-01 00:00:00'? Czy serwer aplikacji interpretuje to tak samo jak serwer bazy danych? Co się stanie, gdy użytkownicy z różnych stref czasowych uruchomią to samo zapytanie?

Uniksowe znaczniki czasu omijają wszystkie te problemy. Zawsze są UTC, zawsze jednoznaczne, zawsze liczby. Konwersja na czas lokalny odbywa się w warstwie wyświetlania, a nie w zapytaniu.

Obliczanie znaczników czasu dla typowych zakresów dat

Aby napisać zapytanie o zakres, potrzebujesz uniksowych znaczników czasu dla początku i końca zakresu. Konwerter uniksowych znaczników czasu czyni to łatwym — wpisz datę i uzyskaj znacznik czasu.

Oto obliczenia dla typowych wzorców zapytań:

Ostatnie N dni: ` start = aktualny_znacznik_czasu - (N × 86400) end = aktualny_znacznik_czasu `

Ostatnie 7 dni: start = teraz - 604800 (7 × 86 400 sekund) Ostatnie 30 dni: start = teraz - 2592000 (30 × 86 400) Ostatnie 90 dni: start = teraz - 7776000

Konkretny miesiąc kalendarzowy (np. marzec 2024): ` start = znacznik czasu Uniksa dla 2024-03-01 00:00:00 UTC = 1709251200 end = znacznik czasu Uniksa dla 2024-04-01 00:00:00 UTC = 1711929600 `

Zapytanie: WHERE created_at >= 1709251200 AND created_at < 1711929600

Uwaga: użyj < (mniej niż) zamiast <= dla granicy końcowej, gdy używasz północy następnego dnia. To czyszczenie wyklucza wszelkie rekordy z 1 kwietnia bez martwienia się o dokładną ostatnią sekundę 31 marca.

Od początku roku: ` start = znacznik czasu Uniksa dla 1 stycznia bieżącego roku, 00:00:00 UTC end = aktualny znacznik czasu `

Ustalony zakres dat między dwoma datami: Konwertuj obie daty do znaczników czasu północy UTC za pomocą konwertera i używaj ich bezpośrednio jako granic.

Prawidłowa obsługa stref czasowych

Najczęstszy błąd przy zapytaniach o zakresy z uniksowymi znacznikami czasu to zamieszanie w strefach czasowych podczas generowania znaczników granic.

Uniksowe znaczniki czasu zawsze są UTC. Jeśli twoja aplikacja musi wyszukać „zamówienia złożone 8 kwietnia w czasie nowojorskim (UTC-4)", to 8 kwietnia w Nowym Jorku obejmuje:

2024-04-08 00:00:00 EDT = 2024-04-08 04:00:00 UTC = znacznik czasu Uniksa 1712545200
2024-04-09 00:00:00 EDT = 2024-04-09 04:00:00 UTC = znacznik czasu Uniksa 1712631600

Jeśli naiwnie użyjesz północy UTC zamiast tego:

2024-04-08 00:00:00 UTC = znacznik czasu Uniksa 1712534400

Będziesz zawierać 4 godziny rekordów z późnego 7 kwietnia czasu nowojorskiego i brakować 4 godzin rekordów z późnego 8 kwietnia.

Prawidłowe podejście: zawsze konwertuj zakres dat na UTC przed wygenerowaniem znaczników granic. W kodzie:

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)  # lub .timestamp()

Lub użyj biblioteki dat, która obsługuje znaczniki czasu świadome stref czasowych. Kluczowa zasada: generuj granice w lokalnym czasie użytkownika, konwertuj na UTC, a następnie użyj wynikowej liczby w zapytaniu.

Indeksowanie dla wydajności

Aby zapytania o zakresy znaczników czasu były szybkie, kolumna znacznika czasu musi mieć indeks. W tabeli z milionami wierszy niezindeksowane skanowanie liczb całkowitych dla zakresu dat będzie powolne, niezależnie od tego, jak proste wygląda zapytanie.

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

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

Dla zapytań, które często łączą zakresy czasowe z innymi filtrami (np. WHERE user_id = 123 AND created_at >= X), indeks złożony z najbardziej selektywną kolumną na początku jest zwykle szybszy:

CREATE INDEX idx_events_user_created ON events (user_id, created_at);

Indeksy złożone pozwalają bazie danych najpierw zawęzić według użytkownika, a następnie przeskanować tylko rekordy tego użytkownika uporządkowane czasowo pod względem zakresu.

Generowanie granic znaczników czasu w popularnych językach

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

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

Dla konkretnej daty UTC: `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); // 1 stycznia 2024 północ czasu lokalnego // Użyj strtotime dla UTC: strtotime('2024-01-01 00:00:00 UTC') `

SQL (PostgreSQL) — generowanie granicy w tekście zapytania: `sql SELECT * FROM events WHERE created_at >= EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days')::int AND created_at <= EXTRACT(EPOCH FROM NOW())::int; `

Typowe błędy do uniknięcia

Używanie północy czasu lokalnego zamiast północy UTC. Jeśli serwer aplikacji jest w innej strefie czasowej niż UTC, new Date().setHours(0,0,0,0) w JavaScripcie daje ci północ w lokalnej strefie czasowej serwera — nie UTC. Zawsze bądź jawny co do strefy czasowej.

Off-by-one na granicach końcowych. created_at <= 1711929599 (ostatnia sekunda 31 marca) i created_at < 1711929600 (północ 1 kwietnia) są równoważne, ale druga forma jest czystsza i unika przypadków brzegowych w podsekundach, jeśli kiedykolwiek przełączysz się na milisekundy.

Zapomnienie przejść na czas letni. Podczas konwertowania zakresów dat w strefach czasowych z przejściem na czas letni, jeden dzień w roku ma 23 godziny, a jeden 25 godzin. To wpływa na obliczenia „ostatnie 24 godziny": teraz - 86400 nie zawsze oznacza „wczoraj o tej samej porze" w czasie lokalnym. Dla dokładnych zapytań o dni lokalne, generuj granice z lokalnej daty kalendarzowej w jawny sposób, zamiast odejmować ustalone liczby sekund.

Nieindeksowanie kolumny znacznika czasu. Oczywiste w zasadzie, ale często pomijane we wczesnym rozwoju, gdy tabele są małe i powolne zapytania nie są jeszcze zauważalne.

Użyj konwertera uniksowych znaczników czasu, aby sprawdzić poprawność swoich znaczników granic przed wdrożeniem zapytania — wklej liczbę i potwierdź, że konwertuje się na oczekiwaną datę i godzinę.

Install this app