How to store time in PostgreSQL - using TIMESTAMPTZ
Official PostgreSQL documentation recommends using timestamptz
(timestamp with time zone) for storing time data. This guide explains why you should use timestamptz
instead of timestamp
, how to implement it, and best practices for working with time data in PostgreSQL.
Why TIMESTAMPTZ Matters
The Problem with TIMESTAMP
When PostgreSQL stores a datetime in a timestamp
field, it drops the UTC offset, causing data loss:
-- These two different times will be stored identically
INSERT INTO events (created_at) VALUES ('2019-10-11 21:11:24+02'); -- Berlin time
INSERT INTO events (created_at) VALUES ('2019-10-11 21:11:24-06'); -- Chicago time
-- Both stored as: 2019-10-11 21:11:24 (timezone information lost)
This creates serious data integrity issues in global applications.
The Solution: TIMESTAMPTZ
timestamptz
(timestamp with time zone) preserves timezone information:
CREATE TABLE events (
event_time TIMESTAMPTZ
);
Implementation Guide
Creating New Tables
CREATE TABLE users (
id SERIAL PRIMARY KEY,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
last_login TIMESTAMPTZ
);
Modifying Existing Tables
-- Simple conversion (assumes UTC)
ALTER TABLE users
ALTER COLUMN created_at TYPE TIMESTAMPTZ,
ALTER COLUMN updated_at TYPE TIMESTAMPTZ;
Safe Migration for Production
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN created_at_new TIMESTAMPTZ;
-- Step 2: Populate with timezone assumption
UPDATE users
SET created_at_new = created_at AT TIME ZONE 'UTC'
WHERE created_at IS NOT NULL;
-- Step 3: Verify data
SELECT created_at, created_at_new FROM users LIMIT 5;
-- Step 4: Replace old column
BEGIN;
ALTER TABLE users DROP COLUMN created_at;
ALTER TABLE users RENAME COLUMN created_at_new TO created_at;
COMMIT;
Working with TIMESTAMPTZ
Inserting Data
-- Insert with explicit timezone
INSERT INTO events (event_time) VALUES ('2024-01-15 10:30:00+00');
INSERT INTO events (event_time) VALUES ('2024-01-15 10:30:00-05');
-- Insert current time
INSERT INTO events (event_time) VALUES (NOW());
Timezone Conversions
-- Convert for display in different timezones
SELECT
event_time,
event_time AT TIME ZONE 'America/New_York' AS ny_time,
event_time AT TIME ZONE 'Europe/London' AS london_time
FROM events;
Filtering by Time Ranges
-- Always use explicit timezone for filters
SELECT * FROM events
WHERE event_time >= '2024-01-01 00:00:00+00'
AND event_time < '2024-02-01 00:00:00+00';
Managing Session Timezone
-- Set timezone for current session
SET timezone = 'America/New_York';
-- Check current timezone
SHOW timezone;
Practical Example
-- Create orders table
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Insert and display in different timezones
INSERT INTO orders (customer_id, order_date)
VALUES (123, '2024-06-15 14:30:00+00');
SELECT
id,
order_date,
order_date AT TIME ZONE 'America/Los_Angeles' AS local_time
FROM orders;
Summary
Using timestamptz
instead of timestamp
prevents timezone-related data loss and ensures your application works correctly across different timezones.
Key Benefits:
- Preserves timezone information - No data loss during storage
- Consistent behavior - Predictable timezone conversions
- Global application support - Works correctly worldwide
- Framework compatibility - Supported by all major ORMs
Remember: Always prefer timestamptz
for any timestamp that represents a specific moment in time, especially for user-generated data.