👋
Welcome to my blog!

PostgreSQL Deep Dive Part 3 — Mastering Date, Time, and Timezone Handling

Learn how to handle dates, times, and timezones in PostgreSQL. Discover best practices for timestamp storage, timezone conversion, and avoiding common pitfalls.

PostgreSQL Deep Dive Part 3 — Mastering Date, Time, and Timezone Handling
Database
Postgres
Development

Published At

12/25/2024

Reading Time

~ 7 min read

📚 Raw Postgres Study Notes

Access complete, unfiltered study notes maintained on GitHub for easily maintaing Second Brain Dir

Time is the silent complexity that lurks in every database. What seems straightforward at first glance – storing when something happened – becomes increasingly complex as applications scale globally. Let's dive deep into PostgreSQL's datetime handling capabilities and common pitfalls that can cost teams weeks of debugging.

The Temporal Toolkit: Understanding PostgreSQL's Time-Related Types

Core Temporal Types

sql
CREATE TABLE temporal_examples (
    date_only DATE,                    -- Date without time
    time_only TIME,                    -- Time without date
    time_tz TIME WITH TIME ZONE,       -- Time with timezone (rarely useful)
    timestamp_basic TIMESTAMP,         -- Timestamp without timezone
    timestamp_tz TIMESTAMPTZ           -- Timestamp with timezone (preferred)
);
 
-- Examining storage requirements
SELECT
    pg_column_size('2024-01-31'::DATE) as date_size,
    pg_column_size('15:30:00'::TIME) as time_size,
    pg_column_size('2024-01-31 15:30:00'::TIMESTAMP) as timestamp_size,
    pg_column_size('2024-01-31 15:30:00+00'::TIMESTAMPTZ) as timestamptz_size;
 
--| date_size | time_size | timestamp_size | timestamptz_size |
--|-----------|-----------|----------------|------------------|
--|         4 |         8 |              8 |                8 |
sql
CREATE TABLE temporal_examples (
    date_only DATE,                    -- Date without time
    time_only TIME,                    -- Time without date
    time_tz TIME WITH TIME ZONE,       -- Time with timezone (rarely useful)
    timestamp_basic TIMESTAMP,         -- Timestamp without timezone
    timestamp_tz TIMESTAMPTZ           -- Timestamp with timezone (preferred)
);
 
-- Examining storage requirements
SELECT
    pg_column_size('2024-01-31'::DATE) as date_size,
    pg_column_size('15:30:00'::TIME) as time_size,
    pg_column_size('2024-01-31 15:30:00'::TIMESTAMP) as timestamp_size,
    pg_column_size('2024-01-31 15:30:00+00'::TIMESTAMPTZ) as timestamptz_size;
 
--| date_size | time_size | timestamp_size | timestamptz_size |
--|-----------|-----------|----------------|------------------|
--|         4 |         8 |              8 |                8 |

The DateStyle Setting: A Hidden Gotcha

sql
-- Check current DateStyle
SHOW DateStyle;
 
--| DateStyle |
--|-----------|
--| ISO, MDY  |
 
-- Common DateStyle formats
SELECT '1/2/2024'::DATE;      -- Depends on DateStyle (DMY vs MDY)
SELECT '2024-01-31'::DATE;    -- ISO format (always safe)
SELECT '31 Jan 2024'::DATE;   -- Unambiguous format
 
-- Explicitly setting DateStyle
SET DateStyle = 'ISO, DMY';
SELECT '1/2/2024'::DATE;      -- February 1st, 2024
 
SET DateStyle = 'ISO, MDY';
SELECT '1/2/2024'::DATE;      -- January 2nd, 2024
sql
-- Check current DateStyle
SHOW DateStyle;
 
--| DateStyle |
--|-----------|
--| ISO, MDY  |
 
-- Common DateStyle formats
SELECT '1/2/2024'::DATE;      -- Depends on DateStyle (DMY vs MDY)
SELECT '2024-01-31'::DATE;    -- ISO format (always safe)
SELECT '31 Jan 2024'::DATE;   -- Unambiguous format
 
-- Explicitly setting DateStyle
SET DateStyle = 'ISO, DMY';
SELECT '1/2/2024'::DATE;      -- February 1st, 2024
 
SET DateStyle = 'ISO, MDY';
SELECT '1/2/2024'::DATE;      -- January 2nd, 2024

Timezone Handling: The Global Challenge

