MySQL and PHP UTC time differs when in number format

Question

I'm trying to make a login system, which in order to reset password or confirm the account I have a column with an 'expiration date', and for that expiration to work everywhere, I'm storing in UTC times. But I've come across something very weird. When I get the formatted UTC time from SQL and the formatted UTC time from PHP, both are the same. But when I get in numbers (I don't know what the real name of that format is) they differ exactly 7200 (which is the same as 2 hours difference). If anyone has any idea why is this happening? Thanks.

Here's the code I used to debug this:

<?php

$con = mysqli_connect('localhost', 'root', '', 'clients');

updateUserUTCTime($con, 3);
$user = getUserAssocArray($con, 3);

echo $user['date_utc'];
echo '<br>';
echo gmdate("Y-m-d H:i:s", time());
echo '<br>';
echo '<br>';
echo strtotime($user['date_utc']);
echo '<br>';
echo gmdate(time());
echo '<br>';
echo '____________';
echo '<br>';
echo strtotime($user['date_utc']) - gmdate(time());

function updateUserUTCTime($con, $id=1){
    $query = '
    update datetests 
    set date_utc = UTC_TIME()
    where id = '.$id.'
    ;';

    return mysqli_query($con, $query);
}

function getUserAssocArray($con, $id=1){
    $query = 'select * from datetests where id = '.$id.';';
    $result = mysqli_query($con, $query);
    $result = mysqli_fetch_assoc($result);
    return $result;
}

The output (at the time I've run the code) is:

2021-09-23 09:54:06
2021-09-23 09:54:06

1632383646
1632390846
____________
-7200

Note that when formatted they're the same, but when not, they differ 7200 units...

Ps: In the database, I'm using "DATETIME" type to store the UTC_TIME. I'm using the following functions to get UTC time in each language:

gmdate(time()) -> PHP

UTC_TIME() -> MySQL

The datatype of your `date_utc` column in your table matters. What is it? Please [edit] your question. When MySQL reads `TIMESTAMP` data with `SELECT`, it automatically translates it from UTC to your local time zone setting. You can find that with `SELECT @@time_zone`. It does not do this translation with `DATETIME` data types. — O. Jones, Sep 23 '21 at 10:18
I imagine PHP and MySQL don't have the same timezone set in the config of each one. Have a look at https://stackoverflow.com/questions/34428563/set-timezone-in-php-and-mysql — Patrick Janser, Sep 23 '21 at 10:21
You're using the wrong functions or using them incorrectly (i) `UTC_TIME()` gives you time, not full datetime (ii) `strtotime` will assume local timezone if no explicit timezone is specified. Finally, your output does not seem to match the code. — Salman A, Sep 23 '21 at 10:50

O. Jones · Accepted Answer · 2021-09-23T11:06:16.857

Timezone processing can be a pain in the xss neck.

1632383646 is 07:54:06 UTC, 09:54:06 CET
1632390846 is 09:54:06 UTC, 11:54:06 CET

php's time() always returns the UNIX timestamp number (like 1632390846) in UTC. UNIX timestamps are always in UTC. (As long as your machine has its local time zone configured correctly.)

But, php's strtotime() function takes a date/time string 2021-09-23 09:54:06 as if it were in local time and converts it to a UNIX timestamp. Your machine is set to CET (+02:00), so strtime() subtracted two hours to get the timestamp, which is assumed always to be in UTC.

Application design wise, you may be wise to use MySQL to do all your timezone handling. It's set up remarkably well for that task.

Use TIMESTAMP data types throughout. That way all your stored date/time values will be in UTC.
If you're going global, ask each user to choose her time zone preference, with a string like Europe/Amsterdam or Asia/Kolkata. See this.
When you run queries to your database on behalf of users, first give this MySQL statement
```
SET time_zone = ###user-preference-string###
```
and all your query results will be in local time.
When you run database maintenance queries, first say
```
SET time_zone = 'utc'
```
and your output will be in UTC.

This answer was very helpful. Indeed is a pain in*** to work with timezones, but to wrap it up, i used the handling time entirely in the MySQL as you suggested. Otherwise it will conflict too much between the default timezone of the php and the MySQL. Thanks! (I didn't used the TIMESTAMP type due to it's limit beeing "soon" in 2038). — Soulss, Sep 23 '21 at 21:09
Yeah, they need to fix that Y2039 problem. And they need to do it soon. Kids born today will be only 17 when it dies. — O. Jones, Sep 24 '21 at 09:38

MySQL and PHP UTC time differs when in number format

1 Answers1