Date Timezone

Andrew Maclean wrote:

I have a problem in that dates are stored as UTC in my dataase. I.e. without
timezone.

What I want to do: is to get this date from the server and add the server's
current time zone to it. Then I will present a list of dates and times to
the user in their time-zone. How can I do this? I have been trying to get
the timezone from now() and adding it but I can't see how.

alvherre=# select extract(timezone from now());
date_part
-----------
-10800
(1 row)

--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)

On Nov 22, 2007 3:16 PM, Andrew Maclean <andrew.amaclean@gmail.com> wrote:

I have a problem in that dates are stored as UTC in my dataase. I.e. without
timezone.

What I want to do: is to get this date from the server and add the server's
current time zone to it. Then I will present a list of dates and times to
the user in their time-zone. How can I do this? I have been trying to get
the timezone from now() and adding it but I can't see how.

Of course this doesn't account for daylight saving.

Does this help?

http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

Andrew Maclean wrote:

This extracts the time zone in seconds, but how do I add it to the datetime
that I have?

This is what I have been trying but it doesn't work:
'2007-12-3'::timestamp + interval 'extract(timezone from now()) seconds'

'2007-12-3'::timestamp + extract(timezone from now()) * '1 second'::interval;

Please copy the list on replies.

--
Alvaro Herrera http://www.advogato.org/person/alvherre
"En el principio del tiempo era el desencanto. Y era la desolaci�n. Y era
grande el esc�ndalo, y el destello de monitores y el crujir de teclas."
("Sean los P�jaros Pulentios", Daniel Correa)

Thankyou very much.

You have fixed my synax issues!

It is much appreciated.

Andrew

On Nov 23, 2007 8:51 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

Andrew Maclean wrote:

This extracts the time zone in seconds, but how do I add it to the

datetime

that I have?

This is what I have been trying but it doesn't work:
'2007-12-3'::timestamp + interval 'extract(timezone from now()) seconds'

'2007-12-3'::timestamp + extract(timezone from now()) * '1
second'::interval;

Please copy the list on replies.

--
Alvaro Herrera
http://www.advogato.org/person/alvherre
"En el principio del tiempo era el desencanto. Y era la desolación. Y
era
grande el escándalo, y el destello de monitores y el crujir de teclas."
("Sean los Pájaros Pulentios", Daniel Correa)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
___________________________________________
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney 2006 NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___________________________________________