Enhver erfaren utvikler er klar over hvor vanskelig tidshåndtering kan være. Vi priser oss lykkelige for at programmeringsspråket vi bruker har gode open source-biblioteker, sånn at vi slipper å implementere disse greiene selv. Den store utfordringen er hva som skjer i andre systemer eller programmer vi ikke har direkte kontroll over. Ofte kan disse gjøre ting på en fornuftig, men litt annerledes måte enn hva vi er vant til. Noen ganger er oppførselen mildt sagt overraskende.

Jeg har allerede skrevet om kronjobber og tidssoner, så det er vel naturlig at jeg hopper videre til en større “synder” – PostgreSQL. Jeg digger PostgreSQL av mange grunner: Dokumentasjonen er god, jeg kan lett kjøre og teste det lokalt, og alle de store skytjenesteleverandørene har administrerte versjoner jeg kan bruke om jeg ikke har lyst til å drifte ting selv. Og siden det er åpen kildekode finnes det mange gode utvidelser, blant annet PostGIS eller TimescaleDB. Man kan til og med kjøre PostgreSQL i nettleseren i disse dager, noe jeg gjorde da jeg verifiserte og testet ut diverse ting relatert til dette innlegget.

Men som en database først utgitt helt tilbake i 1996 er det naturlig at PostgreSQL har litt merkelig oppførsel, og da spesielt for datatypene og funksjonene. Som database kan man liksom ikke la bakoverkompatibilitet være en ettertanke, og det gjør at man ender opp med logikk som nesten 30 år senere kan virke litt rar. Som for eksempel at NOW() gir deg et tidspunkt i fortiden, at et tidspunkt med tidssone ikke inneholder noen tidssone, og at et år er 360 dager, men bare av og til.

Når nå er da

Ved første øyekast høres det nok litt rart ut at NOW() kan finte deg ut. Om du har brukt PostgreSQL før er du nok kjent med funksjonen: Den returnerer tidspunktet da den ble kalt.

… eller, det er i alle fall det mange tror når de først kommer til PostgreSQL. De med traumer fra tidshåndtering i andre databaser vil nok lese seg opp på hva den faktisk returnerer, mens de med erfaring i PostgreSQL har antakeligvis fått vite hva NOW() returnerer på den harde måten. Dokumentasjonen sier nemlig blant annet dette:

now() is a traditional PostgreSQL equivalent to transaction_timestamp()

Transaksjonstidspunktet, tidspunktet transaksjonen startet på, kan nemlig være veldig forskjellig fra tidspunktet du kaller NOW(). I de fleste tilfeller er ikke dette et problem, men noen ganger får det store konsekvenser.

Se for deg for eksempel at du har en hendelseslogg for brukere, og at vi ønsker at denne er i riktig rekkefølge:

CREATE TABLE events (
  event_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  user_id UUID NOT NULL REFERENCES users(user_id),
  registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  event_type TEXT NOT NULL,
  data JSONB NOT NULL -- evt. noe annet
);

Om vi sorterter på event_id (som vi har som et heltall for å kunne sortere på den) er det ingen garanti at de vil ha en stigende/synkende registered_at. Om vi starter opp to transaksjoner ca. samtidig er det nemlig ikke usannsynlig at vi ender opp i denne situasjonen:

Selv om registered_at vil være transaksjonstiden, er event_id en økende teller som kan sees på tvers av transaksjoner. Som bildet over viser, kan en transaksjon starte før en annen, men sette inn rader i events-tabellen etter at den andre har satt inn rader. Resultatet er at de siste radene som blir satt inn har et tidligere registreringstidspunkt, men en høyere event_id.

Vårt første forsøk på å fikse dette blir naturlig nok å ha en indeks på bruker og tidspunktet hendelsen ble registrert:

CREATE INDEX events_user_sorted
  ON events (user_id, registered_at);

Dette hjelper oss litt på vei, men holder ikke 100%. Om vi genererer flere hendelser i samme transaksjon har vi dessverre ingen garanti på hvilken rekkefølge de dukker opp i, og dette kan av og til være kritisk.

Løsningen på det blir å gjenintrodusere event_id som en del av indeksen, om registered_at har lik verdi:

CREATE INDEX events_user_sorted
  ON events (user_id, registered_at, event_id);

