CODEWITHSUNDEEP
phpmysqldatetime
user2398514
user2398514

Reputation: 167

converting UTC to local time automatically using PHP

Im using MySQL with PHP, and the project is a kind of notification manager

if some one does some thing, a new notification need to be posted in the common post-ground

the issue is - we have users from multiple timezones, currently Im using "datetime" as the field type, so what ever I enter converts into UTC

for example: Im from India(+5:30) and if I create a notification at 21/3/2015 22:50:00, it is saving in DB as 21/3/2015 17:20:00

storing works perfectly well from all zones, but while retrieving it shows for me as 21/3/2015 17:20:00(UTC) but Im expecting in my timezone(IST +5:30) so what Im expecting is 21/3/2015 22:50:00.

is there a way to mention in the query itself to get the time converted in the fetching itself?

or any other way to convert and process further?

any help on this is greatly appreciated.

Thank you

Upvotes: 0

Views: 1343

Answers (2)

Jacinto
Jacinto

Reputation: 180

You can get your client TimeZone through JSTZ

In your php file, you can simply convert the date after you received the from mysql:

 $date = new DateTime($date . " " . "UTC");
 $date->setTimezone(new DateTimeZone($timeZoneTo));//$timeZoneTo you get it from JSTZ

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can use the function convert_tz so while selecting you can use the following 

mysql> select convert_tz('2015-03-21 17:20:00','+00:00','+05:30') as ist_time;
+---------------------+
| ist_time            |
+---------------------+
| 2015-03-21 22:50:00 |
+---------------------+
1 row in set (0.00 sec)

All you need to do is to change the hard coded date '2015-03-21 17:20:00' to your column name in the selection.

Upvotes: 1

Related Questions