Re: TIMESTAMP with a timezone offset

Raymond O'Donnell wrote:
> On 16/12/2008 19:16, Madison Kelly wrote:
>> I want to say in my WHERE clause to offset the value I am giving by X
>> number of hours and to display the column I've cast as a timestamp
>> offset by the same X hours.
>
> You could use AT TIME ZONE to shift it the required number of hours:
>
> http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
>
> I hope this helps.
>
> Ray.

I was reading that before posting, but have not been able to get it to
work. After looking at it again I think it's because I've cast the
column I restricted in the SELECT as a 'TIMESTAMP WITHOUT TIME ZONE'. So
I ALTERed the column to be 'TIMESTAMP WITH TIME ZONE' and tried again.
However, it looks like it cast the time zone on each column to my
current time zone instead of UTC. After ALTERing the column and using
the AT TIME ZONE 'EST' it returns values five hours ahead.

So now I have another question... How can I recast a column to specify
that the current values are UTC timestamps?

Thanks!

Madi