Thread: timestamps, epoch and time zones
Hello,
Maybe it’s a question for pgsql-novice, but I have a problem converting to and from unix times. In particular, it seems that a round trip unix->PG->unix does not return the original timestamp:
select extract(epoch from timestamp 'epoch');
date_part
-----------
-3600
Shouldn’t that be zero? My timezone is Europe/London (but does it matter?).
Thanks
Alberto
On Fri, Jan 22, 2010 at 11:45:30AM -0000, Alberto Colombo wrote: > select extract(epoch from timestamp 'epoch'); > > date_part > ----------- > -3600 > > Shouldn't that be zero? My timezone is Europe/London (but does it > matter?). Writing "timestamp" like that says that you want the time in your current timezone. Your locale says that that's one hour out and hence PG modifies the epoch for your current timezone. I think you want to be using "timestamptz" or "timestamp with time zone" if you don't want this correction performed. The timezone stuff is all a bit fuzzy in my head and I just tend to swap between the two depending on which does the "right thing" in each instance. Not sure if this is just because I've not used them enough, or because there should be more options to make the complexity more obvious. -- Sam http://samason.me.uk/
Thanks, I also think that the whole time/zone bit is a bit confusing. My major mistake, probably, was that I was interchanging timestamp and timestamptz (because I didn't know this latter form and timestamp with time zone is quite a mouthful!). I'll make more experiments and see if I can get it to work. Regards Alberto -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sam Mason Sent: 22 January 2010 12:25 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] timestamps, epoch and time zones On Fri, Jan 22, 2010 at 11:45:30AM -0000, Alberto Colombo wrote: > select extract(epoch from timestamp 'epoch'); > > date_part > ----------- > -3600 > > Shouldn't that be zero? My timezone is Europe/London (but does it > matter?). Writing "timestamp" like that says that you want the time in your current timezone. Your locale says that that's one hour out and hence PG modifies the epoch for your current timezone. I think you want to be using "timestamptz" or "timestamp with time zone" if you don't want this correction performed. The timezone stuff is all a bit fuzzy in my head and I just tend to swap between the two depending on which does the "right thing" in each instance. Not sure if this is just because I've not used them enough, or because there should be more options to make the complexity more obvious. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general