Skip site navigation (1) Skip section navigation (2)

Re: Times and time zones

From: "Gavin M(dot) Roy" <gmr(at)ehpg(dot)net>
To: Lynna Landstreet <lynna(at)spidersilk(dot)net>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: Times and time zones
Date: 2005-07-20 23:36:46
Message-ID: 6F13DAFC-577D-4EE3-B170-18B13D8AA7FD@ehpg.net (view raw or flat)
Thread:
Lists: pgsql-php
Why not just tell PostgreSQL the timezone for the user and use  
timestamp with timezone and let pgsql do the rest?  I believe it's  
SET TIMEZONE '-8'; for PST/PDT, etc.

Regards,

Gavin

On Jul 20, 2005, at 4:27 PM, Lynna Landstreet wrote:

> Hello all,
>
> Back on this list after a long absence...
>
> I'm working on a project where I have to allow users to input  
> various dates
> and times, including time zone, and then later allow them to view  
> and if
> necessary edit the times they have previously entered.
>
> I've set the datatype for all the time/date columns to timestamp  
> with time
> zone, as it seems to be the only type that will store all the data  
> I need,
> but I was thrown a bit by the fact that PostgreSQL apparently then  
> converts
> all the times to GMT, setting the time zone to +00 accordingly. So  
> if a user
> enters July 20, 2005, 6:30 pm EDT (Eastern daylight savings time),  
> it's
> saved as 2005-07-20 22:30:00+00 rather than 2005-07-20 18:30:00-04.
>
> Now, this is fine for comparing different times internally, but  
> it's not so
> good for outputting the times back to the user for them to view and  
> edit.
> I've now created a second column for each time which saves the time  
> zone
> they originally entered, but I'm having trouble converting the time  
> back
> from GMT to the original. I need to write a PHP function that will  
> add or
> subtract the appropriate number of hours according to the time  
> zone, and
> while this originally seemed simple, it doesn't seem to be working out
> nearly as well as I thought.
>
> There didn't seem to be a datatype specifically for timezones, so I  
> stored
> them as strings, but now when I attempt to add the timezone to the  
> time in
> PHP, using a simple function I wrote for that, it only returns the  
> year, not
> the date or time. And when I try to modify the dates in the  
> database that
> were entered before I added the ability to specify time zones so  
> that they
> reflect the time zone they should have been entered with, in  
> phpPgAdmin, I
> get told that my input syntax is wrong no matter how I try to  
> specify the
> time - "04:00", "4 hours", pretty much anything.
>
> Can anyone give me some advice on how to handle these two issues -  
> modifying
> the times in the database via SQL, and converting the GMT times  
> back to
> their original time zones in PHP? Or even just point me at a  
> tutorial or
> reference somewhere about it? I've read through the date/time  
> functions
> section of the PHP manual and the date/time datatypes section of the
> PostgreSQL manual over and over again, and that hasn't helped.  
> Everything
> I've been able to find on the subject seems to assume you want all  
> your data
> to be in one time zone and focusses on localization for your  
> particular
> zone, not on handling data submitted by international users in a wide
> variety of time zones...
>
> Thanks,
>
> Lynna
>
> -- 
> Spider Silk Design - http://www.spidersilk.net
> 509 St Clair W Box 73576, Toronto ON Canada M6C 1C0
> Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Gavin M. Roy
800 Pound Gorilla
gmr(at)ehpg(dot)net



In response to

Responses

pgsql-php by date

Next:From: Lynna LandstreetDate: 2005-07-21 01:12:31
Subject: Re: Times and time zones
Previous:From: Frank BaxDate: 2005-07-20 23:36:14
Subject: Re: Times and time zones

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group