-- i bruk, sortert med siste hendelse først:

SELECT e.event_id, e.registered_at, e.data
FROM events e
WHERE e.user_id = :user_id
ORDER BY e.registered_at DESC, e.event_id DESC

Man skulle tro dette var nok, og i praksis er det nok det. Men om du er skikkelig uheldig kan det hende at to transaksjoner får samme NOW()-tid, og da blir det også krøll selv med denne løsningen. Er dette viktig for deg kan du f.eks. putte en lås på brukeren for å forsikre deg om at transaksjonene skjer i sekvens. Tidspunktet vil være det samme, men sekvensen på event_id-ene sørger for at hendelsene til en av transaksjonene kommer først, for så den andre.

Det er verdt å nevne at clock_timestamp() finnes, og returnerer det faktiske tidspunktet den blir kalt. I noen tilfeller vil dette fungere bra, men det hjelper ikke i eksempelet over hvor vi ønsker å garantere at hendelser i samme transaksjon kommer rett etter hverandre.

Hvilken tidssone sa du?

Jeg har jo skrevet litt om tidssoner tidligere på denne bloggen, for selv uten databaser kan de bli et svare styr. Med databaser blir ting som oftest enda mer kronglete.

En del av forvirringen for PostgreSQL-brukere er at man har to typer man kan bruke for å jobbe med tidspunkt:

  1. timestamp [ without time zone ]
  2. timestamp with time zone, også kjent som timestamptz

Som du kanskje skjønner, så håndterer ikke timestamp without time zone tidssoner. Om du sender inn et tidspunkt med tidssone, for eksempel "2003-04-12 04:05:06 America/New_York", så er det akkurat det samme som å sende inn tidspunktet "2003-04-12 04:05:06", og det er dette du vil få tilbake.

Følgelig ville man kanskje tro at timestamp with time zone håndterer tidssoner ved å lagre dem. Vel, la oss prøve å putte denne bloggposten inn i en tabell med publiseringstidspunkt og se hva som skjer:

CREATE TABLE bloggposter (
  tittel TEXT NOT NULL,
  publisert TIMESTAMPTZ NOT NULL
  -- kunne også brukt TIMESTAMP WITH TIME ZONE
);

INSERT INTO bloggposter (tittel, publisert)
VALUES ('I PostgreSQL er et aar 360 dager (og annen moro)',
        '2024-06-26 12:04:22.293 Europe/Oslo');

Om vi så henter denne ut får vi dette:

postgres=# SELECT * FROM bloggposter;

-[ RECORD 1 ]-----------------------------------------------
tittel    | I PostgreSQL er et aar 360 dager (og annen moro)
publisert | 2024-06-26 10:04:22.293+00

Hva skjedde med tidssonen? Som du ser har PostgreSQL faktisk konvertert tidssonen fra norsk tid til UTC. UTC er det PostgreSQL-serveren bruker som standard tidssone, men om jeg ønsker at min tilkobling til databasen konverterer det til en annen tidssone er det lett å ordne:

postgres=# SET TIMEZONE TO 'JAPAN';
SET
postgres=# SELECT * FROM bloggposter;

-[ RECORD 1 ]-----------------------------------------------
tittel    | I PostgreSQL er et aar 360 dager (og annen moro)
publisert | 2024-06-26 19:04:22.293+09

Det timestamp with time zone gjør er at den tar tidssonen, konverterer tidspunktet til UTC, og så lagrer det til disk. Når noen så ønsker å hente dette ut fra databasen igjen, ser den på tidssonen tilkoblingen har og returnerer tidspunktet i den tidssonen.

Dette er ikke intuitivt, for man skulle tro at with time zone betydde at tidspunktet ble lagret med tidssonen. Det er altså ikke tilfelle: Den lagrer IKKE tidssonen, men konverterer tidspunktene fra og til tidssoner når klienten kommuniserer med serveren.

Om du lagrer tidspunkt fra fortiden/nåtiden og ikke bryr deg om tidssonen er ikke dette et problem i praksis, men om du trenger å vise tidspunktet med riktig tidssone må du altså legge dette inn som et ekstra felt.

