🕗 A practical guide to Timestamp and time zone in Postgres 🐘

A deformed postgres clock

In this practical guide, we’ll look at how to use timestamps and timestamps with time zone in the database PostgreSQL.
The wonderful Postgres doc does a good job explaining the timestamp system, you should read it first.
It does not really tell you how to use timestamps in practice though, so let’s try to fix that !

What’s a timestamp (tm)?

A timestamp is a precise moment in time, expressed in universal time, aka the UTC +00 time zone, which is based on the prime meridian, somewhere between London and Paris.

So whenever you use a naked timestamp (without a timezone), the agreement is that it’s implicitly at UTC +00.

What’s a timestamp with time zone (timestamptz) (tmtz)?

A time stamp with time zone is a precise moment expressed in a local time, relative to the UTC+00 time zone. I work in Boston.

So 2022-11-30 15:57:40 -05 means it 15:57 in the timezone that is 5 hours before UTC+00.
So 2022-11-30 15:57:40 -05 is strictly equivalent to 2022-11-30 20:57:40 +00, that is the timestamp in UTC+00.

You can also specify a time zone by its name, which encompass the daylight changes: 2022-11-30 15:57:40 America/New_York. Depending on the date, the difference with UTC might vary to accomodate daylight savings:
Here are two examples in America/New_York , before and after daylight change : ` 2022-11-04 05:35:52 -04 and 2022-11-30 13:03:09 -05

What about the database?

The database stores your local timezone and uses it a lot.
There is a default for the whole DB < default per user < default per session (connection).
Our current default is America/New York, which is UTC-04 or UTC-05 depending on daylight savings.

• you can check it by running SHOW TIME ZONE;
• you can set it by running eg SET TIME ZONE 'UTC' ;
• you can reset it to default by running RESET TIME ZONE;

What happens when I select/insert tm/tmtz?

The behavior of using timestamp and timezone is highly non-intuitive !
The gist is that :

1. Any naked timestamp is considered UTC+00, or in your local 😿
2. Whenever timestamptz is involved, postgres uses your local time zone!!

The workaround already revolves about being very explicit and converting to UTC+00.

Now let’s dive into each conversion specific behavior regarding text, timestamp (tm) and timestamp with time zone (tmtz).

text-> tm: text to timetsamp

❓ What happens?

postgres will ignore any time zone contained in your text.

SET TIME ZONE 'America/New_York'; 
    SELECT '2022-11-30 15:57:40 -05'::timestamp::text;
        --> /!\ 2022-11-30 15:57:40
SET TIME ZONE 'UTC';
    SELECT '2022-11-30 15:57:40 -05'::timestamp::text;
        --> /!\ 2022-11-30 15:57:40

--> time zone was fully ignored, this is surprising !

⚙️ Workaround

Explicitly convert your string to UTC

SET TIME ZONE 'America/New_York'; 
    SELECT timezone('utc','2022-11-30 15:57:40 -05')::timestamp::text;
        --> [X] 2022-11-30 20:57:27
SET TIME ZONE 'UTC';
    SELECT timezone('utc','2022-11-30 15:57:40 -05')::timestamp::text;
        --> [X] 2022-11-30 20:57:27

text -> tmtz : text to timetsamptz

❓ What happens?

Postgres correctly understands the time zone you provided and convert it to be displayed in your local time zone.

--> text to tmtz    
SET TIME ZONE 'America/New_York'; 
    SELECT '2022-11-30 15:57:40 -05'::timestamptz::text;
        --> 2022-11-30 15:57:40 -05
SET TIME ZONE 'UTC';
    SELECT '2022-11-30 15:57:40 -05'::timestamptz::text;
        --> 2022-11-30 20:57:40 +00
        
--> the displayed value depend on your local, 
-- BUT they are equivalent, all good !

tm -> text: timstamp to text

❓ What happens?

What you see is what you get, so very clear and straightforward

ELECT  '2022-11-30 15:57:40'::timestamp::text;
    --> 2022-11-30 15:57:40

tmtz-> text: Timestamptz to text

❓ What happens?

☠️ postgres will convert your tmtz to your local before casting it to text.
So the results will be equivalent, but the text will depend on your local !

SET TIME ZONE 'America/New_York'; 
    SELECT '2022-11-30 15:57:40 America/New_York'::timestamptz::text;
    --> 2022-11-30 15:57:40-05

SET TIME ZONE 'UTC'; 
    SELECT '2022-11-30 15:57:40 America/New_York'::timestamptz::text;
    --> 2022-11-30 20:57:40+00
    
--> Equivalent output, 
-- but the displayed value depend on your local, which might break tests for instance

⚙️ Workaround

Manually convert your tmtz to UTC, then fabricate an output string
( tmtz → UTC tm → text → add `+00` )

-- if you want to do it manually, this function does:
 SELECT   CONCAT(
        timezone('utc'
            ,'2022-11-30 15:57:40 America/New_York'::timestamptz
            )::text
        , '+00'
    );
    --> 2022-11-30 20:57:40 +00

tm->tm : Timestamp to timestamp

❓ What happens?

✅postgres just inserts/shows what you inputted, regardless of your local time zone.

SET TIME ZONE 'America/New_York';
    SELECT '2022-11-30 15:57:40'::timestamp ; 
        -->  2022-11-30 15:57:40      

SET TIME ZONE 'UTC';
    SELECT '2022-11-30 15:57:40'::timestamp ; 
        -->  2022-11-30 15:57:40

--> both are indentical, local time zone ignored, everything assumed to be UTC+00

tmtz->tmtz : Timestamptz to timestamptz

❓ What happens?

✅Postgres will understant the provided timezone, and convert it to your local timezone.

SET TIME ZONE 'America/New_York';
SELECT '2022-11-04 09:35:52 +10'::timestamptz, '2022-11-30 18:03:09 +10'::timestamptz ;
    --> 2022-11-03 19:35:52 -0400               2022-11-30 03:03:09 -0500 

tm->tmtz : Timestamp to Timestamptz

❓ What happens?

☠️ postgres will assume the time zone is your local.

SET TIME ZONE 'America/New_York'; 
    SELECT '2022-11-30 15:57:40'::timestamptz::text;
        --> /!\ 2022-11-30 15:57:40 -05
  
SET TIME ZONE 'UTC';
    SELECT '2022-11-30 15:57:40'::timestamptz::text;
        --> /!\ 2022-11-30 15:57:40 +00

--> DANGER: you inputed the same value
  -- your local make you see different things
  -- AND your local make it different /!\

⚙️ Workaround

Manually build a tmtz in UTC using string concat.
Then postgres displays it in your local fine.

SET TIME ZONE 'EST'; 
    SELECT CONCAT('2022-11-30 20:57:27', ' UTC')::timestamptz::text;
        --> 2022-11-30 15:57:40 -05
SET TIME ZONE 'UTC' ; 
    SELECT CONCAT('2022-11-30 20:57:27', ' UTC')::timestamptz::text;
        --> 2022-11-30 20:57:27+00
        
--> all good, you inputed the same value
  -- your local make you see different things
  -- but they are equivalent

tmtz->tm: Timestamptz to Timestamp

❓ What happens?

☠️ postgres will drop the timezone altogether !
Postgres uses your time zone to express the tmtz, then ignore the time zone altogether!

SET TIME ZONE 'America/New_York'; 
    SELECT '2022-11-04 09:35:52 +00'::timestamptz
         , '2022-11-04 09:35:52 +00'::timestamptz::timestamp ; 
        --> 2022-11-04 05:35:52 -0400   
        --> 2022-11-04 05:35:52
-- /!\ : the time zone info was just dropped entirely

⚙️ Workaround

convert everything to UTC+00 to be safe:

SET TIME ZONE 'America/New_York'; 
    SELECT '2022-11-04 09:35:52 +02'::timestamptz 
         , timezone('utc','2022-11-04 09:35:52 +02')::timestamp::text;
        --> 2022-11-04 03:35:52 -0400  -- OK, timezone was used to convert to your local
        --> 2022-11-04 03:35:52        -- /!\ BAD !
        --> 2022-11-04 07:35:52        -- GOOD, timezone was used to convert to UTC

-- all good, you get what you should have !