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.
Tweet