Rajtík's blog

← Back to home

Programming 10 min read

DUZP, Stripe and UTC: how to store financial timestamps

Two valid approaches to storing financial timestamps in a Czech SaaS - and why I chose the pragmatic one in billify.cz.

When you run a Czech SaaS with international payments, you run into a conflict that is not explicitly described in any framework's documentation: the financial date (DUZP) must always be in Europe/Prague, but the technical timestamp from the payment gateway arrives to you in UTC. Mix them at the wrong moment and this happens: a payment at 23:30 CET has a DUZP of 31 December, but the invoicing system books it only on 1 January of the next year. And you have just botched the VAT return.

This is exactly what I dealt with in the communication between captchaapi.eu (a PoW CAPTCHA API hosted in the EU) and billify.cz (the invoicing SaaS that issues its invoices). I will describe two approaches, the defensive textbook one and the pragmatic one I actually use, and explain why both work.

Why it is not trivial

DUZP - the date of the taxable supply (datum uskutečnění zdanitelného plnění) - is a term from the Czech VAT Act no. 235/2004 Coll., to which the duty to declare the tax is tied. For VAT payers and identified persons the DUZP is a mandatory item on an invoice. It determines which tax period the transaction falls into. The key thing: the DUZP is a calendar date in Czechia, not a timestamp in UTC. The state does not care that 2025-12-31 22:30:00 UTC is technically the previous calendar day. For it, it is a payment from 31 December 2025 local time.

And now the other side: Stripe (and Lemon Squeezy, and basically every modern payment gateway) sends you a webhook with created as a Unix timestamp in UTC. If a payment happens on 31 December 2025 at 23:30 CET, you get the timestamp 1735687800 → 2025-12-31 22:30:00 UTC. One bug in the conversion is enough and you send the invoice with a DUZP into the wrong year.

Two valid approaches

In Postgres there are two types for storing date and time:

  • timestamptz (= timestamp with time zone) - internally always UTC, converts according to the session timezone on read/write
  • timestamp (= timestamp without time zone) - naive, no conversion, stores exactly what it gets

From these come two architectural styles:

  1. Defensive (UTC in the DB): timestamptz + app.timezone = Europe/Prague + DB session timezone = UTC. Postgres translates automatically in between.
  2. Pragmatic (matched timezone): timestamp (without TZ) + app.timezone = Europe/Prague + DB session timezone = Europe/Prague. No conversion, naive wall-clock storage.

Both are valid, it depends on the context. Let us describe them.

The common foundation: DUZP as a date, not a timestamp

This applies to both approaches and is the most important decision in the whole schema. The DUZP is a calendar day. Store it as a timestamp with the time 00:00:00 and you are asking for trouble: which timezone? As soon as someone converts that timestamp through setTimezone(), they get a different day.

In my schema for billify.cz it looks like this (shortened):

create table public.invoices (
    id                  bigserial primary key,
    issued_at           date,           -- issue date
    taxable_supply_at   date,           -- DUZP
    due_at              date,           -- due date
    paid_at             timestamp(0),   -- moment of payment
    sent_at             timestamp(0),   -- moment of sending
    -- ...
);

All accounting dates are date - no timezone, no time, just a calendar day. All operational timestamps are timestamp(0) (without time zone, second precision). More on that in a moment.

Approach 1: Defensive (UTC in the DB + timestamptz)

This is the textbook best practice you will find in every Postgres handbook.

// config/app.php
'timezone' => 'Europe/Prague',

// config/database.php
'pgsql' => [
    // ...
    'timezone' => 'UTC',  // session TZ on connect
],

In migrations then timestampTz() instead of timestamp():

$table->timestampTz('paid_at');
$table->date('taxable_supply_at');     // DUZP stays a date
$table->timestampsTz();