Understanding Timezone Configurations

sql
-- Current timezone settings, all 3 works fine
SHOW timezone;
SHOW TIME ZONE;
SELECT current_setting('timezone');
 
--| TimeZone        |
--|-----------------|
--| America/Halifax |
 
SET TIME ZONE 'America/Chicago'; -- this will only alter timezone for session
 
-- To alter timezone on database level, which does not reset/session.
ALTER DATABASE demo SET TIME ZONE 'UTC';
 
-- You can modify the config file, and that will modify the timezone on cluster level, you have to reload the config file after update. Think before you do that, as cluster level will affect all the databases
SHOW config_file;
 
-- Available timezone names
SELECT *
FROM pg_timezone_names -- this table contains all the timezones
WHERE name LIKE 'America%'
ORDER BY name;
 
-- Timezone abbreviations (use with caution!)
SELECT *
FROM pg_timezone_abbrevs
WHERE abbrev IN ('EST', 'EDT', 'PST', 'PDT');
sql
-- Current timezone settings, all 3 works fine
SHOW timezone;
SHOW TIME ZONE;
SELECT current_setting('timezone');
 
--| TimeZone        |
--|-----------------|
--| America/Halifax |
 
SET TIME ZONE 'America/Chicago'; -- this will only alter timezone for session
 
-- To alter timezone on database level, which does not reset/session.
ALTER DATABASE demo SET TIME ZONE 'UTC';
 
-- You can modify the config file, and that will modify the timezone on cluster level, you have to reload the config file after update. Think before you do that, as cluster level will affect all the databases
SHOW config_file;
 
-- Available timezone names
SELECT *
FROM pg_timezone_names -- this table contains all the timezones
WHERE name LIKE 'America%'
ORDER BY name;
 
-- Timezone abbreviations (use with caution!)
SELECT *
FROM pg_timezone_abbrevs
WHERE abbrev IN ('EST', 'EDT', 'PST', 'PDT');

The Case for TIMESTAMPTZ

Let's explore a real-world scenario that illustrates why TIMESTAMPTZ should be your default choice:

