TIMESTAMPTZ vs TIMESTAMP: PostgreSQL-dashboards en API's

Het echte probleem: één gebeurtenis, veel interpretaties

Een gebeurtenis gebeurt één keer, maar wordt op een dozijn manieren gerapporteerd. De database slaat een waarde op, een API serialiseert hem, een dashboard groepeert hem, en elke persoon ziet het in zijn eigen tijdzone. Als een laag een andere aanname maakt, kan dezelfde rij eruitzien als twee verschillende momenten.

Daarom is TIMESTAMPTZ versus TIMESTAMP niet zomaar een voorkeur voor een datatype. Het bepaalt of een opgeslagen waarde een specifiek tijdstip (instant) voorstelt, of een wall-clock tijd die alleen in een bepaalde plaats zinvol is.

Dit is wat meestal eerst breekt: een sales-dashboard toont verschillende dagelijkse totalen in New York en Berlijn. Een uurlijkse grafiek mist een uur of heeft een gedupliceerd uur tijdens zomertijdovergangen. Een auditlog lijkt buiten volgorde omdat twee systemen het eens zijn over de datum maar niet over het werkelijke moment.

Een eenvoudig model houdt je uit de problemen:

• Storage: wat je opslaat in PostgreSQL en wat het voorstelt.
• Display: hoe je het formatteert in een UI, export of rapport.
• User locale: de tijdzone en kalenderregels van de kijker, inclusief DST.

Als je die door elkaar haalt, ontstaan stille rapportagefouten. Een supportteam exporteert “tickets gemaakt gisteren” uit een dashboard en vergelijkt dat met een API-rapport. Beide lijken logisch, maar de één gebruikte de lokale middernachtgrens van de kijker terwijl de ander UTC gebruikte.

Het doel is simpel: voor elke tijdswaarde maak twee duidelijke keuzes. Bepaal wat je opslaat en bepaal wat je toont. Diezelfde duidelijkheid moet door je datamodel, API-responses en dashboards lopen zodat iedereen dezelfde tijdlijn ziet.

Wat TIMESTAMP en TIMESTAMPTZ eigenlijk betekenen

In PostgreSQL zijn de namen misleidend. Ze lijken te beschrijven wat er wordt opgeslagen, maar ze beschrijven vooral hoe PostgreSQL input interpreteert en output formatteert.

TIMESTAMP (ook bekend als timestamp without time zone) is gewoon een kalenderdatum en kloktijd, zoals 2026-01-29 09:00:00. Er wordt geen tijdzone aan gekoppeld. PostgreSQL zal het niet voor je converteren. Twee mensen in verschillende tijdzones kunnen dezelfde TIMESTAMP lezen en verschillende echte momenten aannemen.

TIMESTAMPTZ (ook bekend als timestamp with time zone) stelt een echt punt in de tijd voor. Zie het als een instant. PostgreSQL normaliseert het intern (effectief naar UTC) en toont het vervolgens in welke tijdzone je sessie ook gebruikt.

Het gedrag achter de meeste verrassingen is:

• Bij input: PostgreSQL converteert TIMESTAMPTZ-waarden naar één vergelijkbaar instant.
• Bij output: PostgreSQL formatteert dat instant met behulp van de huidige session time zone.
• Voor TIMESTAMP: er gebeurt geen automatische conversie bij input of output.

Een klein voorbeeld toont het verschil. Stel dat je app 2026-03-08 02:30 van een gebruiker ontvangt. Als je het in een TIMESTAMP-kolom invoegt, slaat PostgreSQL precies die wall-clock waarde op. Als die lokale tijd niet bestaat vanwege een DST-sprong, merk je dat misschien pas wanneer rapportage faalt.

Als je het in TIMESTAMPTZ invoegt, heeft PostgreSQL een tijdzone nodig om de waarde te interpreteren. Als je 2026-03-08 02:30 America/New_York opgeeft, converteert PostgreSQL het naar een instant (of geeft een fout afhankelijk van regels en exacte waarde). Later toont een dashboard in Londen een andere lokale kloktijd, maar het is hetzelfde instant.

