PostgreSQL Timestamps: TIMESTAMPTZ vs TIMESTAMP, Unix Epochs & Best Practices (2026)

PostgreSQL has two timestamp types, and choosing the wrong one is a production bug waiting to happen. This guide covers everything: which type to use, how to convert to and from Unix timestamps, timezone handling, date arithmetic, index performance, and the mistakes that silently corrupt your data.

The Two Timestamp Types

PostgreSQL stores timestamps in two flavors. They look similar, behave very differently, and the difference only shows up when your servers are in different timezones.

Type	Full name	Storage	Timezone-aware?	Alias
`TIMESTAMPTZ`	Timestamp with time zone	UTC internally, displayed in session TZ	✓ Yes	`TIMESTAMP WITH TIME ZONE`
`TIMESTAMP`	Timestamp without time zone	Literal value as stored, no conversion	✗ No	`TIMESTAMP WITHOUT TIME ZONE`

✅

The rule: always use TIMESTAMPTZ

Unless you have a very specific reason to store a "wall clock time" that should not be adjusted for timezones (like a recurring alarm "8 AM every day"), use TIMESTAMPTZ. It is the PostgreSQL team's own recommendation.

Why TIMESTAMP Without Timezone Is Dangerous

The issue is subtle. TIMESTAMP stores exactly what you give it — no conversion happens. If you insert '2026-05-11 14:38:32' on a server in UTC+2 and read it on a server in UTC, you get the same string back — but it now means a different moment in physical time.

The dangerous scenarioSQL

-- Server A is in UTC+2 (Europe/Paris)
INSERT INTO events (created_at) VALUES ('2026-05-11 14:38:32');
-- stored: '2026-05-11 14:38:32' — no timezone info

-- Server B is in UTC (your London server reads the same row)
SELECT created_at FROM events;
-- returns: '2026-05-11 14:38:32' — but this is now 2 hours off!

-- With TIMESTAMPTZ, the same scenario works correctly:
-- INSERT converts to UTC, SELECT converts back to session TZ
-- The physical moment in time is always preserved.

🐛

Real production bug: Your app runs fine in development (single timezone). You deploy to a multi-region setup or change your server timezone. Suddenly all your timestamps are off by 1, 2, or 5.5 hours. This is the TIMESTAMP vs TIMESTAMPTZ bug — and it's completely silent until you notice wrong data.

Unix Timestamp → PostgreSQL

TO_TIMESTAMP() converts a Unix seconds integer directly to a TIMESTAMPTZ. It always produces a timezone-aware result.

Unix seconds → TIMESTAMPTZSQL

-- Basic conversion: Unix seconds → timestamptz
SELECT TO_TIMESTAMP(1715429912);
-- → 2026-05-11 14:38:32+00

-- From a table column (API stores unix seconds as BIGINT)
SELECT
  id,
  TO_TIMESTAMP(created_at_unix) AS created_at,
  TO_TIMESTAMP(created_at_unix) AT TIME ZONE 'Europe/Paris' AS created_at_paris
FROM events;

-- From Unix milliseconds (JavaScript Date.now())
SELECT TO_TIMESTAMP(1715429912000::NUMERIC / 1000);
-- → 2026-05-11 14:38:32+00

-- Cast from string if needed
SELECT TO_TIMESTAMP('1715429912'::BIGINT);

PostgreSQL → Unix Timestamp

EXTRACT(EPOCH FROM ...) is the standard way to get a Unix timestamp from any PostgreSQL datetime value. It always returns seconds as a double precision float — cast to BIGINT for clean integer seconds.

TIMESTAMPTZ → Unix secondsSQL

-- Current time as Unix seconds
SELECT EXTRACT(EPOCH FROM NOW())::BIGINT;
-- → 1715429912

-- Specific timestamp → Unix seconds
SELECT EXTRACT(EPOCH FROM '2026-05-11T14:38:32Z'::TIMESTAMPTZ)::BIGINT;
-- → 1715429912

-- Unix milliseconds (for JavaScript compatibility)
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT;
-- → 1715429912000

-- From a table column
SELECT
  id,
  created_at,
  EXTRACT(EPOCH FROM created_at)::BIGINT AS unix_sec,
  (EXTRACT(EPOCH FROM created_at) * 1000)::BIGINT AS unix_ms
FROM events;

NOW(), CURRENT_TIMESTAMP, and Their Differences

PostgreSQL has several "get current time" functions. They are not all the same:

Current time functionsSQL

SELECT NOW();
-- → 2026-05-11 14:38:32.123456+00  (timestamptz, transaction start time)

SELECT CURRENT_TIMESTAMP;
-- → same as NOW() — transaction start time, same value for whole transaction

SELECT CURRENT_TIMESTAMP(0);
-- → 2026-05-11 14:38:32+00  (rounded to seconds, no microseconds)

SELECT CLOCK_TIMESTAMP();
-- → actual wall clock time at the moment of call (not transaction start)
-- Changes with each call within a transaction — use for logging

