Thread: timestamp with timezone and time zone name
Hello,
I am wondering, why following two values result in a shift by 3.5 hours. I would expect them to be identical.
I understand that canonical time zone names could be ambiguous at times but I think IST is not one of them.
Any explanation?
---------------
test=# select '2012-08-07 05:24:56.758891+05:30'::timestamptz;
timestamptz
----------------------------------
2012-08-07 05:24:56.758891+05:30
(1 row)
test=# select '2012-08-07 05:24:56.758891 IST'::timestamptz;
timestamptz
----------------------------------
2012-08-07 08:54:56.758891+05:30
(1 row)
shridhar@bheem ~$ psql test
psql (9.1.4)
Type "help" for help.
test=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.7.0 20120505 (prerelease), 64-bit
(1 row)
---------------
TIA.
--
Regards
Shridhar
Shridhar Daithankar <ghodechhap@ghodechhap.net> writes:
> I am wondering, why following two values result in a shift by 3.5 hours. I
> would expect them to be identical.
> I understand that canonical time zone names could be ambiguous at times but I
> think IST is not one of them.
I don't know why you'd think that ...
src/timezone/tznames/Asia.txt:IST 19800 # Indian Standard Time
src/timezone/tznames/Asia.txt:IST 7200 # Israel Standard Time
... and there's some references to "Irish Summer Time" in the Olson
database, as well. IIRC, IST was one of the primary problems that
forced us to invent the "timezone_abbreviations" configuration
mechanism. Try setting that to "India" if you want the 05:30 meaning.
regards, tom lane
On Tuesday 07 Aug 2012 12:21:04 AM Tom Lane wrote:
> Shridhar Daithankar <ghodechhap@ghodechhap.net> writes:
> > I am wondering, why following two values result in a shift by 3.5 hours. I
> > would expect them to be identical.
> >
> > I understand that canonical time zone names could be ambiguous at times
> > but I think IST is not one of them.
>
> I don't know why you'd think that ...
>
> src/timezone/tznames/Asia.txt:IST 19800 # Indian Standard Time
> src/timezone/tznames/Asia.txt:IST 7200 # Israel Standard Time
My bad.. should have searched a bit more.
>
> ... and there's some references to "Irish Summer Time" in the Olson
> database, as well. IIRC, IST was one of the primary problems that
> forced us to invent the "timezone_abbreviations" configuration
> mechanism. Try setting that to "India" if you want the 05:30 meaning.
Thanks. I will stick to the numerical offsets for uniformity.
--
Regards
Shridhar
p, li { white-space: pre-wrap; } On Tuesday 07 Aug 2012 12:21:04 AM Tom Lane wrote:
> Shridhar Daithankar <ghodechhap@ghodechhap.net> writes:
> > I am wondering, why following two values result in a shift by 3.5 hours. I
> > would expect them to be identical.
> >
> > I understand that canonical time zone names could be ambiguous at times
> > but I think IST is not one of them.
>
> I don't know why you'd think that ...
>
> src/timezone/tznames/Asia.txt:IST 19800 # Indian Standard Time
> src/timezone/tznames/Asia.txt:IST 7200 # Israel Standard Time
My bad.. should have searched a bit more.
>
> ... and there's some references to "Irish Summer Time" in the Olson
> database, as well. IIRC, IST was one of the primary problems that
> forced us to invent the "timezone_abbreviations" configuration
> mechanism. Try setting that to "India" if you want the 05:30 meaning.
Thanks. I will stick to the numerical offsets for uniformity.
Date/time processing has lots of potential gotchas. Spend some quality-time with:
http://www.postgresql.org/docs/current/static/datatype-datetime.html
Note that short abbreviations and numerical offsets are simple offsets from UTC and will require you to change the offset as appropriate for each timestamp you want to enter. For example it is perfectly OK to enter midnight on new-year's day Eastern Daylight Time. PostgreSQL sees this as an offset of 4-hours from UTC which you could also specify as -04. Since January 1 is winter in New York, this may not be what you want.
select '2012-01-01 0000 EDT'::timestamptz at time zone 'UTC';
timezone
---------------------
2012-01-01 04:00:00
If you want PostgreSQL to account for DST rules (including how the rules have changed historically), use the timezone name:
select '2012-01-01 0000 America/New_York'::timestamptz at time zone 'UTC';
timezone
---------------------
2012-01-01 05:00:00
Cheers,
Steve