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