MySQL and PHP UTC time differs when in number format-CodePudding

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

CodePudding user response：

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.