How to correctly set mysql timezone

Question

I have a weird problem concerning mysql timezone.

In my website config file i have this line which sets the timezone :

mysql_query("SET SESSION time_zone = '$offset';"); // Offset is properly calculated, no worries about that

The funny part is that if i add another line right after this like this :

$q = mysql_query("SELECT NOW() as now");
$row = mysql_fetch_array($row);
echo $row["now"];

After executing that code, the time is displayed correctly.

BUT, in some other queries i insert rows in tables that have a column named date that defaults to CURRENT_TIMESTAMP.

Rows are inserted like this:

INSERT INTO `sessions` (`user_id`) VALUES `1`

(The sessions table has a date column that defaults to CURRENT_TIMESTAMP)

But the value inserted in DB still points back to the timezone of the server :((

Any ideas how to work through this ?

When you say the values inserted in DB point back to the server timezone, how do you check? Also though a connection where you did the `SET SESSION time_zone` trick? — Romain, Dec 08 '11 at 16:17
are you closing the connection before running the `INSERT` ? — Manse, Dec 08 '11 at 16:18
I am checking it on phpMyAdmin, and nope connection is not closed until end of script execution — Dany Khalife, Dec 08 '11 at 16:18
any clue if phpMyAdmin might be the one converting back when displaying it ? — Dany Khalife, Dec 08 '11 at 16:24

Jpsy · Accepted Answer · 2019-10-28T09:33:52.030

You have to understand that MySQL maintains multiple time zone settings:

System time zone (basically the time zone set in OS)
Server time zone (the time zone used by MySQL)
Client time zone (the session time zone used per connection)

See http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html for details.

Date/time values are stored in two different ways:

All unix timestamp based values are always stored in UTC. They are internally converted from and to Client time zone on the fly when they are stored and read. The same is true for NOW() and CURTIME() functions as they are timestamp based.
DATE, TIME and DATETIME columns (which store their values in a year-month-day hour-minute-second format) are NOT affected by time zone settings and are never converted.

From the above it should become clear that the values that you see when you read from unix timestamp based columns are not necessarily what is really stored in the DB. They are converted using the server time zone and the client time zone. The result can be confusing if you do not understand the details of the mechanics.

For a first test try to find out the current settings in each of your client programs by executing

SELECT @@global.time_zone, @@session.time_zone;

The global time zone will always be the same. But the session time zone can differ from client application to client application and will change the results of your read and write operations.

Thanks, very enlightning indeed! I realized that phpMyAdmin was displaying the times converted to server timezone, but if i read them from DB in my script after setting the session timezone they appear correcly so it was just phpMyAdmin that was confusing me :) — Dany Khalife, Dec 08 '11 at 22:36
@DanyKhalife Thanks for commenting on this, thought I was going insane. — Mark, Apr 14 '15 at 15:53
@Jpsy Greate answer..i have query how do you set Client Time zone in mysql confiq file ??? — goodyzain, Mar 02 '16 at 05:00
@goodyzain The client time zone cannot be set in the config. It is determined on a per-session-basis. See [the MySQL docs](https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html): `Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement: mysql> SET time_zone = timezone;` — Jpsy, Mar 02 '16 at 10:51

How to correctly set mysql timezone

1 Answers1

Linked