CODEWITHSUNDEEP
postgresqltimezone
pgollangi
pgollangi

Reputation: 948

What timezone(zone, timestamp) of Postgresql does?

I am trying to add timezone to date and it is resulting a strange result. Here is my query

SELECT  timezone('GMT+5:30','2014-03-15'::date);

And resulting value is

"2014-03-14 13:00:00" (timestamp without timezone)

Can anyone explains why it is returning "2014-03-14 13:00:00" for '2014-03-15'

And my timezone is Asia/Calcutta(GMT+5:30)

Upvotes: 3

Views: 1109

Answers (1)

vyegorov
vyegorov

Reputation: 22905

As you might know, PostgreSQL allows function overloading.
To check all possible functions named timezone, run this query:

SELECT proname,pg_get_function_result(oid),pg_get_function_arguments(oid)
  FROM pg_catalog.pg_proc
 WHERE proname ~ '^timezone$';

As you see, none of them accepts date type as argument. It is quite valid in fact, as time zones have no meaning without time component.
Therefore, your input it converted to timestamp with time zone (based on the resulting datatype you've mentioned), which is 2014-03-15 00:00:00+XX, where XX depend on your location.

And, if you're located well ahead of GMT, this results in time substraction to return you timestamp at the desired zone.

What is reported by this query:

SELECT current_timestamp, setting
  FROM pg_settings
 WHERE name = 'TimeZone';

UPDATE

Yes, this is tricky stuff. I highly recommend to read through this answer a couple of times.

Please, do the following:

SET TimeZone TO 'Asia/Calcutta'; -- you don't have to do this
SELECT ts AT TIME ZONE 'UTC', ts AT TIME ZONE 'UTC' AT TIME ZONE 'UTC',
       row_number() OVER () rn
  FROM (VALUES (now()),
/* 2 */ ('2014-03-15'::date),
/* 3 */ ('2014-03-15'::date::timestamptz),
/* 4 */ ('2014-03-15'::date::timestamptz AT TIME ZONE 'UTC'),
/* 5 */ ('2014-03-15'::date::timestamptz AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'),
/* 6 */ ('2014-03-15'::timestamp),
/* 7 */ (timezone('GMT+5:30','2014-03-15')),
/* 8 */ (timezone('GMT+5:30','2014-03-15'::date)),
/* 9 */ (timezone('GMT+5:30','2014-03-15'::timestamp)),
/*10 */ (timezone('GMT+5:30','2014-03-15'::timestamp)  AT TIME ZONE 'UTC'),
/*11 */ (timezone('GMT+5:30','2014-03-15'::timestamptz)),
/*12 */ (timezone('GMT+5:30','2014-03-15'::timestamptz)  AT TIME ZONE 'UTC')) t(ts);

and check the output.

Your case corresponds to row #4 here. You do have 2014-03-14 18:30:00 here, but as you're in time zone +05:30 and function should return timestamp without time zone, you're getting 2014-03-14 13:00:00 as a result.

Please, find more bout these types in the manual and also check AT TIME ZONE construct.

Upvotes: 2

Related Questions