Friday, August 30, 2013

PostgreSQL. Handling timestamps with timezone

Regarding timestamps and their proper handling, the world of databases is packed with bittersweet stories. With PostgreSQL this issue is rather straightforward if you are aware of the following principles:

- PostgreSQL doesn't store timezone metadata, it converts every timestamp with timezone you throw at it to UTC, and stores it that way.
- If you use timestamp without timezone fields, they're stored using the local timezone valid for the client session, in this way, we know nothing about relative to what that timestamp was stored in the database.
- To present timestamps with timezone to users it converts "on the fly" timestamps to the client's configured timezone.

show timezone;
    TimeZone    
----------------
 Mexico/General

select now();
             now              
------------------------------
 2013-08-30 00:39:55.82538-05

CREATE TABLE test_timestamptz(
id serial primary key,
mytimestamptz timestamp with time zone,
mytimestamp timestamp without time zone
);

INSERT INTO test_timestamptz SELECT 1,now(),now();

select * from test_timestamptz;
 id |        mytimestamptz         |        mytimestamp        
----+------------------------------+---------------------------
  1 | 2013-08-30 00:48:19.55422-05 | 2013-08-30 00:48:19.55422

set timezone = 'UTC';

select * from test_timestamptz;
 id |        mytimestamptz         |        mytimestamp        
----+------------------------------+---------------------------
  1 | 2013-08-30 05:48:19.55422+00 | 2013-08-30 00:48:19.55422

There are some provisions for us not to shoot ourselves in the foot when dealing with timezones, but I'll write about that later.
Cheers!

No comments:

Post a Comment