Re: Times and time zones

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