meltedown <asdf(at)fake(dot)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