sql
CREATE TABLE global_events (
    event_name TEXT,
    event_timestamp TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
 
-- Insert an event (server in UTC)
INSERT INTO global_events (event_name, event_timestamp)
VALUES ('Product Launch', '2024-01-31 15:00:00+00');
 
-- View the event in different timezones
SELECT event_name,
       event_timestamp AT TIME ZONE 'UTC' as utc_time,
       event_timestamp AT TIME ZONE 'America/New_York' as ny_time,
       event_timestamp AT TIME ZONE 'Asia/Tokyo' as tokyo_time
FROM global_events;
 
--|event_name    |utc_time           |ny_time            |tokyo_time       |
--|--------------|-------------------|-------------------|-----------------|
--|Product Launch|2024-01-31 15:00:00|2024-01-31 10:00:00|2024-02-01 00:00:|
sql
CREATE TABLE global_events (
    event_name TEXT,
    event_timestamp TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
 
-- Insert an event (server in UTC)
INSERT INTO global_events (event_name, event_timestamp)
VALUES ('Product Launch', '2024-01-31 15:00:00+00');
 
-- View the event in different timezones
SELECT event_name,
       event_timestamp AT TIME ZONE 'UTC' as utc_time,
       event_timestamp AT TIME ZONE 'America/New_York' as ny_time,
       event_timestamp AT TIME ZONE 'Asia/Tokyo' as tokyo_time
FROM global_events;
 
--|event_name    |utc_time           |ny_time            |tokyo_time       |
--|--------------|-------------------|-------------------|-----------------|
--|Product Launch|2024-01-31 15:00:00|2024-01-31 10:00:00|2024-02-01 00:00:|

Timezone Conversion Deep Dive

sql
-- Understanding AT TIME ZONE operator
SELECT
    -- Converting timestamptz to timestamp
    '2024-01-31 12:00:00+00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York' as to_local,
 
    -- Converting timestamp to timestamptz
    '2024-01-31 12:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York' as to_utc,
 
    -- Demonstrating the difference
    ('2024-01-31 12:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York') AT TIME ZONE 'UTC' as round_trip;
 
--| to_local            | to_utc                 | round_trip          |
--|---------------------|------------------------|---------------------|
--| 2024-01-31 07:00:00 | 2024-01-31 13:00:00-04 | 2024-01-31 17:00:00 |
sql
-- Understanding AT TIME ZONE operator
SELECT
    -- Converting timestamptz to timestamp
    '2024-01-31 12:00:00+00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York' as to_local,
 
    -- Converting timestamp to timestamptz
    '2024-01-31 12:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York' as to_utc,
 
    -- Demonstrating the difference
    ('2024-01-31 12:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York') AT TIME ZONE 'UTC' as round_trip;
 
--| to_local            | to_utc                 | round_trip          |
--|---------------------|------------------------|---------------------|
--| 2024-01-31 07:00:00 | 2024-01-31 13:00:00-04 | 2024-01-31 17:00:00 |

Few usefull queries

sql
select 'allballs'::time; -- 00:00:00
select 'epoch'::timestamp; -- 1970-01-01 00:00:00
select 'epoch'::time; -- throw error, because it is not time, it is time-stamp with date
select 'tomorrow'::timestamp;
 
select CURRENT_DATE - 1;
select CURRENT_DATE + 1;
 
-- dont use with current time
select CURRENT_TIME; -- use timestamp instead
select CURRENT_TIMESTAMP; -- can use this
select LOCALTIME; -- it will give time without any timezone
sql
select 'allballs'::time; -- 00:00:00
select 'epoch'::timestamp; -- 1970-01-01 00:00:00
select 'epoch'::time; -- throw error, because it is not time, it is time-stamp with date
select 'tomorrow'::timestamp;
 
select CURRENT_DATE - 1;
select CURRENT_DATE + 1;
 
-- dont use with current time
select CURRENT_TIME; -- use timestamp instead
select CURRENT_TIMESTAMP; -- can use this
select LOCALTIME; -- it will give time without any timezone

Best Practices and Production Tips

  1. Always Use TIMESTAMPTZ

    sql
    -- Don't do this
CREATE TABLE events (
    timestamp_notz TIMESTAMP  -- Timezone-naive
);
 
-- Do this instead
CREATE TABLE events (
    timestamp_tz TIMESTAMPTZ  -- Timezone-aware
);
    sql
    -- Don't do this
CREATE TABLE events (
    timestamp_notz TIMESTAMP  -- Timezone-naive
);
 
-- Do this instead
CREATE TABLE events (
    timestamp_tz TIMESTAMPTZ  -- Timezone-aware
);

  2. Keep the timezone in UTC as long as possible, Display in Local

    sql
    -- Application-level display conversion
SELECT to_char(
    created_at AT TIME ZONE 'America/New_York',
    'YYYY-MM-DD HH24:MI:SS TZ'
) as formatted_ny_time
FROM events;
    sql
    -- Application-level display conversion
SELECT to_char(
    created_at AT TIME ZONE 'America/New_York',
    'YYYY-MM-DD HH24:MI:SS TZ'
) as formatted_ny_time
FROM events;

  3. Use ISO 8601 Format for String Representations

    sql
    -- Always safe parsing
SELECT '2024-01-31T15:30:00Z'::TIMESTAMPTZ;
    sql
    -- Always safe parsing
SELECT '2024-01-31T15:30:00Z'::TIMESTAMPTZ;

  4. Use named timezones, not offsets (never use offsets)

    1. It will save you from day-light saving time adjustments.
    2. Save you from flip sign problem.

  5. When using date and time, it is not recommended to store them separately in different columns.

  6. Avoid using just time with timezone.

This deep dive into PostgreSQL's temporal types and timezone handling reveals the complexity of managing time in a global application context. In our next instalment, we'll explore Enums and Domains – powerful features that can enhance your database's type safety and domain modeling capabilities.

🕓

Share this article

Do you have any questions, or simply wish to contact me privately? Don't hesitate to shoot me a DM on Twitter.

Have a wonderful day.
Abhishek 🙏

Join My Exclusive Newsletter Community

Step into a world where creativity intersects with technology. By subscribing, you'll get a front-row seat to my latest musings, full-stack development resources, and exclusive previews of future posts. Each email is a crafted experience that includes:

  • In-depth looks at my covert projects and musings to ignite your imagination.
  • Handpicked frontend development resources and current explorations, aimed at expanding your developer toolkit.
  • A monthly infusion of inspiration with my personal selection of quotes, books, and music.

Embrace the confluence of words and wonder, curated thoughtfully and sent straight to your inbox.

No fluff. Just the highest caliber of ideas.