|
|
Posted by Michael on 10/29/07 05:06
Your suggestion of using a MySQL DATETIME field because they allow dates
from 0000-9999 is then contradicted by saying 'so you might want to go
via Unix timestamp to get back to a local time using set_locale() and
date().'.
There is no point of storing it in a DATETIME field if you are going to
be using a UNIX_TIMESTAMP() call anywhere.
You are right to store the UNIX timestamp from TIME() in the database as
an integer. The timestamp will always be in GMT and is easily worked
with and converted amongst timezones.
I'd suggest not to use MySQL DATETIME unless you specifically require
dates that cannot be stored in a UNIX Timestamp.
Using a MySQL DATETIME field can be troublesome with timezones, and even
worse if the Database server is not on the same server as the PHP script.
Also, remember to use strtotime('+7 days', $timestamp) instead of $date
+= 7*24*60*60 as these functions handle leap years and DST changes
correctly.
Also, try date_default_timezone_set(), if your box has it's timezone's
mis-configured. List of timezones can be found here:
http://au.php.net/manual/en/timezones.php
Regards,
Michael
C. wrote:
> On 10 Oct, 12:03, Ben Bradley <no...@nowhere.net> wrote:
>> My question relates to what the best way is to handle dates/times with
>> regards to daylight savings time.
>> And we've managed to get ourselves confused over it all in the process.
>>
>> At the moment we've been storing all dates/times as Unix timestamps in
>> our MySQL database, as a standard integer field. Which we noticed was
>> the same way phpBB does it, so it can't be a bad idea.
>>
>
> Not the way I'd do it - Unix timestamps only operate for a realtively
> narrow timeband. I'd strongly recommend you use MySQL DATETIME fields
> and format them from Zulu/UTC/GMT (your truly predates the Unix
> epoch).
>
> Last time I looked, MySQL handling of timezones was a bit ropey (but
> the format allows for dates from year 0000 to year 9999) so you might
> want to go via Unix timestamp to get back to a local time using
> set_locale() and date().
>
>> Here's my questions:
>> 1) When we're in BST (British Summer Time) which is 1hr ahead of GMT,
>> does that function provide an automatic correction?
>>
>
> It does on Unix/Linux if the Locale is set correctly and the TZ files
> are installed.
>
>> 2) The Default timezone setting in our php.ini file (as discovered from
>> phpinfo) is currently Europe/London
>> Do I need to change any timezone of Apache or MySQL on our server from
>> BST to GMT when the clocks change, in order to make sure we're storing
>> accurate timestamps with our data?
>>
>
> Certainly for Apache on *nix, it logs in GMT by default.
>
> HTH
>
> C.
>
Navigation:
[Reply to this message]
|