SELECT CURRENT_DATE;
-- → 2026-05-11  (date only, in session timezone)

SELECT CURRENT_TIME;
-- → 14:38:32.123456+00  (time only with timezone)

ℹ️

NOW() vs CLOCK_TIMESTAMP(): NOW() returns the transaction start time and is the same throughout a transaction. CLOCK_TIMESTAMP() returns the real wall-clock time at each call. Use NOW() for DEFAULT values and business logic. Use CLOCK_TIMESTAMP() for timing queries or logging that needs true elapsed time.

Timezone Conversion with AT TIME ZONE

The AT TIME ZONE operator converts between timezones. Its behavior depends on whether the input is timezone-aware or not — a subtle but important difference.

Timezone conversionSQL

-- Display a UTC timestamp in a specific timezone
SELECT NOW() AT TIME ZONE 'America/New_York';
-- → 2026-05-11 10:38:32  (timestamp WITHOUT timezone — the local time)

SELECT NOW() AT TIME ZONE 'Europe/Paris';
-- → 2026-05-11 16:38:32

SELECT NOW() AT TIME ZONE 'Asia/Tokyo';
-- → 2026-05-11 23:38:32

-- ⚠️ AT TIME ZONE on a TIMESTAMPTZ returns TIMESTAMP (naive)
-- AT TIME ZONE on a TIMESTAMP returns TIMESTAMPTZ (interprets as that TZ)

-- Set session timezone for all output
SET timezone TO 'Europe/Paris';
SELECT NOW();
-- → 2026-05-11 16:38:32+02  (displays in Paris time)
-- Stored value is still UTC — only display changes

-- Format as string with timezone
SELECT TO_CHAR(NOW() AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS TZ');
-- → 2026-05-11 10:38:32 EST

Date Arithmetic with INTERVAL

PostgreSQL's INTERVAL type makes date math readable and correct, handling DST, leap years, and month boundaries automatically.

Date arithmeticSQL

-- Add and subtract intervals
SELECT NOW() + INTERVAL '7 days';        -- 7 days from now
SELECT NOW() - INTERVAL '1 hour';        -- 1 hour ago
SELECT NOW() + INTERVAL '30 days';       -- JWT exp for 30-day token
SELECT NOW() + INTERVAL '1 year 3 months';

-- Difference between two timestamps
SELECT AGE(NOW(), '2026-01-01'::TIMESTAMPTZ);
-- → 4 mons 10 days 14:38:32.123

-- Difference in specific units
SELECT EXTRACT(EPOCH FROM (NOW() - '2026-01-01'::TIMESTAMPTZ))::INT AS seconds_since;
SELECT DATE_PART('day', NOW() - '2026-01-01'::TIMESTAMPTZ) AS days_since;

-- JWT-style: create expiration 1 hour from now (as Unix seconds)
SELECT EXTRACT(EPOCH FROM (NOW() + INTERVAL '1 hour'))::BIGINT AS exp;
-- → 1715433512

-- Check if a Unix timestamp is expired
SELECT
  EXTRACT(EPOCH FROM NOW())::BIGINT > 1715433512 AS is_expired;

Grouping with DATE_TRUNC

DATE_TRUNC() rounds a timestamp down to a given precision. It's the core of any time-series query — group by day, week, month, or hour.

DATE_TRUNC — group by time periodSQL

-- Events per day
SELECT
  DATE_TRUNC('day', created_at) AS day,
  COUNT(*) AS events
FROM events
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

-- Revenue per week (in a specific timezone)
SELECT
  DATE_TRUNC('week', created_at AT TIME ZONE 'America/New_York') AS week,
  SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

-- Available precision values:
-- microseconds, milliseconds, second, minute, hour
-- day, week, month, quarter, year, decade, century, millennium

-- Events in the last 24 hours, grouped by hour
SELECT
  DATE_TRUNC('hour', created_at) AS hour,
  COUNT(*) AS events
FROM events
WHERE created_at >= NOW() - INTERVAL '24 hours'
GROUP BY 1
ORDER BY 1;

Efficient Range Queries

Timestamp range queries are among the most common in backend systems. The key is writing them so PostgreSQL can use an index — always compare timestamps to timestamps, never to strings.

Range queries — index-friendly patternsSQL

-- ✅ Index-friendly: compare to timestamptz literal
SELECT * FROM events
WHERE created_at BETWEEN
  '2026-05-01'::TIMESTAMPTZ AND
  '2026-05-31 23:59:59'::TIMESTAMPTZ;

-- ✅ From Unix timestamps (API gives you epoch values)
SELECT * FROM events
WHERE created_at BETWEEN
  TO_TIMESTAMP(1714521600) AND
  TO_TIMESTAMP(1717200000);

-- ✅ Last N days — common pattern
SELECT * FROM events
WHERE created_at >= NOW() - INTERVAL '7 days';

-- ✅ Specific month (use DATE_TRUNC for clean boundaries)
SELECT * FROM events
WHERE created_at >= DATE_TRUNC('month', NOW())
  AND created_at < DATE_TRUNC('month', NOW()) + INTERVAL '1 month';

-- ❌ NOT index-friendly: wrapping the column in a function
-- SELECT * FROM events WHERE DATE_TRUNC('day', created_at) = '2026-05-11';
-- ↑ This forces a full table scan. Use the range pattern above instead.

Convert PostgreSQL timestamps instantly

Paste any Unix timestamp from your PostgreSQL query — UnixLi converts it to UTC, ISO 8601, local time, SQL format, and generates the PostgreSQL snippet automatically.

Open UnixLi →

Schema Best Practices

How you define your timestamp columns matters as much as how you query them.

Recommended schema patternsSQL

-- ✅ Standard table with timestamps
CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  event_type  TEXT NOT NULL,

  -- Always TIMESTAMPTZ, always with DEFAULT
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Never use TIMESTAMP (without timezone)
  -- Never use INT for timestamps (use BIGINT if you must store Unix)
);

