Thread: Date Timezone

Date Timezone

From

"Andrew Maclean"

Date:

22 November 2007, 20:16:34

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/
___________________________________________

Re: Date Timezone

From

Alvaro Herrera

Date:

22 November 2007, 20:20:27

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)

Re: Date Timezone

From

"Scott Marlowe"

Date:

22 November 2007, 20:31:02

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

Re: Date Timezone

From

Alvaro Herrera

Date:

22 November 2007, 20:51:35

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)

Re: Date Timezone

From

"Andrew Maclean"

Date:

22 November 2007, 20:59:15

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