Een veelvoorkomende misvatting: mensen zien “with time zone” en verwachten dat PostgreSQL het originele tijdzone-label opslaat. Dat doet het niet. PostgreSQL slaat het moment op, niet het label. Als je de originele tijdzone van de gebruiker nodig hebt voor weergave (bijv. “toon het in de lokale tijd van de klant”), sla die zone dan apart op als tekstveld.

Session time zone: de verborgen instelling achter veel verrassingen

PostgreSQL heeft een instelling die stilletjes bepaalt wat je ziet: de session time zone. Twee mensen kunnen dezelfde query op dezelfde data uitvoeren en verschillende kloktijden krijgen omdat hun sessies verschillende tijdzones gebruiken.

Dit beïnvloedt vooral TIMESTAMPTZ. PostgreSQL slaat een absoluut moment op en toont het vervolgens in de session time zone. Bij TIMESTAMP behandelt PostgreSQL de waarde als platte kalendertijd. Het verschuift het niet voor weergave, maar de session time zone kan je nog steeds in de problemen brengen wanneer je het converteert naar TIMESTAMPTZ of vergelijkt met tijdzone-bewuste waarden.

Session time zones worden vaak gezet zonder dat je het merkt: applicatie-startconfig, driverparameters, connection pools die oude sessies hergebruiken, BI-tools met eigen defaults, ETL-jobs die serverlocale-instellingen erven, of handmatige SQL-consoles die de voorkeuren van je laptop gebruiken.

Zo komen teams vaak in onenigheid. Stel dat een event is opgeslagen als 2026-03-08 01:30:00+00 in een TIMESTAMPTZ-kolom. Een dashboard-sessie in America/Los_Angeles toont het als de voorafgaande avond lokale tijd, terwijl een API-sessie in UTC een andere kloktijd toont. Als een grafiek groepeert per dag met behulp van de session-lokale dag, kun je verschillende dagelijkse totalen krijgen.

-- Make your output consistent for a reporting job
SET TIME ZONE 'UTC';

SELECT created_at, date_trunc('day', created_at) AS day_bucket
FROM events;

Voor alles wat rapporten of API-responses produceert: maak de tijdzone expliciet. Stel het bij verbinding in (of voer eerst SET TIME ZONE uit), kies één standaard voor machine-outputs (vaak UTC), en voor “lokale bedrijfstijd”-rapporten stel je de bedrijfszone in binnen de job, niet op iemands laptop. Als je gepoolde verbindingen gebruikt, reset dan session-instellingen wanneer een verbinding wordt uitgecheckt.

Hoe dashboards breken: groeperen, buckets en DST-gaten

Dashboards lijken simpel: orders per dag tellen, aanmeldingen per uur tonen, week-op-week vergelijken. Problemen beginnen wanneer de database één “moment” opslaat maar de grafiek het verandert in veel verschillende “dagen”, afhankelijk van wie kijkt.

Als je groepeert per dag met de lokale tijd van een gebruiker, kunnen twee mensen verschillende datums zien voor hetzelfde event. Een bestelling geplaatst om 23:30 in Los Angeles is in Berlijn al “morgen”. En als je SQL groepeert met DATE(created_at) op een gewone TIMESTAMP, groepeer je niet op een echt moment. Je groepeert op een wall-clock waarde zonder tijdzone.

Uur-grafieken worden complexer rond DST. In het voorjaar gebeurt één lokaal uur nooit, dus grafieken kunnen een gat tonen. In de herfst gebeurt één lokaal uur twee keer, dus je kunt een piek of dubbele buckets krijgen als query en dashboard niet overeenkomen over welke 01:30 je bedoelt.

Een praktische vraag helpt: chart je echte momenten (veilig om te converteren), of een lokale schema-tijd (moet niet worden geconverteerd)? Dashboards willen bijna altijd echte momenten.

Wanneer groeperen op UTC versus een bedrijfstijdzone

Kies één groepeerregel en pas die overal toe (SQL, API, BI-tool), anders lopen totalen uiteen.