I tillegg er fremtidige tidspunkter skjøre: Vi vet ikke 100% sikkert akkurat når klokka 12:00 den 1. januar 2025 her i Norge er i UTC-tid. Det kan hende, mot alle odds, at vi bestemmer oss for å ikke gå over til vintertid denne høsten, og da vil dagens “gjett” være en time feil. Det høres usannsynlig ut og skjer sjelden, men Libya gjorde faktisk det i 2013, rett før de gikk over til vintertid. Det medførte til at blant annet tidssonedatabasen vi alle bruker hadde feil tidssoneinformasjon for Libya i et par uker.

Her er igjen løsningen å lagre tidssonen som et ekstra felt i databasen, men hvordan du sammenligner to forskjellige tidspunkter inne i PostgreSQL blir nå en litt mer kronglete affære som krever bruk av AT TIME ZONE.

Korte og lange år

Året 1582 var for noen et kort år, for under oktoberen i det året hoppet Spania, Portugal og deler av Italia rett fra torsdag den 4. til fredag den 15. Grunnen var at de gikk over fra den julianske kalenderen til den gregorianske, og følgelig ble året bare 355 dager.

De andre landene i verden fulgte etter, men det skjedde gradvis over flere århundrer. Så vidt jeg vet hoppet alle landene mellom 10 og 13 dager fremover i et stort jafs, men söta bror hadde egentlig andre planer. De hadde nemlig tenkt at det var mer fornuftig å ha en gradvis overgang, og ønsket å hoppe over alle skuddårsdagene fra 1700 til og med 1740. De begynte bra i 1700, men på grunn av den store nordiske krigen glemte de å fjerne skuddårsdagene i 1704 og 1708. Så for å unngå enda mer forvirring ga de opp forsøket og la til en ekstra skuddårsdag, 30. februar, i 1712. Til slutt gjorde de som alle andre og hoppet 11 dager framover i 1753.

Dette var nok kjekt på den tiden, men for oss stakkars utviklere er jo dette et rent mareritt. Framfor mareritt har utviklerne av PostgreSQL heller valgt en mer pragmatisk løsning på problemet: De følger den proleptiske gregorianske kalenderen, som i praksis betyr at den gregorianske kalenderen brukes for alle datoer. Det er ikke korrekt om du jobber med ting langt tilbake i tid, men for de fleste formål nå til dags er det mer enn godt nok.

Man skulle derfor tro at et år i PostgreSQL enten er 365 eller 366 dager langt. Og det er det. For det meste. Men så har du de tilfellene hvor året faktisk bare er 360 dager da.

La oss fortsette med eksempelet vi hadde tidligere:

postgres=# SELECT * FROM bloggposter;

-[ RECORD 1 ]-----------------------------------------------
tittel    | I PostgreSQL er et aar 360 dager (og annen moro)
publisert | 2024-06-26 19:04:22.293+09

Om vi går 363 dager fremover får vi følgende tidspunkt:

postgres=# CREATE TABLE framtiden (
  tid TIMESTAMPTZ NOT NULL
);
postgres=# INSERT INTO framtiden
SELECT publisert + '363 days' FROM bloggposter;
postgres=# SELECT tid FROM framtiden;

            tid
----------------------------
 2025-06-24 10:04:22.293+00

Ikke noe hokus pokus der, og som du ser er datoen mindre enn et år fram i tid:

postgres=# SELECT framtiden.tid < bloggposter.publisert + '1 year'
FROM framtiden, bloggposter;

 ?column?
----------
 true

men her begynner moroa. Om vi gjør om litt på uttrykket får vi et helt annet svar:

postgres=# SELECT framtiden.tid - bloggposter.publisert
FROM framtiden, bloggposter;

 ?column?
----------
 363 days

postgres=# SELECT framtiden.tid - bloggposter.publisert < '1 year'
FROM framtiden, bloggposter;
 ?column?
----------
 false

postgres=# SELECT INTERVAL '363 days' < INTERVAL '1 year';
 ?column?
----------
 false

… altså, hæ? Tydeligvis er 363 dager lengre enn 1 år i Postgres? Vel, vi får grave litt mer i dokumentasjonen da. Og etter litt graving finner vi noe langt nede i denne seksjonen:

Internally, interval values are stored as three integral fields: months, days, and microseconds. These fields are kept separate because the number of days in a month varies, while a day can have 23 or 25 hours if a daylight savings time transition is involved.