-- ✅ Index on timestamp for range queries
CREATE INDEX idx_events_created_at ON events (created_at);

-- ✅ Composite index when filtering by both user and time
CREATE INDEX idx_events_user_time ON events (user_id, created_at DESC);

-- ✅ Auto-update updated_at with a trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON events
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Formatting with TO_CHAR

TO_CHAR() formats a timestamp as a string using format patterns similar to strftime.

TO_CHAR format patternsSQL

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');              -- → 2026-05-11
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');   -- → 2026-05-11 14:38:32
SELECT TO_CHAR(NOW(), 'DD Mon YYYY');             -- → 11 May 2026
SELECT TO_CHAR(NOW(), 'Day, DD Month YYYY');      -- → Monday  , 11 May       2026
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD"T"HH24:MI:SS"Z"'); -- → 2026-05-11T14:38:32Z (ISO 8601)
SELECT TO_CHAR(NOW(), 'Dy DD Mon HH12:MI AM TZ');  -- → Mon 11 May 02:38 PM UTC

-- Common format codes:
-- YYYY=4-digit year, MM=month, DD=day, HH24=hour(24h)
-- MI=minutes, SS=seconds, MS=milliseconds
-- TZ=timezone abbr, OF=UTC offset, TZH=TZ hours

Common PostgreSQL Timestamp Mistakes

Using TIMESTAMP instead of TIMESTAMPTZ — the most common source of timezone bugs in multi-region deployments.
Storing Unix timestamps as INT instead of BIGINT — INT overflows in 2038. Always use BIGINT for Unix seconds.
Wrapping a column in a function in WHERE — WHERE DATE_TRUNC('day', created_at) = '...' defeats the index. Use range queries instead.
Using NOW() inside functions to get per-row time — NOW() is fixed at transaction start. Use CLOCK_TIMESTAMP() if you need true wall-clock time inside a loop.
Forgetting to set timezone in queries — SET timezone TO 'UTC' at the connection level prevents surprises when your server's OS timezone changes.
Not indexing timestamp columns used in WHERE — every time-range query on an unindexed timestamp column does a sequential scan.

Quick Reference

Goal	PostgreSQL
Current Unix seconds	`EXTRACT(EPOCH FROM NOW())::BIGINT`
Current Unix milliseconds	`(EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT`
Unix seconds → TIMESTAMPTZ	`TO_TIMESTAMP(1715429912)`
Unix ms → TIMESTAMPTZ	`TO_TIMESTAMP(1715429912000::NUMERIC / 1000)`
TIMESTAMPTZ → Unix seconds	`EXTRACT(EPOCH FROM ts)::BIGINT`
Convert timezone	`ts AT TIME ZONE 'Europe/Paris'`
Format as string	`TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS')`
ISO 8601 string	`TO_CHAR(ts, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')`
Truncate to day	`DATE_TRUNC('day', ts)`
Add time	`ts + INTERVAL '7 days'`
Subtract time	`ts - INTERVAL '1 hour'`
Age / difference	`AGE(ts1, ts2)`
Diff in seconds	`EXTRACT(EPOCH FROM (ts1 - ts2))::INT`
Last 7 days	`WHERE ts >= NOW() - INTERVAL '7 days'`
Today (UTC)	`WHERE ts >= DATE_TRUNC('day', NOW())`

Summary

Three rules that prevent 95% of PostgreSQL timestamp bugs:

Always use TIMESTAMPTZ, never plain TIMESTAMP. PostgreSQL stores it as UTC and handles all timezone conversions for you.
Never wrap your timestamp column in a function in WHERE clauses. Use range comparisons (>=, <) to keep queries index-friendly.
Use BIGINT, not INT, if you must store Unix timestamps as integers. INT overflows in January 2038.

For more: Unix seconds vs milliseconds, JWT expiration in SQL, and Python datetime from timestamp.