Re: timestamps, epoch and time zones

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sam Mason
Sent: 22 January 2010 12:25
To: pgsql-general(at)postgresql(dot)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/