How it works: Eloquent serializes the Carbon into a Y-m-d H:i:s string. Postgres interprets that string in the session timezone (UTC) and stores it as UTC. On read it converts back according to the session timezone (UTC) - Eloquent gets Y-m-d H:i:s in UTC, parses it in app.timezone (Europe/Prague), and a Carbon in the Europe/Prague time zone is created.

Advantages:

  • A defensive default - if someone ever imports data from another timezone, it is clear
  • Multi-region friendly - replicas in different regions see the same UTC values
  • Reporting tools (Metabase, Grafana) default to UTC and see the same as the DB
  • psql from another country shows UTC, which is predictable

Disadvantages:

  • NOW(), CURRENT_DATE, DATE_TRUNC in SQL return UTC values - for business queries ("invoices issued today in Prague") you have to write AT TIME ZONE 'Europe/Prague' everywhere
  • When debugging in psql you see UTC, in the application Prague - you have to translate mentally
  • For a single-region SaaS it is overkill

Approach 2: Pragmatic (matched timezone, my setup)

This is what I actually use in billify.cz. Both configs are set to Europe/Prague and the database uses a naive timestamp without time zone:

// config/app.php
'timezone' => 'Europe/Prague',

// config/database.php
'pgsql' => [
    // ...
    // Pin the PostgreSQL session timezone to Europe/Prague to
    // match app.timezone. Eloquent writes timestamps without an
    // explicit offset (`Y-m-d H:i:s`); PG interprets that as
    // session-local, so app TZ and DB session TZ must agree or
    // TIMESTAMPTZ round-trips drift by 1–2 hours. Also makes
    // SQL functions (NOW(), CURRENT_DATE, DATE_TRUNC) return
    // Czech calendar values, which is what business queries
    // ("invoices issued today", monthly quotas) need.
    'timezone' => 'Europe/Prague',
],

In migrations timestamp() (not timestampTz()):

$table->timestamp('paid_at', 0)->nullable();
$table->date('taxable_supply_at');
$table->timestamps(0);

How it works: Eloquent serializes the Carbon (in Prague) into a Y-m-d H:i:s string (wall clock in Prague). Postgres stores it in timestamp without time zone exactly as it came - no conversion. On read Eloquent gets the same string back, parses it in app.timezone (Prague), and a Carbon in Prague is created. A round trip without a single timezone conversion.

The session timezone Europe/Prague in the DB connection plays a role only for:

  • SQL functions (NOW(), CURRENT_DATE, DATE_TRUNC)
  • the timestamptz → timestamp cast (should you ever need it)

For the timestamp without time zone storage itself the session TZ is irrelevant - Postgres stores it as it came.

Advantages:

  • SELECT NOW()::date FROM invoices WHERE created_at::date = CURRENT_DATE works intuitively without AT TIME ZONE
  • In psql you see the same values as in the application - no mental translation
  • Reporting queries are shorter and more readable
  • No possibility of "drift by 1-2 hours" because of unsynchronized timezones - since no conversion takes place

Disadvantages:

  • When someone from another timezone connects to the DB without SET TIME ZONE 'Europe/Prague', they get the same strings, but NOW() returns their local time. Confusion can arise from that
  • Multi-region would require an additional decision (typically a migration to timestamptz)
  • When importing data from another timezone you have to know manually that it is wall clock in Prague and not UTC

The key point: the single place with a timezone conversion

In both approaches there is one point where a UTC timestamp from the Stripe webhook enters the system, and at that point it must be explicitly converted to Prague time.

In my setup the conversion already happens on the captchaapi.eu side, not in billify.cz. The captcha API works out the DUZP itself from the Stripe webhook and sends it to the billify API as a finished string:

// captchaapi.eu - Stripe webhook handler
use Carbon\CarbonImmutable;

