Re: timezone difference in timestamp?

meltedown <asdf@fake.com> writes:
> Short version: I'm trying to turn a unix timestamp into a psql
> timestamp, but there is a 5 hour difference. Is this because of
> timezones ? Can I just subtract 5 hours to get the right value ?

> "select timestamp '1970-01-01' + interval '$startofday seconds' as
> timestamp"

If it really is a Unix timestamp --- ie, referenced to midnight GMT
1970-01-01 --- then you need to start with midnight GMT not midnight
local time as the basis.  So,

select timestamp with time zone '1970-01-01 00:00 GMT' + interval ...

or better yet

select timestamp with time zone 'epoch' + interval ...

            regards, tom lane