MySql Timestamp to use my timezone, not the server's

[SamA74]

I’m working on a site which is UK based, but it’s on shared hosting on a server in the USA.
I’m using INSERT queries to record data to a MySql database and to make recording the time simple I have a Timestamp with a default value of CURRENT_TIMESTAMP, that way I don’t even need think about recording the time of insertion, it just happens. But I just twigged that on the live site it records in the server’s timezone (PDT -7 I think) not my local Europe/London timezone which I have PHP set to.
I know I could get php to supply a timestamp and insert it with the query, but I like the effortless method of the timestamp. I’m just not sure how to set the timezone for the database. I did find this article by @ceeb but I couldn’t get it to work, it always records in the local timezone of the server. Any ideas?

[Mittineague]

Yes, working with time zones is about the same fun as dealing with text translations

I’m assuming you don’t have access to database configuration. This leaves either specifying which time zone to use for the INSERT or changing the returned result.

My server is 4 time zones away, and at first doing a simple “minus four hours” with PHP was usually good enough. One problem with this however, is dealing with “savings time” differences. Since then I have leaned more to using timezone names.

I have not done so, but AFAIK, MySQL has both “server” timezone (as set in the config) and “session” timezone (which falls back to using the server timezone as default if not specified).

So I think as long as the databases timezone table is good you could do a SET to specify a named timezone of your choice at the time you make the connection.

[SamA74]

Nothing I try seems to work. Using a named timezone gives me an error. Setting it as an offset, even hard coded rather than calculated, gives no error but has no effect.
For now I have resorted to:-

$now = date('Y-m-d H:i:s');

…and adding it to the data array.

[Lemon_Juice]

Maybe you should contact your host and ask why setting time zone doesn’t work? You can set the session time zone without administrative privileges, my shared hosts allow this without any problems:

SET time_zone='+04:00';

You could run this after connection and then this time zone will be respected. When you use a TIMESTAMP column then it doesn’t matter which time zone is selected when you insert a new record because TIMESTAMP holds UTC timestamps without time zone information. The time_zone setting will affect how the time will be fetched by SELECT statements.

@SamA74

$now = date('Y-m-d H:i:s');

I use this most often but for other reasons - when the database server is on a different machine then the times may not be perfectly synchronized so I prefer to have one source of time rather than two.

[SamA74]

@Lemon_Juice

SET time_zone=‘+04:00’;

Am I using this correctly? I tried it as per the article straight after the connection.

$db = new PDO($dsn, $db_username, $db_pass, $options);
$db->exec("SET time_zone='+04:00';");

@Lemon_Juice

Maybe you should contact your host and ask why setting time zone doesn’t work?

Trying again, that may be the next step, because it appears to be working in my local environment, but not on the host server.

[SamA74]

@SamA74

it appears to be working in my local environment, but not on the host server.

With further tests, that is not the case.
The offset does work both locally and on the host if I insert the date with the query. But relying on the Timestamp it has no effect.
The thing is, if I insert using the query, I don’t need to offset, because I set PHP to the correct timezone.

[Lemon_Juice]

@SamA74

The offset does work both locally and on the host if I insert the date with the query. But relying on the Timestamp it has no effect.

Perhaps you have different expectations from what MySQL actually does. If you have a TIMESTAMP column with default CURRENT_TIMESTAMP then the time zone has no effect on default time inserts and it is by design. TIMESTAMP holds the time in seconds since the beginning of the Unix epoch and has no time zone information. The time zone setting only has an effect on your SELECT queries as the unix time from the TIMESTAMP column is then converted to the current time zone.

A different case is with DATETIME columns, which can also be set as default CURRENT_TIMESTAMP since MySQL 5.6 - it just stores the datetime as is at the moment of insertion (at the current time zone) and reads it as it was stored - so on insertion the time according to the current time zone will be inserted and the exact same time will always be retrieved with SELECT regardless of the time zone. So in a way the situation is reversed for DATETIME - the time zone has an effect on inserts but has no effect on selects.

[SamA74]

@Lemon_Juice

TIMESTAMP holds the time in seconds since the beginning of the Unix epoch and has no time zone information.

@Lemon_Juice

with DATETIME columns, which can also be set as default CURRENT_TIMESTAMP

It could be I was confusing my data types. Perhaps a datetime is what I meant to use.
But if that is the case with timestamp I would expect to see the data in seconds, like 1503943220 instead of the Y-m-d H:i:s format.

[SamA74]

@SamA74

Perhaps a datetime is what I meant to use.

Update
Seems so.
I added another column as datetime with a current timestamp default and it appears to be working as I expected.
It’s even working where to offset value comes from a function based on the code in Craig’s article (if I hard code it, it won’t account for daylight saving, being relative to UTC).
Thanks for the insight.

Though I’m still curious about the timestamp and how it’s stored and why it displays as Y-m-d H:i:s.

@Lemon_Juice

The time zone setting only has an effect on your SELECT queries as the unix time from the TIMESTAMP column is then converted to the current time zone.

Is it just that PHPmyAdmin displays it as that because it’s the result of a select? I would expect to see the data as is, an integer of seconds.

[Lemon_Juice]

@SamA74

But if that is the case with timestamp I would expect to see the data in seconds, like 1503943220 instead of the Y-m-d H:i:s format.

@SamA74

Is it just that PHPmyAdmin displays it as that because it’s the result of a select? I would expect to see the data as is, an integer of seconds.

No, the integer timestamp is stored internally by MySQL and it is converted to a human readable format (plus it is converted to current time zone) when you do SELECT. This is done by MySQL. And indeed, it looks identical to DATETIME but its internal representation is different.

[Lemon_Juice]

@SamA74

Perhaps a datetime is what I meant to use.

If your goal is to store the exact time when a record was inserted and you want the consecutive records to have the time in a sequential order then you should use TIMESTAMP data type or DATETIME filled with UTC time (I don’t know if it’s possible to make default CURRENT_TIMESTAMP insert UTC time into DATETIME columns, probably not unless you set the server time zone to UTC). Otherwise, when there is a change to daylight saving time and vice-versa you may have unexpected gaps and shifts in the recorded time or you may have duplicated periods within the same hour when the clock is switched back.

This is important if you intend to do ORDER BY the time column or compare times between the records. When I want to store the time of record insertion just for informational purposes and I make a system that is intended to be used during the day and in one time zone only then for simplicity I store the time into a DATETIME column. But in other cases this might not be a good solution.