public function handlePaymentSucceeded(array $event): void
{
    $paymentIntent = $event['data']['object'];

    // DUZP = today's calendar date in Prague
    $today = CarbonImmutable::now('Europe/Prague')->toDateString();

    // We send it to the billify API
    $this->billifyClient->createInvoice([
        'customer_id' => $this->resolveCustomer($paymentIntent),
        'taxable_supply_at' => $today,  // already a finished 'Y-m-d' string
        // ... other items
    ]);
}

The billify API then just stores that string in taxable_supply_at as a date. No timezone arithmetic on the billify side, a clean separation of responsibility. The only place in the whole system where the timezone is dealt with is this one line in captchaapi.eu.

The reason I use now('Europe/Prague') instead of deriving it from paymentIntent['created']: the invoice is generated at the moment of a successful payment (synchronously in the webhook handler), which usually arrives within a few seconds. For 99.9% of cases now() in Prague is the same as Stripe.created in Prague. The problem arises with a payment around midnight, when the webhook may arrive with a slight delay and slip over midnight. For absolute precision you would use:

$paidAt = CarbonImmutable::createFromTimestampUTC($paymentIntent['created']);
$today = $paidAt->setTimezone('Europe/Prague')->toDateString();

For me the difference is negligible (Stripe webhook latency is a matter of seconds and the invoice has no fixed delivery time of the service), but if you invoice something with a time-sensitive supply, use the second variant.

What not to do (applies to both approaches)

1. Do not store the DUZP as a timestamp. The DUZP is a date. Period. Store it as a timestamp and you immediately get into discussions about "which timezone is 00:00:00 in" and ask for an off-by-one on serialization.

2. Do not rely on the implicit app.timezone for accounting calculations. For accounting data always state the timezone explicitly: CarbonImmutable::now('Europe/Prague'), not CarbonImmutable::now(). Likewise ->setTimezone('Europe/Prague'), not relying on the default. A default can change; accounting logic cannot.

3. Do not mix both approaches in one DB (personal preference < consistency) If you go the pragmatic way (matched timezone + naive timestamp), do not add a single timestamptz column somewhere because it "looks more robust". Consistency within one schema is worth more than a local optimization.

4. Do not forget background workers and CLI commands. If you have a Horizon worker or php artisan schedule:run, make sure they use the same DB connection config as the web request. Otherwise the session timezone in the DB can differ and quarterly reports will start showing data from other periods.

Which approach to choose?

Context Recommendation
Czech-only SaaS, single-region Pragmatic (matched timezone, naive timestamp)
Multi-region or planned expansion Defensive (timestamptz, DB in UTC)
A team without deep Postgres knowledge Defensive (fewer surprises, the learning curve does not suit timezone hacks)
Reporting through external BI tools Defensive (BI tooling defaults to UTC)
Low frequency of data from other timezones Pragmatic (simpler queries, more readable psql)

For billify.cz I chose the pragmatic one. It is a Czech-only invoicing SaaS, the only TZ-aware point is the Stripe webhook in captchaapi.eu, and WHERE created_at::date = CURRENT_DATE is nicely readable. If I extend billify outside Czechia in the future, I will switch to timestamptz with a migration - Postgres can convert in place using ALTER COLUMN ... TYPE timestamptz USING created_at AT TIME ZONE 'Europe/Prague'.

Summary

┌─────────────────┐    ┌──────────────────────────┐    ┌────────────────────┐
│  Stripe (UTC)   │ →  │  captchaapi.eu (Prague)  │ →  │  billify.cz API    │
│  Unix timestamp │    │  ::now('Europe/Prague')  │    │  taxable_supply_at │
│                 │    │  ->toDateString()        │    │  as a 'Y-m-d' date │
└─────────────────┘    └──────────────────────────┘    └────────────────────┘

Three layers, clear roles, one conversion point between UTC and Prague. Stripe gives you UTC, captchaapi.eu produces the DUZP as a plain string in Prague, billify stores it as a date. No timezone arithmetic in the accounting logic, no doubts about "which timezone is this value in" while debugging. And the tax office is happy.