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!




LVL 1
MortimerCatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard QuadlingSenior Software DeveloperCommented:
Universal time! Ha! You need to store your time with the timezone (which is probably happening already).

Then you need to know the timezone for the client (you have to ask them and remember this - maybe a login is used? in which case their user profile needs to allow them to change/set their timezone or GMT offset.

I'm not sure how you get their time though.

Looking through the phpBB code though, they use ...

gmdate($format, $gmepoch + (3600 * $tz));

Basically you hold the timezone supplied by the client as hours. You then multiply that by 3600 to get seconds and add that to the GMT time you have on file. And then you create the client time from that.

I don't think DST is handled through all of this though.

There are some timezone functions available since PHP5.1.0RC1, so you would need to upgrade to use them. I've not used them as all my work shows the time as GMT!

I suspect that the timezone functions have the DST data in there and you would need to swap to the clients time zone and process times for each request.


From the manual ...

date_default_timezone_set() sets the default timezone used by all date/time functions.

Note:
Since PHP 5.1.0 (when the date/time functions were rewritten), every call to a date/time function will generate a E_NOTICE if the timezone isn't valid, and/or a E_STRICT message if using the system settings or the TZ environment variable.

Parameters
timezone_identifier
The timezone identifier, like UTC or Europe/Lisbon. The list of valid identifiers is available in the Appendix H, List of Supported Timezones.

Return Values
This function returns FALSE if the timezone_identifier isn't valid, or TRUE otherwise.

ChangeLog
Version Description
5.1.2 The function started to validate the timezone_identifier parameter.  

See Also date_default_timezone_get()


The appendix H. List of Supported Timezones is significant.

Africa/Abidjan Africa/Accra Africa/Addis_Ababa Africa/Algiers Africa/Asmera
Africa/Bamako Africa/Bangui Africa/Banjul Africa/Bissau Africa/Blantyre
Africa/Brazzaville Africa/Bujumbura Africa/Cairo Africa/Casablanca Africa/Ceuta
Africa/Conakry Africa/Dakar Africa/Dar_es_Salaam Africa/Djibouti Africa/Douala
Africa/El_Aaiun Africa/Freetown Africa/Gaborone Africa/Harare Africa/Johannesburg
Africa/Kampala Africa/Khartoum Africa/Kigali Africa/Kinshasa Africa/Lagos
Africa/Libreville Africa/Lome Africa/Luanda Africa/Lubumbashi Africa/Lusaka
Africa/Malabo Africa/Maputo Africa/Maseru Africa/Mbabane Africa/Mogadishu
Africa/Monrovia Africa/Nairobi Africa/Ndjamena Africa/Niamey Africa/Nouakchott
Africa/Ouagadougou Africa/Porto-Novo Africa/Sao_Tome Africa/Timbuktu Africa/Tripoli
Africa/Tunis Africa/Windhoek      

America/Adak America/Anchorage America/Anguilla America/Antigua America/Araguaina
America/Argentina/Buenos_Aires America/Argentina/Catamarca America/Argentina/ComodRivadavia America/Argentina/Cordoba America/Argentina/Jujuy
America/Argentina/La_Rioja America/Argentina/Mendoza America/Argentina/Rio_Gallegos America/Argentina/San_Juan America/Argentina/Tucuman
America/Argentina/Ushuaia America/Aruba America/Asuncion America/Atka America/Bahia
America/Barbados America/Belem America/Belize America/Boa_Vista America/Bogota
America/Boise America/Buenos_Aires America/Cambridge_Bay America/Campo_Grande America/Cancun
America/Caracas America/Catamarca America/Cayenne America/Cayman America/Chicago
America/Chihuahua America/Coral_Harbour America/Cordoba America/Costa_Rica America/Cuiaba
America/Curacao America/Danmarkshavn America/Dawson America/Dawson_Creek America/Denver
America/Detroit America/Dominica America/Edmonton America/Eirunepe America/El_Salvador
America/Ensenada America/Fort_Wayne America/Fortaleza America/Glace_Bay America/Godthab
America/Goose_Bay America/Grand_Turk America/Grenada America/Guadeloupe America/Guatemala
America/Guayaquil America/Guyana America/Halifax America/Havana America/Hermosillo
America/Indiana/Indianapolis America/Indiana/Knox America/Indiana/Marengo America/Indiana/Vevay America/Indianapolis
America/Inuvik America/Iqaluit America/Jamaica America/Jujuy America/Juneau
America/Kentucky/Louisville America/Kentucky/Monticello America/Knox_IN America/La_Paz America/Lima
America/Los_Angeles America/Louisville America/Maceio America/Managua America/Manaus
America/Martinique America/Mazatlan America/Mendoza America/Menominee America/Merida
America/Mexico_City America/Miquelon America/Monterrey America/Montevideo America/Montreal
America/Montserrat America/Nassau America/New_York America/Nipigon America/Nome
America/Noronha America/North_Dakota/Center America/Panama America/Pangnirtung America/Paramaribo
America/Phoenix America/Port-au-Prince America/Port_of_Spain America/Porto_Acre America/Porto_Velho
America/Puerto_Rico America/Rainy_River America/Rankin_Inlet America/Recife America/Regina
America/Rio_Branco America/Rosario America/Santiago America/Santo_Domingo America/Sao_Paulo
America/Scoresbysund America/Shiprock America/St_Johns America/St_Kitts America/St_Lucia
America/St_Thomas America/St_Vincent America/Swift_Current America/Tegucigalpa America/Thule
America/Thunder_Bay America/Tijuana America/Toronto America/Tortola America/Vancouver
America/Virgin America/Whitehorse America/Winnipeg America/Yakutat America/Yellowknife
Brazil/Acre Brazil/DeNoronha Brazil/East Brazil/West Canada/Atlantic
Canada/Central Canada/East-Saskatchewan Canada/Eastern Canada/Mountain Canada/Newfoundland
Canada/Pacific Canada/Saskatchewan Canada/Yukon Chile/Continental Chile/EasterIsland
Mexico/BajaNorte Mexico/BajaSur Mexico/General US/Alaska US/Aleutian
US/Arizona US/Central US/East-Indiana US/Eastern US/Hawaii
US/Indiana-Starke US/Michigan US/Mountain US/Pacific US/Pacific-New
US/Samoa        

Antarctica/Casey Antarctica/Davis Antarctica/DumontDUrville Antarctica/Mawson Antarctica/McMurdo
Antarctica/Palmer Antarctica/Rothera Antarctica/South_Pole Antarctica/Syowa Antarctica/Vostok

Arctic/Longyearbyen        

Asia/Aden Asia/Almaty Asia/Amman Asia/Anadyr Asia/Aqtau
Asia/Aqtobe Asia/Ashgabat Asia/Ashkhabad Asia/Baghdad Asia/Bahrain
Asia/Baku Asia/Bangkok Asia/Beirut Asia/Bishkek Asia/Brunei
Asia/Calcutta Asia/Choibalsan Asia/Chongqing Asia/Chungking Asia/Colombo
Asia/Dacca Asia/Damascus Asia/Dhaka Asia/Dili Asia/Dubai
Asia/Dushanbe Asia/Gaza Asia/Harbin Asia/Hong_Kong Asia/Hovd
Asia/Irkutsk Asia/Istanbul Asia/Jakarta Asia/Jayapura Asia/Jerusalem
Asia/Kabul Asia/Kamchatka Asia/Karachi Asia/Kashgar Asia/Katmandu
Asia/Krasnoyarsk Asia/Kuala_Lumpur Asia/Kuching Asia/Kuwait Asia/Macao
Asia/Macau Asia/Magadan Asia/Makassar Asia/Manila Asia/Muscat
Asia/Nicosia Asia/Novosibirsk Asia/Omsk Asia/Oral Asia/Phnom_Penh
Asia/Pontianak Asia/Pyongyang Asia/Qatar Asia/Qyzylorda Asia/Rangoon
Asia/Riyadh Asia/Saigon Asia/Sakhalin Asia/Samarkand Asia/Seoul
Asia/Shanghai Asia/Singapore Asia/Taipei Asia/Tashkent Asia/Tbilisi
Asia/Tehran Asia/Tel_Aviv Asia/Thimbu Asia/Thimphu Asia/Tokyo
Asia/Ujung_Pandang Asia/Ulaanbaatar Asia/Ulan_Bator Asia/Urumqi Asia/Vientiane
Asia/Vladivostok Asia/Yakutsk Asia/Yekaterinburg Asia/Yerevan Indian/Antananarivo
Indian/Chagos Indian/Christmas Indian/Cocos Indian/Comoro Indian/Kerguelen
Indian/Mahe Indian/Maldives Indian/Mauritius Indian/Mayotte Indian/Reunion

Atlantic/Azores Atlantic/Bermuda Atlantic/Canary Atlantic/Cape_Verde Atlantic/Faeroe
Atlantic/Jan_Mayen Atlantic/Madeira Atlantic/Reykjavik Atlantic/South_Georgia Atlantic/St_Helena
Atlantic/Stanley  

Australia/ACT Australia/Adelaide Australia/Brisbane Australia/Broken_Hill Australia/Canberra
Australia/Currie Australia/Darwin Australia/Hobart Australia/LHI Australia/Lindeman
Australia/Lord_Howe Australia/Melbourne Australia/North Australia/NSW Australia/Perth
Australia/Queensland Australia/South Australia/Sydney Australia/Tasmania Australia/Victoria
Australia/West Australia/Yancowinna      

Europe/Amsterdam Europe/Andorra Europe/Athens Europe/Belfast Europe/Belgrade
Europe/Berlin Europe/Bratislava Europe/Brussels Europe/Bucharest Europe/Budapest
Europe/Chisinau Europe/Copenhagen Europe/Dublin Europe/Gibraltar Europe/Helsinki
Europe/Istanbul Europe/Kaliningrad Europe/Kiev Europe/Lisbon Europe/Ljubljana
Europe/London Europe/Luxembourg Europe/Madrid Europe/Malta Europe/Mariehamn
Europe/Minsk Europe/Monaco Europe/Moscow Europe/Nicosia Europe/Oslo
Europe/Paris Europe/Prague Europe/Riga Europe/Rome Europe/Samara
Europe/San_Marino Europe/Sarajevo Europe/Simferopol Europe/Skopje Europe/Sofia
Europe/Stockholm Europe/Tallinn Europe/Tirane Europe/Tiraspol Europe/Uzhgorod
Europe/Vaduz Europe/Vatican Europe/Vienna Europe/Vilnius Europe/Warsaw
Europe/Zagreb Europe/Zaporozhye Europe/Zurich    

Pacific/Apia Pacific/Auckland Pacific/Chatham Pacific/Easter Pacific/Efate
Pacific/Enderbury Pacific/Fakaofo Pacific/Fiji Pacific/Funafuti Pacific/Galapagos
Pacific/Gambier Pacific/Guadalcanal Pacific/Guam Pacific/Honolulu Pacific/Johnston
Pacific/Kiritimati Pacific/Kosrae Pacific/Kwajalein Pacific/Majuro Pacific/Marquesas
Pacific/Midway Pacific/Nauru Pacific/Niue Pacific/Norfolk Pacific/Noumea
Pacific/Pago_Pago Pacific/Palau Pacific/Pitcairn Pacific/Ponape Pacific/Port_Moresby
Pacific/Rarotonga Pacific/Saipan Pacific/Samoa Pacific/Tahiti Pacific/Tarawa
Pacific/Tongatapu Pacific/Truk Pacific/Wake Pacific/Wallis Pacific/Yap

CET CST6CDT Cuba EET Egypt
Eire EST EST5EDT Etc/GMT Etc/GMT+0
Etc/GMT+1 Etc/GMT+10 Etc/GMT+11 Etc/GMT+12 Etc/GMT+2
Etc/GMT+3 Etc/GMT+4 Etc/GMT+5 Etc/GMT+6 Etc/GMT+7
Etc/GMT+8 Etc/GMT+9 Etc/GMT-0 Etc/GMT-1 Etc/GMT-10
Etc/GMT-11 Etc/GMT-12 Etc/GMT-13 Etc/GMT-14 Etc/GMT-2
Etc/GMT-3 Etc/GMT-4 Etc/GMT-5 Etc/GMT-6 Etc/GMT-7
Etc/GMT-8 Etc/GMT-9 Etc/GMT0 Etc/Greenwich Etc/UCT
Etc/Universal Etc/UTC Etc/Zulu Factory GB
GB-Eire GMT GMT+0 GMT-0 GMT0
Greenwich Hongkong HST Iceland Iran
Israel Jamaica Japan Kwajalein Libya
MET MST MST7MDT Navajo NZ
NZ-CHAT Poland Portugal PRC PST8PDT
ROC ROK Singapore Turkey UCT
Universal UTC W-SU WET Zulu

There are recommendations with the ETC/... to NOT use them as they are there for POSIX compliancy and are the wrong way round.

If you get these working, let me know and I'll create a working example for the manual.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard QuadlingSenior Software DeveloperCommented:
A user note on the timezones ...

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.
0
MortimerCatAuthor Commented:
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?
0
Richard QuadlingSenior Software DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.