Tuesday, October 16, 2012

Getting used to with Postgresql Timestamp Conversion

0 comments

I was about to get knowledge on Postgresql Timezone conversion. The develoeprs need a query where they store a timestamp without timezone,
but when they match this timestamp with user values, database should convert both the timestamp values to one timezone.
Timezone conversion for Postgresql has described clearly in Postgresql documentation at 9.9.3.
http://www.postgresql.org/docs/9.1/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

I went through it and tested it as follows.

I have a table as follows:

create table tmptab(id int, t timestamp);

insert into tmptab
select c, '2012-10-15 16:29:00'::timestamp without time zone as t
from generate_series(1,100) as c;

System timezone is US/Eastern which I get from timezone system variable.

select id, t,
(t::timestamp with time zone at time zone 'BDT') as from_est_to_bdt ,
(t::timestamp without time zone at time zone 'BDT') as from_bdt_to_est
from tmptab 

In the above query there are two columns - from_est_to_bdt and from_bdt_to_est

from_est_to_bdt - it follows the doc Table 9-28
Expression:  timestamp with time zone AT TIME ZONE zone
Return type: timestamp without time zone
Description: Convert given time stamp with time zone to the new time zone, with no time zone designation
Here, the value of t is considering as EST as our default timezone and then convert to BDT.

from_bdt_to_est - it follows the doc Table 9-28
Expression:  timestamp without time zone AT TIME ZONE zone
Return type: timestamp with time zone 
Description: Treat given time stamp without time zone as located in the specified time zone
Here, the value of t is considering in BDT and then convert to EST in our default timezone.

So, in our application, where user is going to give us a date from different location and we need to match that date with timestamp wihtout time zone values,
I did the following query :-
select *
from tmptab
where (t::timestamp with time zone at time zone 'BDT')::date  = '2012-10-17' 
And it worked for me.

BTW, I got the timezone names from pg_timezone_names.
select * from pg_timezone_names

Leave a Reply

Labels