PostgreSQL type decision
PostgreSQL TIMESTAMP vs TIMESTAMPTZ
Understand what PostgreSQL stores for timestamp and timestamptz, how session time zones affect output, and which one belongs in created_at.
Use timestamptz for a real event such as created_at, paid_at, or a meeting start. Use timestamp for a wall-clock value whose time zone is intentionally unknown or supplied elsewhere.
- timestamptz
- An instant that can be compared across time zones
- timestamp
- A local date and time without an implied location
A concrete schema
create table appointments (
starts_at timestamptz not null,
venue_time_zone text not null
);
set timezone = 'America/New_York';
select starts_at from appointments;When timestamptz is the better fit
An instant that can be compared across time zones.
When timestamp is the better fit
A local date and time without an implied location.
The tradeoff that matters
timestamptz converts input to an absolute instant and displays it in the current session time zone. It does not retain the original zone name. timestamp stores the date and clock fields as given and ignores a zone in the input.
A migration note
Changing a column type in a live database can rewrite the table, rebuild indexes, or break application assumptions. Inspect dependent foreign keys and run the conversion against production-sized data before scheduling the migration.
Questions that come up
Is timestamptz always better than timestamp?
No. The choice follows the meaning of the data and the queries you need to run. A blanket rule hides the one requirement that should decide the column.
Can I change the type later?
Usually, but the migration may require an explicit USING expression and can lock or rewrite a large table. Test the exact ALTER TABLE statement on a recent copy first.
Where can I try the schema?
Use the PostgresGUI SQL editor for syntax work or the schema designer to model the table and export PostgreSQL SQL.