How to check timezone stored in database

Run SELECT happened, typeof(happened) FROM events; to see how each timestamp is encoded, then SELECT datetime('now'), datetime('now','localtime'); to read the two clocks the database can write from. SQLite stores no timezone at all, so the offset between those two values is what a wrong write costs you.

Why check this

Run this before sign-off on any feature that stamps a row: an order, an audit entry, a scheduled job, a session expiry. The failure it prevents is a report that is right for three weeks and wrong for one row, because one service wrote local time where every other service reads UTC.

SQLite has no date type. A timestamp is text, an integer or a float, and the declared type on the column does not change that. Nothing in the schema records which convention a given row followed, so the check has to read the data and then read it again through the application.

Every command runs against SQLite 3.50.6 on a machine set to Europe/Kiev, UTC+3 on the verification date. PostgreSQL timestamptz and MySQL @@session.time_zone are different mechanisms and neither was verified here.

Prerequisites

• The sqlite3 shell and the date and time functions reference.
• A fixture whose four rows are the same instant, 2026-09-12 07:00:00 UTC, written four ways. Save it as events.sql and run sqlite3 events.db < events.sql.

CREATE TABLE events (
  id       INTEGER PRIMARY KEY,
  label    TEXT NOT NULL,
  happened DATETIME NOT NULL
);

INSERT INTO events VALUES (1, 'text, no zone', '2026-09-12 07:00:00');
INSERT INTO events VALUES (2, 'text with Z',   '2026-09-12T07:00:00Z');
INSERT INTO events VALUES (3, 'unix seconds',  1789196400);
INSERT INTO events VALUES (4, 'julian day',    2461295.79166667);

• A reader for the application half of the check. Save it as roundtrip.mjs next to the database.

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync('events.db');
const rows = db.prepare('SELECT id, label, happened FROM events WHERE id <= 3').all();

console.log('zone:', Intl.DateTimeFormat().resolvedOptions().timeZone);
for (const r of rows) {
  const parsed = typeof r.happened === 'number'
    ? new Date(r.happened * 1000)
    : new Date(r.happened);
  console.log(`${r.id}  ${String(r.label).padEnd(14)}  stored=${r.happened}  ->  ${parsed.toISOString()}`);
}

Steps

1. 1

   Step 1.

   Read how each row is encoded, next to the value itself.

   sqlite3 -header -column events.db "SELECT id, label, happened, typeof(happened) AS storage FROM events;"
   

   id  label          happened              storage
   --  -------------  --------------------  -------
   1   text, no zone  2026-09-12 07:00:00   text
   2   text with Z    2026-09-12T07:00:00Z  text
   3   unix seconds   1789196400            integer
   4   julian day     2461295.79166667      real

   One column declared DATETIME, three storage classes in it. Rows 1 and 2 are both text and only one carries a zone. Run it across the whole table: the convention changes on the date a service was replaced.

2. 2

   Step 2.

   Decode every row with the same function and see which one fails.

   sqlite3 -header -column events.db "SELECT id, label, datetime(happened) AS as_datetime FROM events;"
   

   id  label          as_datetime
   --  -------------  -------------------
   1   text, no zone  2026-09-12 07:00:00
   2   text with Z    2026-09-12 07:00:00
   3   unix seconds
   4   julian day     2026-09-12 07:00:00

   Row 3 returns nothing. datetime() reads a bare number as a Julian day, and 1789196400 is out of the calendar it covers, so the answer is NULL rather than an error. That row needs datetime(happened,'unixepoch').

3. 3

   Step 3.

   Read the two clocks the database can write from.

   sqlite3 -header -column events.db "SELECT datetime('now') AS utc_now, datetime('now','localtime') AS local_now, (julianday('now','localtime')-julianday('now'))*24 AS hours_offset;"
   

   utc_now              local_now            hours_offset
   -------------------  -------------------  ------------
   2026-09-12 07:44:56  2026-09-12 10:44:56  3.0

   datetime('now') is UTC. It carries no Z and no offset, so the text alone does not say so. The 3.0 is this machine's offset on the day of the run, and the size of the error a wrong default writes.

