0

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.

StackOverflowNewbie
  • 39,403
  • 111
  • 277
  • 441

2 Answers2

1

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

Community
  • 1
  • 1
Raptor
  • 53,206
  • 45
  • 230
  • 366
0

I think you want the CONVERT_TZ function .

georgepsarakis
  • 1,927
  • 3
  • 20
  • 24
  • It works perfectly with any datetime representation, I checked it. In order to use named time zones though, you must set the time zone tables first http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html . – georgepsarakis May 13 '12 at 11:28