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
date_utccolumn in your table matters. What is it? Please edit your question. When MySQL readsTIMESTAMPdata withSELECT, it automatically translates it from UTC to your local time zone setting. You can find that withSELECT @@time_zone. It does not do this translation withDATETIMEdata types.UTC_TIME()gives you time, not full datetime (ii)strtotimewill assume local timezone if no explicit timezone is specified. Finally, your output does not seem to match the code.