What is the timezone (zone, timestamp) of Postgresql?
I am trying to add a timezone for today and it gives a strange result. Here is my request
SELECT timezone('GMT+5:30','2014-03-15'::date);
And the final value
"2014-03-14 13:00:00" (timestamp without timezone)
Can anyone explain why it returns "2014-03-14 13:00:00" for "2014-03-15"
And my time zone Asia/Calcutta(GMT+5:30)
source to share
As you know, PostgreSQL allows function overloading.
To test all possible functions with a name 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 can see, none of them take a type date
as an argument. This is true, as time zones are meaningless without a time component.
So your input is converted to timestamp with time zone
(based on the datatype you mentioned), which 2014-03-15 00:00:00+XX
where XX
depends on your location.
And if you are much ahead of it GMT
, it will trigger a time stamp to get you back the time stamp in the desired zone.
What is reported for this request:
SELECT current_timestamp, setting
FROM pg_settings
WHERE name = 'TimeZone';
UPDATE
Yes, this is difficult stuff. I highly recommend reading 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 matches the line #4
here. You have it 2014-03-14 18:30:00
here, but since you are in a timezone +05:30
and the function needs to return timestamp without time zone
, you get 2014-03-14 13:00:00
the result.
Please find more bot these types in the manual and also check the constructionAT TIME ZONE
.
source to share