Re: timezone difference in timestamp?

Tom Lane wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
Thanks, that answered my question. I thought as much, but thanks for
that fine explanation.