Re: correcting a date with a time-zone field
| From: | CPT John W. Holmes | Date: | Thu, 10 Apr 2003 15:27:01 +0000 |
| Subject: | Re: correcting a date with a time-zone field | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-143355@lists.php.net to get a copy of this message | ||
> I have two fields in a MySQL db, one is date-time and
> one is time zone, in th following format:
>
> 2003-04-10 13:08:28
> +0200
>
> Time zones can be anywhere from +1000 to -1000
>
> I need to find a way to add that time zone field to
> the date field to get a proper GMT time :
>
> 2003-04-10 15:08:28
>
> The time zone field can contain other stuff, like (PDT)
> but they always start with ([+-])(\d{2})
>
> Once I have that, I'll need to substract 2 hours to get
> the right time relative to our own time zone.
>
> Any ideas on how to achieve this ?
You can do it this way:
SELECT date_time + INTERVAL SUBSTRING(time_zone,1,3) HOURS - INTERVAL 2
HOURS FROM your_table WHERE ...
---John Holmes...