MortimerCat
asked on
Converting GMT to everyones timezone.
PHP Version 5.0.5 MySql 4.0.25
I am recording an "event" in a mysql database using the type 'datetime'. The date I store is GMT. When I browse, one "event" happened on 2006-03-24 5:14:20 for example.
I, and the server, are based in the UK. At the moment we are currently in BST, or daylight savings time meaning my local time is actually an hour ahead. So when I look up that table, I would really like it to say "2006-03-24 6:14:20".
Alternatively, when someone in Vancouver looks at it, I want it to say "2006-03-23 22:14:20".
I would also like to allow for the daylight saving historically.
"2005-12-20 14:12:12" occured before the clocks went forward, but I am in Daylight saving time when I look it up. In this case I should display the time as it is, without an hour added.
To summarise, I want to display the time that the viewer would have seen, if they looked at their clock as the "event" happened.
I have been totally bamboozled by all the time/date functions, which is why I am asking for help. I cannot decide which is the best way to store the information, or which commands are best to display the date. Indded, do the existing commands handle this international requirement, or would I have to do my own functions!
I am recording an "event" in a mysql database using the type 'datetime'. The date I store is GMT. When I browse, one "event" happened on 2006-03-24 5:14:20 for example.
I, and the server, are based in the UK. At the moment we are currently in BST, or daylight savings time meaning my local time is actually an hour ahead. So when I look up that table, I would really like it to say "2006-03-24 6:14:20".
Alternatively, when someone in Vancouver looks at it, I want it to say "2006-03-23 22:14:20".
I would also like to allow for the daylight saving historically.
"2005-12-20 14:12:12" occured before the clocks went forward, but I am in Daylight saving time when I look it up. In this case I should display the time as it is, without an hour added.
To summarise, I want to display the time that the viewer would have seen, if they looked at their clock as the "event" happened.
I have been totally bamboozled by all the time/date functions, which is why I am asking for help. I cannot decide which is the best way to store the information, or which commands are best to display the date. Indded, do the existing commands handle this international requirement, or would I have to do my own functions!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so far.
I have discovered that php does not know the timezone of the viewer (how could it!) so the set your own timezone will have to be a user option.
The formula you quoted, has now got in clear in my mind how to convert the time. However, I will leave the question open for a while to see if anyone has any comments about daylight savings.
Recap: Is there an easy way to know if a date was covered by daylight savings?
I have discovered that php does not know the timezone of the viewer (how could it!) so the set your own timezone will have to be a user option.
The formula you quoted, has now got in clear in my mind how to convert the time. However, I will leave the question open for a while to see if anyone has any comments about daylight savings.
Recap: Is there an easy way to know if a date was covered by daylight savings?
I suspect the new functions include this information in some way.
You could run a quick-ish check.
Loop through the new timezones (use a big array)
Loop through an entire year in hours looking for the a gap/repeat/dst change.
I don't know if the timezone includes the DST name.
e.g.
GMT (Greenwich Mean Time) and BST (British Summer Time) are the same timezone (GMT), but with a DST offset.
This whole thing is a nightmare. My previous employment had software which records T&A clockings. There were several clients in multiple countries/different timezones. The employees DID manage to work out that if they clocked at the right time, they could get an hours extra pay. It took a while to fix. I wasn't involved with that project, but a REAL UTC would be great. Maybe Sun time. Simply the time from a fixed source. Anything other than local time.
Hmm..
Not a lot of help really.
You could run a quick-ish check.
Loop through the new timezones (use a big array)
Loop through an entire year in hours looking for the a gap/repeat/dst change.
I don't know if the timezone includes the DST name.
e.g.
GMT (Greenwich Mean Time) and BST (British Summer Time) are the same timezone (GMT), but with a DST offset.
This whole thing is a nightmare. My previous employment had software which records T&A clockings. There were several clients in multiple countries/different timezones. The employees DID manage to work out that if they clocked at the right time, they could get an hours extra pay. It took a while to fix. I wasn't involved with that project, but a REAL UTC would be great. Maybe Sun time. Simply the time from a fixed source. Anything other than local time.
Hmm..
Not a lot of help really.
The plus and minus signs (+/-) are not intuitive. For example,
"Etc/GMT-10" actually refers to the timezone "(GMT+10:00) Canberra,Sydney,Melbourne"
"Etc/GMT+10" actually refers to the timezone GMT-10:00.
These are what I have tested. I believe other + and - are also logically reversed.