Обсуждение: Date Timezone
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.
Thanks in advance for any help.
Andrew
--
___________________________________________
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/
___________________________________________
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:'2007-12-3'::timestamp + extract(timezone from now()) * '1 second'::interval;
> 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'
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/
___________________________________________