How to retrive the MySQL server timezone and then do a convertion in php

Question

i have some timedate information of my users in my database. Also i have the timezone of all my users.

Any time a user logs in to my web, a mensage popup showing the last time he logs in. This datetime is stored on mysql database using the datetime format and the SERVER TIMEZONE (no the user datetime).

All this simple by:

mysql_query("UPDATE ".$this->tablename." SET last_login = NOW() 
WHERE email = '".$_SESSION['email_of_user']."';");

What i want is to convert this stored datetime to my user timezone (stored in my database).

Possible duplicate of this: http://stackoverflow.com/questions/13/how-can-i-determine-a-web-users-time-zone — Cheesebaron, Sep 07 '11 at 15:30

score 2 · Accepted Answer · answered Sep 07 '11 at 15:38

mysql function convert_tz

syntax

CONVERT_TZ(dt,from_tz,to_tz)

example

SELECT CONVERT_TZ('2004-01-01 12:00:00','SYSTEM','@user_timezone');

You have the user timezone stored into database,
then you can do a simple join when you want to display last time an user logs in

score 1 · Answer 2 · answered Sep 07 '11 at 15:33

1

If I understand what you're looking to do, why not store everything as UTC, and then apply the timezone after the fact?

answered Sep 07 '11 at 15:33

Jeremy Holovacs

22,480
33
117
254

How to retrive the MySQL server timezone and then do a convertion in php

2 Answers2