Today I Learned

A Hashrocket project

Postgres automatically appends timezone

Dates can be notoriously hard, particularly when it comes to timezones.

If you select a date without timezone information and cast it to a data type w/timezone, Postgres will assume the timezone of the server:

select '2000-01-01'::timestamptz;
┌────────────────────────┐
│      timestamptz       │
├────────────────────────┤
│ 2000-01-01 00:00:00-06 │
└────────────────────────┘

I’m located in CST hence the -06 at the end of the time specification (6 hours before UTC time).

To use a specific timezone such as UTC, instead of your server’s timezone which can be pretty arbitrary:

select '2000-01-01'::timestamptz at time zone 'UTC';
┌─────────────────────┐
│      timezone       │
├─────────────────────┤
│ 2000-01-01 06:00:00 │
└─────────────────────┘

Postgres now displays the time in UTC. Still probably not what you expected (6am?) - Postgres infers the 00:00:00 time in your timezone and converts it to UTC (in my case adding 6 hours).

So how would you get it to show 00:00:00 and still be in UTC timezone?

select '2000-01-01 00:00:00 UTC'::timestamptz at time zone 'UTC';
┌─────────────────────┐
│      timezone       │
├─────────────────────┤
│ 2000-01-01 00:00:00 │
└─────────────────────┘

If you use timezone aware data types it is recommended to always specify the timezone when inserting data, otherwise you are in for a world of trouble.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.