When i use the date() php function on a MySQL insert it stores the date as GMT (6 hours ahead of me). And when i return the data to display on a page it stays the same. IE: register a new user at 1pm but stores it as 7pm on the database, then when i display it on the page it says registered at 7pm. How do i go about converting the time when i retrieve it depending on the time zone of the person viewing?
You’re going to love me…
I had this issue before, the only way for me to get around it was to store the timezone that the person was in and do the conversion when reading from the database for each user.
So I downloaded a Time Zone Database with all the offsets.
Which you see here… You can just run this in your database to create the table and its offsets.
[php]
CREATE TABLE lu_time_zones (
id int(11) NOT NULL AUTO_INCREMENT,
offset decimal(3,1) NOT NULL,
name varchar(45) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name_UNIQUE (name)
) ENGINE=InnoDB AUTO_INCREMENT=427 DEFAULT CHARSET=utf8;
–
– Dumping data for table lu_time_zones
LOCK TABLES lu_time_zones WRITE;
/*!40000 ALTER TABLE lu_time_zones DISABLE KEYS */;
INSERT INTO lu_time_zones VALUES (285,-10.0,’(GMT-10:00) Hawaii’),(286,-9.0,’(GMT-09:00) Alaska’),(287,-8.0,’(GMT-08:00) Pacific Time (US & Canada)’),(288,-7.0,’(GMT-07:00) Arizona’),(289,-7.0,’(GMT-07:00) Mountain Time (US & Canada)’),(290,-6.0,’(GMT-06:00) Central Time (US & Canada)’),(291,-5.0,’(GMT-05:00) Eastern Time (US & Canada)’),(292,-5.0,’(GMT-05:00) Indiana (East)’),(293,-12.0,’(GMT-12:00) International Date Line West’),(294,-11.0,’(GMT-11:00) Midway Island’),(295,-11.0,’(GMT-11:00) Samoa’),(296,-8.0,’(GMT-08:00) Tijuana’),(297,-7.0,’(GMT-07:00) Chihuahua’),(298,-7.0,’(GMT-07:00) La Paz’),(299,-7.0,’(GMT-07:00) Mazatlan’),(300,-6.0,’(GMT-06:00) Central America’),(301,-6.0,’(GMT-06:00) Guadalajara’),(302,-6.0,’(GMT-06:00) Mexico City’),(303,-6.0,’(GMT-06:00) Monterrey’),(304,-6.0,’(GMT-06:00) Saskatchewan’),(305,-5.0,’(GMT-05:00) Bogota’),(306,-5.0,’(GMT-05:00) Lima’),(307,-5.0,’(GMT-05:00) Quito’),(308,-4.0,’(GMT-04:00) Atlantic Time (Canada)’),(309,-4.0,’(GMT-04:00) Caracas’),(310,-4.0,’(GMT-04:00) La Paz’),(311,-4.0,’(GMT-04:00) Santiago’),(312,-3.5,’(GMT-03:30) Newfoundland’),(313,-3.0,’(GMT-03:00) Brasilia’),(314,-3.0,’(GMT-03:00) Buenos Aires’),(315,-3.0,’(GMT-03:00) Georgetown’),(316,-3.0,’(GMT-03:00) Greenland’),(317,-2.0,’(GMT-02:00) Mid-Atlantic’),(318,-1.0,’(GMT-01:00) Azores’),(319,-1.0,’(GMT-01:00) Cape Verde Is.’),(320,0.0,’(GMT) Casablanca’),(321,0.0,’(GMT) Dublin’),(322,0.0,’(GMT) Edinburgh’),(323,0.0,’(GMT) Lisbon’),(324,0.0,’(GMT) London’),(325,0.0,’(GMT) Monrovia’),(326,1.0,’(GMT+01:00) Amsterdam’),(327,1.0,’(GMT+01:00) Belgrade’),(328,1.0,’(GMT+01:00) Berlin’),(329,1.0,’(GMT+01:00) Bern’),(330,1.0,’(GMT+01:00) Bratislava’),(331,1.0,’(GMT+01:00) Brussels’),(332,1.0,’(GMT+01:00) Budapest’),(333,1.0,’(GMT+01:00) Copenhagen’),(334,1.0,’(GMT+01:00) Ljubljana’),(335,1.0,’(GMT+01:00) Madrid’),(336,1.0,’(GMT+01:00) Paris’),(337,1.0,’(GMT+01:00) Prague’),(338,1.0,’(GMT+01:00) Rome’),(339,1.0,’(GMT+01:00) Sarajevo’),(340,1.0,’(GMT+01:00) Skopje’),(341,1.0,’(GMT+01:00) Stockholm’),(342,1.0,’(GMT+01:00) Vienna’),(343,1.0,’(GMT+01:00) Warsaw’),(344,1.0,’(GMT+01:00) West Central Africa’),(345,1.0,’(GMT+01:00) Zagreb’),(346,2.0,’(GMT+02:00) Athens’),(347,2.0,’(GMT+02:00) Bucharest’),(348,2.0,’(GMT+02:00) Cairo’),(349,2.0,’(GMT+02:00) Harare’),(350,2.0,’(GMT+02:00) Helsinki’),(351,2.0,’(GMT+02:00) Istanbul’),(352,2.0,’(GMT+02:00) Jerusalem’),(353,2.0,’(GMT+02:00) Kyev’),(354,2.0,’(GMT+02:00) Minsk’),(355,2.0,’(GMT+02:00) Pretoria’),(356,2.0,’(GMT+02:00) Riga’),(357,2.0,’(GMT+02:00) Sofia’),(358,2.0,’(GMT+02:00) Tallinn’),(359,2.0,’(GMT+02:00) Vilnius’),(360,3.0,’(GMT+03:00) Baghdad’),(361,3.0,’(GMT+03:00) Kuwait’),(362,3.0,’(GMT+03:00) Moscow’),(363,3.0,’(GMT+03:00) Nairobi’),(364,3.0,’(GMT+03:00) Riyadh’),(365,3.0,’(GMT+03:00) St. Petersburg’),(366,3.0,’(GMT+03:00) Volgograd’),(367,3.5,’(GMT+03:30) Tehran’),(368,4.0,’(GMT+04:00) Abu Dhabi’),(369,4.0,’(GMT+04:00) Baku’),(370,4.0,’(GMT+04:00) Muscat’),(371,4.0,’(GMT+04:00) Tbilisi’),(372,4.0,’(GMT+04:00) Yerevan’),(373,4.5,’(GMT+04:30) Kabul’),(374,5.0,’(GMT+05:00) Ekaterinburg’),(375,5.0,’(GMT+05:00) Islamabad’),(376,5.0,’(GMT+05:00) Karachi’),(377,5.0,’(GMT+05:00) Tashkent’),(378,5.5,’(GMT+05:30) Chennai’),(379,5.5,’(GMT+05:30) Kolkata’),(380,5.5,’(GMT+05:30) Mumbai’),(381,5.5,’(GMT+05:30) New Delhi’),(382,5.8,’(GMT+05:45) Kathmandu’),(383,6.0,’(GMT+06:00) Almaty’),(384,6.0,’(GMT+06:00) Astana’),(385,6.0,’(GMT+06:00) Dhaka’),(386,6.0,’(GMT+06:00) Novosibirsk’),(387,6.0,’(GMT+06:00) Sri Jayawardenepura’),(388,6.5,’(GMT+06:30) Rangoon’),(389,7.0,’(GMT+07:00) Bangkok’),(390,7.0,’(GMT+07:00) Hanoi’),(391,7.0,’(GMT+07:00) Jakarta’),(392,7.0,’(GMT+07:00) Krasnoyarsk’),(393,8.0,’(GMT+08:00) Beijing’),(394,8.0,’(GMT+08:00) Chongqing’),(395,8.0,’(GMT+08:00) Hong Kong’),(396,8.0,’(GMT+08:00) Irkutsk’),(397,8.0,’(GMT+08:00) Kuala Lumpur’),(398,8.0,’(GMT+08:00) Perth’),(399,8.0,’(GMT+08:00) Singapore’),(400,8.0,’(GMT+08:00) Taipei’),(401,8.0,’(GMT+08:00) Ulaan Bataar’),(402,8.0,’(GMT+08:00) Urumqi’),(403,9.0,’(GMT+09:00) Osaka’),(404,9.0,’(GMT+09:00) Sapporo’),(405,9.0,’(GMT+09:00) Seoul’),(406,9.0,’(GMT+09:00) Tokyo’),(407,9.0,’(GMT+09:00) Yakutsk’),(408,9.5,’(GMT+09:30) Adelaide’),(409,9.5,’(GMT+09:30) Darwin’),(410,10.0,’(GMT+10:00) Brisbane’),(411,10.0,’(GMT+10:00) Canberra’),(412,10.0,’(GMT+10:00) Guam’),(413,10.0,’(GMT+10:00) Hobart’),(414,10.0,’(GMT+10:00) Melbourne’),(415,10.0,’(GMT+10:00) Port Moresby’),(416,10.0,’(GMT+10:00) Sydney’),(417,10.0,’(GMT+10:00) Vladivostok’),(418,11.0,’(GMT+11:00) Magadan’),(419,11.0,’(GMT+11:00) New Caledonia’),(420,11.0,’(GMT+11:00) Solomon Is.’),(421,12.0,’(GMT+12:00) Auckland’),(422,12.0,’(GMT+12:00) Fiji’),(423,12.0,’(GMT+12:00) Kamchatka’),(424,12.0,’(GMT+12:00) Marshall Is.’),(425,12.0,’(GMT+12:00) Wellington’),(426,13.0,’(GMT+13:00) Nukualofa’);
UNLOCK TABLES;
[/php]
This is one of the queries I used at Unlock The Inbox to convert the time zone, you’ll have to modify it for your program. But you can see where I do the date selection.
[php]SELECT cs.url_or_ip, cs.active, ms.schedule_name, date_format(bl.last_scanned + INTERVAL tz.offset + if(((select 1 from lu_dst where now() between dst_start_date and dst_end_date) * c.C_AdjustForDST) is NULL,0, 1) HOUR, ‘%m/%d/%Y %l:%i:%s %p’) last_scanned,bl.id FROM blacklist_monitoring bl, customers_sites cs, monitoring_schedule ms, customers c, lu_time_zones tz
where bl.monitoring_schedule_id = ms.id and cs.site_id = bl.site_id and cs.c_id = ?MemberID and c.c_id = cs.c_id and tz.id = c.c_timezoneid order by cs.active, cs.url_or_ip[/php]