1

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

3
  • 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. Commented Sep 23, 2021 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 stackoverflow.com/questions/34428563/… Commented Sep 23, 2021 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. Commented Sep 23, 2021 at 10:50

1 Answer 1

0

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.

  1. Use TIMESTAMP data types throughout. That way all your stored date/time values will be in UTC.

  2. 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.

  3. 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.

  4. When you run database maintenance queries, first say

    SET time_zone = 'utc'
    

    and your output will be in UTC.

Sign up to request clarification or add additional context in comments.

2 Comments

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).
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.