[FIXED] MySQL: using timestamp, how to convert to my user's timezone?

Issue

In my LAMP application, I'm storing certain date/time stamps using the timestamp data type in MySQL. From my understanding, this will automatically convert to UTC when saving and converted back from UTC when retrieving. The conversion back from UTC depends on the time zone settings of the server where MySQL is installed, right?

What if I want the timestamp converted into whatever time zone my user is in? How do I query SQL to return those timestamps into a specified time zone? My server's time zone is useless to my users, unless they so happen to be in the same timezone I guess.

Solution

Similar to this question ( Detect user timezone and display UTC time with that timezone ) , you can do it as follow :

1. Detect user timezone offset
2. Calculate the difference between MySQL / Web Server's timezone
3. Apply your logic

UPDATE: another related question here : Determine a User's Timezone

Answered By - Raptor