Unix Calculator

    PostgreSQL Timestamps Cheatsheet

    Last updated May 2026

    PostgreSQL Timestamps Cheatsheet

    Every timestamp operation you need. Print or save as PDF.

    Timestamp Types

    -- TIMESTAMPTZ (recommended — stores UTC)
created_at TIMESTAMPTZ DEFAULT NOW()

-- TIMESTAMP (no timezone — avoid for apps)
created_at TIMESTAMP  -- timezone-naive!

-- DATE (date only, no time)
birth_date DATE

-- TIME / TIMETZ
event_time TIMETZ

-- Key difference:
-- TIMESTAMPTZ: stored as UTC, displayed in session tz
-- TIMESTAMP: stored as-is, no timezone conversion
-- ALWAYS prefer TIMESTAMPTZ for app timestamps

    Get Current Timestamp

    -- Current timestamp with timezone
NOW()                    -- TIMESTAMPTZ
CURRENT_TIMESTAMP        -- same as NOW()
TRANSACTION_TIMESTAMP()  -- start of transaction
STATEMENT_TIMESTAMP()    -- start of statement
CLOCK_TIMESTAMP()        -- actual current time

-- Current Unix timestamp (seconds)
EXTRACT(EPOCH FROM NOW())
-- → 1733529600.123456

-- Current Unix timestamp (integer)
FLOOR(EXTRACT(EPOCH FROM NOW()))::BIGINT

-- Current date / time only
CURRENT_DATE   -- → 2024-12-07
CURRENT_TIME   -- → 04:00:00+00

    Unix Timestamp ↔ TIMESTAMPTZ

    -- Unix seconds → TIMESTAMPTZ
SELECT to_timestamp(1733529600);
-- → 2024-12-07 04:00:00+00

-- Unix milliseconds → TIMESTAMPTZ
SELECT to_timestamp(1733529600000 / 1000.0);

-- TIMESTAMPTZ → Unix seconds
SELECT EXTRACT(EPOCH FROM NOW())::BIGINT;

-- TIMESTAMPTZ → Unix milliseconds
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT;

-- String → TIMESTAMPTZ
SELECT '2024-12-07T04:00:00Z'::TIMESTAMPTZ;
SELECT TO_TIMESTAMP('2024-12-07', 'YYYY-MM-DD');

    Timezone Handling

    -- Set session timezone
SET timezone = 'America/New_York';

-- Convert to specific timezone
SELECT NOW() AT TIME ZONE 'America/New_York';

-- Store as UTC, display in any timezone
SELECT created_at AT TIME ZONE 'Asia/Tokyo'
FROM events;

-- List available timezones
SELECT * FROM pg_timezone_names LIMIT 20;

-- AT TIME ZONE behavior:
-- TIMESTAMPTZ AT TIME ZONE → TIMESTAMP (no tz)
-- TIMESTAMP AT TIME ZONE → TIMESTAMPTZ

    EXTRACT & date_part

    SELECT EXTRACT(EPOCH   FROM NOW()); -- Unix ts
SELECT EXTRACT(YEAR    FROM NOW()); -- 2024
SELECT EXTRACT(MONTH   FROM NOW()); -- 12
SELECT EXTRACT(DAY     FROM NOW()); -- 7
SELECT EXTRACT(HOUR    FROM NOW()); -- 4
SELECT EXTRACT(MINUTE  FROM NOW()); -- 0
SELECT EXTRACT(SECOND  FROM NOW()); -- 0.123
SELECT EXTRACT(DOW     FROM NOW()); -- 0=Sun..6=Sat
SELECT EXTRACT(DOY     FROM NOW()); -- 342
SELECT EXTRACT(WEEK    FROM NOW()); -- ISO week 49
SELECT EXTRACT(QUARTER FROM NOW()); -- 4

-- date_part() is equivalent (older syntax)
SELECT date_part('epoch', NOW());

    Date Arithmetic

    -- Add/subtract intervals
NOW() + INTERVAL '1 hour'
NOW() + INTERVAL '7 days'
NOW() - INTERVAL '30 minutes'
NOW() + INTERVAL '1 year 2 months 3 days'

-- Duration between timestamps
ts2 - ts1  -- returns INTERVAL
EXTRACT(EPOCH FROM (ts2 - ts1))  -- seconds

-- Truncate to time boundary
date_trunc('hour',  NOW())  -- 2024-12-07 04:00
date_trunc('day',   NOW())  -- 2024-12-07 00:00
date_trunc('month', NOW())  -- 2024-12-01 00:00
date_trunc('year',  NOW())  -- 2024-01-01 00:00
date_trunc('week',  NOW())  -- Monday of week

    Indexing Timestamps

    -- Always index timestamp columns
CREATE INDEX idx_events_created
ON events(created_at);

-- Partial index for recent data
CREATE INDEX idx_events_recent
ON events(created_at)
WHERE created_at > NOW() - INTERVAL '90 days';

-- Functional index for date queries
CREATE INDEX idx_events_date
ON events(date_trunc('day', created_at));

-- BRIN index for time-series (large tables)
CREATE INDEX idx_events_brin
ON events USING BRIN (created_at);

    ⚠️ Common Mistakes

    -- ✗ WRONG — TIMESTAMP loses timezone
created_at TIMESTAMP DEFAULT NOW()

-- ✓ RIGHT
created_at TIMESTAMPTZ DEFAULT NOW()

-- ✗ WRONG — comparing TIMESTAMP to TIMESTAMPTZ
WHERE created_at > '2024-01-01'  -- ambiguous!

-- ✓ RIGHT — explicit timezone
WHERE created_at > '2024-01-01T00:00:00Z'

-- ✗ WRONG — AT TIME ZONE strips timezone
NOW() AT TIME ZONE 'UTC'  -- returns TIMESTAMP!

-- ✗ WRONG — storing Unix ts as INTEGER
-- loses precision and timezone context
ts INTEGER  -- don't do this

-- ✓ RIGHT
created_at TIMESTAMPTZ  -- always

    Migration Patterns

    -- Add new column alongside existing
ALTER TABLE events
ADD COLUMN created_at_new TIMESTAMPTZ;

-- Populate from Unix integer
UPDATE events
SET created_at_new = to_timestamp(created_at_unix);

-- Verify before dropping old column
SELECT COUNT(*) FROM events
WHERE created_at_new IS NULL;

-- Rename
ALTER TABLE events
DROP COLUMN created_at_unix;
ALTER TABLE events
RENAME COLUMN created_at_new TO created_at;

-- Convert stored strings to TIMESTAMPTZ
UPDATE events
SET created_at = created_at_str::TIMESTAMPTZ;

    Useful Queries

    -- Events in last 24 hours
SELECT * FROM events
WHERE created_at > NOW() - INTERVAL '24 hours';

-- Events today (in UTC)
SELECT * FROM events
WHERE date_trunc('day', created_at) =
      date_trunc('day', NOW());

-- Count by hour
SELECT date_trunc('hour', created_at) as hour,
       COUNT(*) as count
FROM events
GROUP BY hour ORDER BY hour;

-- Count by day for last 30 days
SELECT date_trunc('day', created_at) as day,
       COUNT(*) as count
FROM events
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY day ORDER BY day;

    unixcalculator.com · /cheatsheets · PostgreSQL reference