pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gavin M. Roy <gmr@ehpg.net>
To: Lynna Landstreet <lynna@spidersilk.net>
Cc: pgsql-php@postgresql.org
Subject: Re: Times and time zones
Date: Wed, 20 Jul 2005 16:36:46 -0700
Message-ID: <6F13DAFC-577D-4EE3-B170-18B13D8AA7FD@ehpg.net> (raw)
In-Reply-To: <BF04562F.1BA59%lynna@spidersilk.net>
References: <BF04562F.1BA59%lynna@spidersilk.net>

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@ehpg.net





view thread (5+ messages)  latest in thread

Message-ID: <6F13DAFC-577D-4EE3-B170-18B13D8AA7FD@ehpg.net>
Permalink:  ../6F13DAFC-577D-4EE3-B170-18B13D8AA7FD@ehpg.net/
Also on:    postgresql.org/message-id/6F13DAFC-577D-4EE3-B170-18B13D8AA7FD@ehpg.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: gmr@ehpg.net, lynna@spidersilk.net
  Subject: Re: Times and time zones
  In-Reply-To: <6F13DAFC-577D-4EE3-B170-18B13D8AA7FD@ehpg.net>
  References: <BF04562F.1BA59%lynna@spidersilk.net> <6F13DAFC-577D-4EE3-B170-18B13D8AA7FD@ehpg.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox