I'm struggling to pinpoint the error in my date/time difference calculations. It seems to be related to timezone variations, but I can't figure out exactly where the problem lies. Below are all the components involved - can you help me identify the mistake? Many thanks!
Firstly, I verified the mysql timezone settings:
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
On my local machine running macOS, the Date & Time settings are configured with the correct local time and Copenhagen timezone (CEST).
In my mysql database, I have a field defined as:
token_time_utc DATETIME DEFAULT NULL
To populate this field, I use NodeJS and the mysql2 connector like so:
'UPDATE `account` SET token = ?, token_time_utc = UTC_TIMESTAMP() WHERE user_id = ?', [token, account.user_id] ...
After sending an email with the token, I attempt to validate that it was received within 6 minutes, but encounter failure:
// Is the token validation done within 6 minutes based on UTC time?
let sixMins = (60 * 1000 * 6);
let tokenTime = account.token_time_utc.getTime();
let tokenExpiration = tokenTime + sixMins;
let now = new Date().getTime();
console.log('Account timestamp: ' + account.token_time_utc + ' Token time: ' + tokenTime + ' Token expiration: ' + tokenExpiration + ' Now: ' + now
+ ' Diff: ' + (now - tokenTime) + ' Diff (minutes): ' + ((now - tokenTime) / 1000 / 60));
if (now > tokenExpiration) {
// return error
res.status(401).json({ message: 'Token timed out' });
return;
}
The console output reveals:
Account timestamp: Tue May 07 2024 07:25:52 GMT+0200 (Central European Summer Time) Token time: 1715059552000 Token expiration: 1715059912000 Now: 1715066768248 Diff: 7216248 Diff (minutes): 120.2708
This entire process occurs locally on my machine. At the time of execution, it was 9:25 am in Copenhagen, which is currently at GMT+2. The discrepancy of 120 minutes appears to stem from incorrect timezone handling. Despite attempting to adhere to UTC throughout, the DB stored the accurate UTC time relative to mine (7:25 am), yet annotated it with CEST for Copenhagen (which puzzles me).
Could the issue lie in how the time is submitted to the DB using the UTC_TIMESTAMP() command, or perhaps in the utilization of account.token_time_utc.getTime()? Alternatively, could there be mistakes in my usage of JS Date methods?
Should I consider configuring mysql timezone settings? I came across these discussions, but I'm unsure if they address my specific problem: Should MySQL have its timezone set to UTC? How do I set the time zone of MySQL?