Groepeer op UTC wanneer je een globale, consistente serie wilt (systeemgezondheid, API-verkeer, wereldwijde aanmeldingen). Groepeer op een bedrijfstijdzone wanneer “de dag” een wettelijke of operationele betekenis heeft (winkel-dag, SLA's support, financiële afsluiting). Groepeer op de tijdzone van de kijker alleen wanneer personalisatie belangrijker is dan vergelijkbaarheid (persoonlijke activiteitsfeeds).

Hier is het patroon voor consistente “bedrijfsdag”-groepering:

SELECT date_trunc('day', created_at AT TIME ZONE 'America/New_York') AS business_day,
       count(*)
FROM orders
GROUP BY 1
ORDER BY 1;

Labels die wantrouwen voorkomen

Mensen verliezen vertrouwen in grafieken als cijfers springen en niemand kan uitleggen waarom. Label de regel duidelijk in de UI: “Daily orders (America/New_York)” of “Hourly events (UTC)”. Gebruik dezelfde regel in exports en API's.

Een eenvoudige regelsuite voor rapportage en API's

Bepaal of je een instant in de tijd opslaat of een lokale kloklezing. Het mengen van die twee is waar dashboards en API's beginnen te verschillen.

Een regelsuite die rapportage voorspelbaar houdt:

• Sla echte gebeurtenissen op als instants met TIMESTAMPTZ, en behandel UTC als de bron van waarheid.
• Sla bedrijfsconcepten zoals “billing day” apart op als DATE (of een lokaal-tijdveld als je echt wall-clock tijd nodig hebt).
• Retourneer in API's timestamps in ISO 8601 en wees consistent: includeer altijd een offset (zoals +02:00) of gebruik altijd Z voor UTC.
• Converteer aan de randen (UI en rapportagelaag). Vermijd heen-en-weerconversies binnen database-logica en achtergrondjobs.

Waarom dit werkt: dashboards bucketen en vergelijken ranges. Als je instants (TIMESTAMPTZ) opslaat, kan PostgreSQL betrouwbaar events ordenen en filteren, zelfs wanneer DST verschuift. Dan kies je hoe je ze toont of groepeert. Als je een lokale kloktijd (TIMESTAMP) opslaat zonder tijdzone, kan PostgreSQL niet weten wat het betekent, dus kan groeperen veranderen wanneer de session time zone verandert.

Houd “lokale bedrijfsdatums” apart omdat het geen instants zijn. “Bezorg op 2026-03-08” is een datumbeslissing, geen moment. Als je dit in een timestamp perst, kunnen DST-dagen ontbrekende of gedupliceerde lokale uren creëren, die later als gaten of pieken verschijnen.

Stap voor stap: het juiste type kiezen voor elke tijdswaarde

Kiezen tussen TIMESTAMPTZ en TIMESTAMP begint met één vraag: beschrijft deze waarde een echt moment dat plaatsvond, of een lokale tijd die je exact wilt bewaren?

1) Scheid echte gebeurtenissen van geplande lokale tijden

Maak snel een inventaris van je kolommen.

Echte gebeurtenissen (klikken, betalingen, logins, zendingen, sensormetingen, supportberichten) moeten meestal als TIMESTAMPTZ worden opgeslagen. Je wilt één onmiskenbaar instant, ook al bekijken mensen het vanuit verschillende tijdzones.

Geplande lokale tijden zijn anders: “Winkel opent om 09:00”, “Ophaalvenster is 16:00 tot 18:00”, “Facturatie draait op de 1e om 10:00 lokale tijd”. Die zijn vaak beter als TIMESTAMP plus een apart tijdzoneveld, omdat de intentie verbonden is met de wall-clock van een locatie.

2) Kies een standaard en leg het vast

Voor de meeste producten is een goede default: sla eventtijden op in UTC, presenteer ze in de tijdzone van de gebruiker. Documenteer het op plekken die mensen echt lezen: schema-notities, API-docs en dashboardbeschrijvingen. Definieer ook wat “bedrijfsdag” betekent (UTC-dag, bedrijfszone-dag of kijker-locatie-dag), want die keuze bepaalt dagelijkse rapportage.

Een korte checklist die in de praktijk werkt:

