使用PHP自动将UTC转换为本地时间

Question

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

Answer 1

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.