Ok, nå begynner dette å bli litt mer klart: Intervallene lagrer måned, dag og mikrosekunder fordi både måned og dag kan ha en variabel lengde. Det gir jo mening, for ikke alle måneder har 30 dager, og ikke alle døgn har 24 timer. Det som skaper krøll og forvirring er det at vi kan sammenligne disse intervallene, selv om de ikke egentlig burde kunne sammenlignes:

postgres=# SELECT INTERVAL '1 day' = INTERVAL '24 hours';
 ?column?
----------
 true

postgres=# SELECT INTERVAL '1 month' = INTERVAL '30 days';
 ?column?
----------
 true

postgres=# SELECT '2024-01-31'::DATE + interval '1 month',
                  '2024-01-31'::DATE + interval '30 days';
      ?column?       |      ?column?
---------------------+---------------------
 2024-02-29 00:00:00 | 2024-03-01 00:00:00

… men hvis en måned er lik 30 dager, hva er da 2 måneder?

postgres=# SELECT INTERVAL '2 months' = INTERVAL '60 days';
 ?column?
----------
 true

Ah. Da er det plutselig forståelig hvorfor et år kan ende opp som 360 dager:

postgres=# SELECT INTERVAL '12 months' = INTERVAL '360 days';
 ?column?
----------
 true

Her er dokumentasjonen noe uklar på hva som faktisk bør skje, men det vakre med at PostgreSQL er åpen kildekode er at vi kan gå inn i koden og finne ut av det selv. I filen timestamp.c fant jeg funksjonen, og kommentaren på funksjonen er nok til å bekrefte at dette er det som faktisk skjer:

/*
 *		interval_relop	- is interval1 relop interval2
 *
 * Interval comparison is based on converting interval values to a linear
 * representation expressed in the units of the time field (microseconds,
 * in the case of integer timestamps) with days assumed to be always 24 hours
 * and months assumed to be always 30 days.  To avoid overflow, we need a
 * wider-than-int64 datatype for the linear representation, so use INT128.
 */

Det er jo kjekt å vite at det er en logikk her, men personlig skulle jeg ønske PostgreSQL bare forbød det å sammenligne intervaller. Som du ser skaper det mer krøll enn noe annet, og jeg tror det er mange der ute som ikke vet at de bare ignorerer de første 5-6 dagene i året når de gjør sensitive spørringer. Si du for eksempel vil premiere gode selgere for salg det siste året. Det kan være fristende å gjøre dette:

SELECT SUM(totalpris), ansatt_id
FROM registrerte_salg
WHERE NOW() - dag <= '1 year'
GROUP BY ansatt_id

Denne spørringen vil ikke telle opp salg helt i starten av den ønskede årsperioden, og det kan jo være kjipt for noen av selgerne.

Løsningen er forsåvidt relativt lett: Aldri sammenlign intervaller. Bruk heller intervaller til å legge til/trekke fra tid på en dato eller tidspunkt, for så å sammenligne to tidspunkt i stedet. I eksempelet over vil vi da altså gjøre

SELECT SUM(totalpris), ansatt_id
FROM registrerte_salg
WHERE NOW() - INTERVAL '1 year' <= dag
GROUP BY ansatt_id

Nå er dette et noe søkt eksempel, da jeg personlig heller ville brukt

dag BETWEEN fradato AND tildato

sånn at jeg kan kjøre spørringen på et senere tidspunkt. Men det er mange steder hvor du ønsker statistikk fra det siste året, og hvor denne feilen lett kan dukke opp.

Et sted hvor forarbeid premieres

Dette er de mest alvorlige fellene jeg kjenner til som man kan gjøre med tid i PostgreSQL, men det er nok ikke de eneste. Som du ser så har jeg referert mye til dokumentasjonen, og det er ikke tilfeldig – den er veldig god.

Om du skal jobbe med tid i PosgreSQL vil jeg faktisk anbefale at du leser all dokumentasjon relatert til det: Det er ikke mye å gå gjennom, er godt skrevet og gir deg innsikt i potensielle problemer du ikke var klar over. Slikt forarbeid gjør at du slipper å forklare sjefen din hvorfor ingen rapporter inkluderer data fra den første uka i januar, eller hvorfor japanske kunder sliter med at tidspunktene de sender inn registreres som ni timer fram i tid. I mine øyne er det en investering som er gull verdt.