• Label elke tijdkolom als “event instant” of “local schedule”.
• Default event instants naar TIMESTAMPTZ opgeslagen in UTC.
• Bij schema-wijzigingen: backfill zorgvuldig en valideer steekproefsgewijs rijen handmatig.
• Standaardiseer API-formaten (inclusief altijd Z of een offset voor instants).
• Stel de session time zone expliciet in ETL-jobs, BI-connectors en background workers.

Wees voorzichtig met “convert and backfill”-werk. Het wijzigen van een kolomtype kan de betekenis stilletjes veranderen als oude waarden zijn geïnterpreteerd onder een andere session time zone.

Veelgemaakte fouten die off-by-one-day en DST-bugs veroorzaken

De meeste tijdfouten zijn geen “PostgreSQL die raar doet.” Ze ontstaan door het opslaan van de er goed-uitziende waarde met de verkeerde betekenis, en vervolgens verschillende lagen laten raden naar de ontbrekende context.

Fout 1: Een wall-clock tijd opslaan alsof het absoluut is

Een veelgemaakte valkuil is het opslaan van lokale wall-clock tijden (zoals “2026-03-29 09:00” in Berlijn) in een TIMESTAMPTZ. PostgreSQL behandelt het als een instant en converteert het op basis van de huidige session time zone. Als de bedoelde betekenis was “altijd 9 uur lokale tijd”, ben je dat kwijt. Hetzelfde rij wordt onder een andere session time zone met een ander uur weergegeven.

Voor afspraken: sla de lokale tijd op als TIMESTAMP plus een apart tijdzone- (of locatie-) veld. Voor gebeurtenissen die op een moment gebeurden (betalingen, logins), sla het instant op als TIMESTAMPTZ.

Fout 2: Verschillende omgevingen, verschillende aannames

Je laptop, staging en productie delen mogelijk niet dezelfde tijdzone. De ene omgeving draait in UTC, de andere in lokale tijd, en “group by day”-rapporten beginnen te verschillen. De data veranderde niet; de session setting wel.

Fout 3: Tijdfuncties gebruiken zonder te weten wat ze beloven

now() en current_timestamp zijn stabiel binnen een transactie. clock_timestamp() verandert bij elke oproep. Als je timestamps op meerdere punten in één transactie genereert en deze functies mixt, kunnen ordening en duur vreemd lijken.

Fout 4: Twee keer (of nul keer) converteren

Een veelvoorkomende API-bug: de app converteert een lokale tijd naar UTC, stuurt het als een naive string, en de database-sessie converteert opnieuw omdat die de input als lokaal aanneemt. Het tegenovergestelde gebeurt ook: de app stuurt een lokale tijd maar labelt het met Z (UTC), waardoor het wordt verschoven bij weergave.

Fout 5: Groeperen op datum zonder de bedoelde tijdzone te noemen

“Dagelijkse totalen” hangt af van welke daggrens je bedoelt. Als je groepeert met date(created_at) op een TIMESTAMPTZ, volgt het resultaat de session time zone. Late-night events kunnen in de vorige of volgende dag vallen.

Voordat je een dashboard of API uitrolt, controleer de basics: kies per grafiek één rapportage-tijdzone en pas die consequent toe, includeer offsets (of Z) in API-payloads, houd staging en productie gelijk betreffende tijdzonebeleid, en wees expliciet over welke tijdzone je bedoelt bij groeperen.

Snelle checks voordat je een dashboard of API uitrolt

Tijdfouten komen zelden door één slechte query. Ze ontstaan omdat opslag, rapportage en de API elk een iets andere aanname doen.

Gebruik een korte pre-release checklist:

• Voor echte gebeurtenissen (aanmeldingen, betalingen, sensorpings) sla het instant op als TIMESTAMPTZ.
• Voor bedrijfs-lokale concepten (billing day, reporting date) sla een DATE of TIME op, niet een timestamp die je later “wil converteren”.
• Zet de session time zone doelbewust in geplande jobs en rapport-runners.
• In API-responses: includeer een offset of Z, en bevestig dat de client het parseert als tijdzone-bewust.
• Test de DST-overgangsweek voor ten minste één doelzone.

