I'm working on a password reset function. I insert records in a password_reset_token table and need to expire them based on their timestamp and a certain lifetime. This gets tricky because I think there are some unwanted time conversions going on.

I have some code (which I thinkworks...) which creates these records with their expiration datetime by generating a datetime string in PHP:

        $sql = "INSERT INTO  password_reset_token (
                user_id,
                token,
                expiration_datetime
        ) VALUES (
                " . $db->escape($user_id) . ",
                '" . $db->escape($password_reset_token) . "',
                '" . date("Y-m-d H:i:s", time() + 7200) . "'
        )";

And this is where it starts to get weird. I'm in California. The server is in an AWS data center in Virgina (3 hours difference). The database server (also in AWS Virginia) seems to have this habit of switching back to UTC time so I have given up on trying to change it. It is my guess that the code above gets the current PHP server time, whatever that happens to be, and the MySQL database converts the datetime string (e.g., 2021-03-24 18:13:09) into UTC time somehow.

How MySQL manipulates this value depends on the way I define my expiration_timestamp column in my database. I vaguely recall seeing columns of type timestamp undergo surprising conversion during import or export operations. I'm not sure what conversion, if any, datetime columns may undergo during import and export, but suspect that the amazon server will think that the record is referring to that datetime in UTC (i.e., Greenwich). If I store the expiration_date as varchar, it will undergo no conversions but it might be much less useful to me because it won't benefit from the various time-related functions. When I look at the records in this table via phpmyadmin, the datetimes look like the date on the server but in fact my PHP server and RDS machine very likely have wildly different ideas about what time it is referring to.

When do my garbage-collecting DELETE queries, it seems OK. If I try to SELECT and count how many records exist that haven't expired, the conversion is working in a similar way:

// this looks like 2 hours ago Virginia time because the server is in Virginia, so I must do a mental conversion
$cutoff_datetime = date("Y-m-d H:i:s", time - 3200);
// 
$sql = "SELECT COUNT(*) FROM * password_reset_token WHERE user_id = $user_id AND expiration > '$cutoff_datetime'";

Because the date is passed as a string generated by my PHP server for each query, stuff tends to work out, but trying to juggle the time conversions in my head is confusing. I've considered storing them as unix timestamp ints, but these are very inconvenient to read unless they are converted to one's local timezone which, if I'm not mistaken would require JS probably -- or changing the time on all of one's servers to the local time.

If anyone has any suggestions about how to store these expiration times (timestamp? datetime? int?) such that they are readable and consistent , I'd very much appreciate it.

EDIT:
Almost forgot to mention my 'time hole' fear. Might it be possible that I specify some rare local time which makes sense but which is somehow straddling a daylight savings time jump so that it's not meaningful in UTC? I guess my question is ultimately what is the best format to store record expirations?

If MySQL can't store timezone information as part of its date/time information, then for any consistency you need to ensure that the same timezone is used for all stored dates. (Explicitly storing the timezone as an additional field isn't really an option because it's not easily available when doing calculations.)

So converting all timestamps to UTC for storage and comparison would make for a common frame of reference. (Use gmdate for example, assuming that PHP's local time(zone) is correct).

sneakyimp Might it be possible that I specify some rare local time which makes sense but which is somehow straddling a daylight savings time jump so that it's not meaningful in UTC?

No, UTC manages to assign a well-defined denotation for every moment, so there will always be a valid UTC timestamp for whatever local time gets thrown at it; proper timezone infrastructure won't be thrown going the other way either: there is no 2:15am if the clocks go forward at 2:00am, but that's automatically compensated for by the fact that daylight saving time and standard time have different offsets from UTC anyway.

      Could you just leave it up to the DB? Something like...

              $sql = "INSERT INTO  password_reset_token (
                user_id,
                token,
                expiration_datetime
        ) VALUES (
                " . $db->escape($user_id) . ",
                '" . $db->escape($password_reset_token) . "',
                TIMESTAMPADD(HOUR, 2, NOW())
        )";

      Then any query that checks it might be like WHERE expiration_datetime >= NOW().

      Write a Reply...