Re: [GENERAL] TIMESTAMP with a timezone offset
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. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] TIMESTAMP with a timezone offset
Madison Kelly li...@alteeve.com writes: 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. Yeah, the default conversion from TS-without-TZ to TS-with-TZ assumes that the TS-without-TZ values are in your current timezone. You might be able to get what you want by setting timezone to UTC temporarily while doing the ALTER. However, that approach might give you headaches with inserting more data --- you might find yourself needing to keep timezone = UTC all the time, which might create troubles elsewhere. Another way to do it is, if you're starting from TS-without-TZ data that you want to consider as being in UTC, is (ts_value AT TIME ZONE 'UTC') AT TIME ZONE 'EST' The first conversion says this TS-without-TZ data is in UTC, now produce a correct TS-with-TZ from it. And then the second one rotates that back to local time in EST. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