Een snelle end-to-end-validatie: kies één bekend edge-case event (bijvoorbeeld 2026-03-08 01:30 in een DST-observerende zone) en volg het door opslag, query-output, API-JSON en het uiteindelijke grafieklabel. Als de grafiek de juiste dag toont maar de tooltip het verkeerde uur (of omgekeerd), heb je een conversiemismatch.

Voorbeeld: waarom twee teams het oneens zijn over dezelfde dagcijfers

Een supportteam in New York en een finance-team in Berlijn kijken naar hetzelfde dashboard. De database-server draait op UTC. Iedereen beweert dat zijn cijfers kloppen, maar “gisteren” is verschillend afhankelijk van wie je het vraagt.

Hier is het event: een klantticket wordt aangemaakt om 23:30 in New York op 10 maart. Dat is 04:30 UTC op 11 maart, en 05:30 in Berlijn. Eén echt moment, drie verschillende kalenderdatums.

Als de creatietijd van het ticket is opgeslagen als TIMESTAMP (zonder tijdzone) en je app gaat ervan uit dat het “lokaal” is, kun je stilletjes de geschiedenis herschrijven. New York behandelt 2026-03-10 23:30 mogelijk als New York-tijd, terwijl Berlijn diezelfde opgeslagen waarde als Berlijn-tijd interpreteert. Dezelfde rij valt op verschillende dagen voor verschillende kijkers.

Als het is opgeslagen als TIMESTAMPTZ, slaat PostgreSQL het instant consistent op en converteert het alleen wanneer iemand het bekijkt of formatteert. Daarom verandert TIMESTAMPTZ versus TIMESTAMP wat “een dag” betekent in rapporten.

De oplossing is om twee ideeën te scheiden: het moment waarop het event gebeurde, en de rapportagedatum die je wilt gebruiken.

Een praktisch patroon:

1. Sla de eventtijd op als TIMESTAMPTZ.
2. Bepaal de rapportageregel: viewer-lokaal (persoonlijke dashboards) of één bedrijfszone (bedrijf brede finance).
3. Bereken de rapportagedatum bij query-tijd met die regel: converteer het instant naar de gekozen zone en neem vervolgens de datum.

Volgende stappen: standaardiseer tijdverwerking in je stack

Als tijdbeheer niet is opgeschreven, wordt elk nieuw rapport een gokspel. Streef naar tijdgedrag dat saai en voorspelbaar is in database, API's en dashboards.

Schrijf een kort “tijdcontract” dat drie vragen beantwoordt:

• Event time standard: sla event-instants op als TIMESTAMPTZ (meestal in UTC) tenzij je een sterke reden hebt om dat niet te doen.
• Business time zone: kies één zone voor rapportage en gebruik die consequent wanneer je “dag”, “week” en “maand” definieert.
• API format: stuur altijd timestamps met een offset (ISO 8601 met Z of +/-HH:MM) en documenteer of velden “instant” of “lokale wall time” betekenen.

Voeg kleine tests toe rond DST-begin en DST-einde. Die vangen dure bugs vroeg. Valideer bijvoorbeeld dat een “dagelijkse totaal”-query stabiel blijft voor een vaste bedrijfszone over een DST-wissel, en dat API-inputs zoals 2026-11-01T01:30:00-04:00 en 2026-11-01T01:30:00-05:00 als twee verschillende instants worden behandeld.

Plan migraties zorgvuldig. Het veranderen van types en aannames ter plaatse kan stilletjes de geschiedenis in grafieken herschrijven. Een veiligere aanpak is om een nieuwe kolom toe te voegen (bijvoorbeeld created_at_utc TIMESTAMPTZ), deze te backfillen met een nagekeken conversie, reads bij te werken om de nieuwe kolom te gebruiken en writes te updaten. Houd oude en nieuwe rapporten een korte tijd naast elkaar zodat verschuivingen in dagelijkse cijfers duidelijk zijn.

Als je één plek wilt om dit “tijdcontract” af te dwingen over datamodellen, API's en schermen, helpt een unified build-setup. AppMaster (appmaster.io) genereert backend, webapp en API's uit één project, wat het makkelijker maakt om timestamp-opslag- en weergaveregels consistent te houden naarmate je app groeit.