Обсуждение: BUG #1757: timestamp 'epoch' is not absolute
The following bug has been logged online: Bug reference: 1757 Logged by: Steve Bennett Email address: S.Bennett@lancaster.ac.uk PostgreSQL version: 8.0.3 Operating system: Linux Description: timestamp 'epoch' is not absolute Details: I'm seeing a rather annoying problem converting timestamps between unix and PostgreSQL. It appears that the timestamp 'epoch' is being interpreted as relative to the local timezone, rather than being an absolute time. e.g. I'm in the UK, and at the moment we're in summer time, which is UTC+1. If I do: select date_part('epoch', timestamp 'epoch'); I get: date_part ----------- -3600 Whereas if I do: select date_part('epoch', timestamp 'epoch' AT TIME ZONE 'UTC'); I get: date_part ----------- 0 Am I misunderstanding what's going on here? Is there any circumstance where 'epoch' should vary according to the timezone that you happen to be in? Thanks in advance Steve.
"Steve Bennett" <S.Bennett@lancaster.ac.uk> writes: > It appears that the timestamp 'epoch' is being interpreted as > relative to the local timezone, rather than being an absolute time. This is correct for timestamp ... you want timestamp with time zone. regards, tom lane
"Bennett, Steve" <s.bennett@lancaster.ac.uk> writes: >>> It appears that the timestamp 'epoch' is being interpreted as >>> relative to the local timezone, rather than being an absolute time. >> >> This is correct for timestamp ... you want timestamp with time zone. > Sorry, but that's dumb (IMHO). The unix epoch is not relative to the > local timezone. Sorry, but you're out of luck on that. A timestamp without time zone cannot represent any absolute time at all --- assuming that it does amounts to ascribing a timezone to it, which we don't do in general. Perhaps it would make more sense to refuse the "epoch" keyword in the context of timestamp without timezone ... > create function epoch(integer) > returns timestamp with time zone > as 'select timestamp with time zone \'epoch\' > + interval \'1 second\' * $1;' > language sql immutable; > Is there a better way? You might want to call this to_timestamp(), since that's what it's going to be called in 8.1 ;-) regards, tom lane
=20 > > It appears that the timestamp 'epoch' is being interpreted as > > relative to the local timezone, rather than being an absolute time. >=20 > This is correct for timestamp ... you want timestamp with time zone. Sorry, but that's dumb (IMHO). The unix epoch is not relative to the local timezone. I'm now using a function to convert from unix times to timestamps, since the alternative is ugly and verbose even by SQL standards... create function epoch(integer) returns timestamp with time zone as 'select timestamp with time zone \'epoch\' + interval \'1 second\' * $1;' language sql immutable; Is there a better way? Steve.