4. 4

   Step 4.

   Write one row each way, within the same second, and compare them.

   sqlite3 -header -column events.db "INSERT INTO events VALUES (5,'written as now', datetime('now')); INSERT INTO events VALUES (6,'written as localtime', datetime('now','localtime')); SELECT id, label, happened, typeof(happened) AS storage FROM events WHERE id IN (5,6);"
   

   id  label                 happened             storage
   --  --------------------  -------------------  -------
   5   written as now        2026-09-12 07:44:56  text
   6   written as localtime  2026-09-12 10:44:56  text

   Same column, same storage class, same shape, three hours apart. No pragma separates them afterwards, which is why the check belongs before the rows exist.

5. 5

   Step 5.

   Read the stored values back through the application and print the instant each one becomes.

   node --no-warnings roundtrip.mjs
   

   zone: Europe/Kiev
   1  text, no zone   stored=2026-09-12 07:00:00  ->  2026-09-12T04:00:00.000Z
   2  text with Z     stored=2026-09-12T07:00:00Z  ->  2026-09-12T07:00:00.000Z
   3  unix seconds    stored=1789196400  ->  2026-09-12T07:00:00.000Z

   Rows 1 and 2 hold the same instant in the database and no longer do in the application. A string with a space separator and no zone is parsed as local time, so 07:00 read on a UTC+3 machine becomes 04:00 UTC. Rows 2 and 3 carry their own zone and survive.

6. 6

   Step 6.

   Run the same reader under another zone. Set TZ in PowerShell, because Git Bash ignores it.

   $env:TZ = 'America/New_York'; node --no-warnings roundtrip.mjs
   

   zone: America/New_York
   1  text, no zone   stored=2026-09-12 07:00:00  ->  2026-09-12T11:00:00.000Z
   2  text with Z     stored=2026-09-12T07:00:00Z  ->  2026-09-12T07:00:00.000Z
   3  unix seconds    stored=1789196400  ->  2026-09-12T07:00:00.000Z

   The same bytes now mean 11:00 UTC. Row 1 moved seven hours between the two runs while rows 2 and 3 did not move. That is the answer to the question this page asks: the timezone is not in the database, it is in whichever machine last parsed the value.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | typeof returns text for some rows and integer for others | Two conventions in one column | Find the date the writer changed. Normalise in a migration, not in every query | | Text with no Z and no offset | The zone is a convention, not data | Decide which one the column follows, write it in the schema comment, and test the reader | | datetime(col) returns an empty cell | The value is unix seconds read as a Julian day | Add the unixepoch modifier. An empty result is not an error and no exception is raised | | hours_offset is not 0 | The host runs on a local zone | Any datetime('now','localtime') default is off by that much from the UTC rows | | The same row prints two different instants under two zones | The value is parsed as local time | Store an offset in the text or store unix seconds. Both survived step 6 |

Common mistakes

What to check next

• How to check table schema: the declared type is the thing this page shows a timestamp is free to ignore.
• How to test timezone handling: the same values on the way out, rendered for a user in another zone.
• How to test daylight saving time: the offset in step 3 is not a constant, and twice a year it moves.
• How to run jest tests with a specific timezone: step 6 as a fixed part of the suite rather than a manual run.
• Convert a timestamp across timezones: decode a single stored value without writing a query.

FAQ

How to check timezone in SQL?

SELECT datetime('now'), datetime('now','localtime'); gives the UTC clock and the host clock, and the gap is the host offset. SQLite has no session timezone setting to read. Other engines expose one, which was not verified here.

How to check db timezone when every value is a number?

Read one row with datetime(col,'unixepoch') and compare it against an event whose real time you know. Unix seconds are defined against UTC, so a number is unambiguous. The ambiguity lives in text columns.

Should timestamps be stored as text or as an integer?

Step 6 answers it for the round trip: unix seconds and text carrying Z both came back unchanged under two zones, and bare text did not. Integers compare without a parser.

Does declaring the column DATETIME do anything?

It sets type affinity, nothing more. Step 1 shows text, an integer and a float sitting in one DATETIME column. Use a CHECK constraint if you want the format enforced.

Which row is the database itself going to write?

Whatever the default expression says. DEFAULT (datetime('now')) writes UTC with no marker, and DEFAULT (datetime('now','localtime')) writes the host clock. Read it with PRAGMA table_info first.

Verified