Re: 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/