Search code examples
javascriptmysql

Converting mysql UTC time field in JS?

I'm having trouble understanding where I'm going wrong in calculating a date/time difference. It seems to stem from timezone difference, but it's not clear where the issue is. Below are all the pieces. Where did I go wrong? Thanks!

I checked the mysql timezone:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

My local system is macOS and Date & Time is set with correct local time and Copenhagen timezone (CEST).

I have a mysql database with a field specified as:

token_time_utc DATETIME DEFAULT NULL

I then populate that field using NodeJS and mysql2 connector:

'UPDATE `account` SET token = ?, token_time_utc = UTC_TIMESTAMP() WHERE user_id = ?', [token, account.user_id] ...

I then send an email with the token and try to validate it that it is within 6 minutes but it fails:

// did they verify token within 6 minutes 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;
}

And here is what is logged to the console:

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 is all running locally on my machine. The time when I ran it was 9:25 am in Copenhagen, which is GMT+2 at the moment. It took less than a minute to receive the email and put in the token, and the diff of 120 minutes seems to be because the timezone isn't correct. And I tried to keep everything in UTC, and the DB stored the correct UTC time compared to me (7:25 am), but it is annotated to CEST which is Copenhagen (which is odd?).

When I submit the time to the DB is the problem that the UTC_TIMESTAMP() command is saying to use the current time as UTC? Or is doing account.token_time_utc.getTime() wrong? Or did I just mess up the use of JS Date methods?

Or do I need to set mysql timezone? I saw these posts but unsure if this is the issue: Should MySQL have its timezone set to UTC? How do I set the time zone of MySQL?

Solution

  • The mistake occurs because Date.now() or new Date() adds the timezone-offset/difference relative for the env (process.env.TZ, if no other informations are provided on the timestamp), while UTC_TIMESTAMP() ignores the timezone. You should either use CURRENT_TIMESTAMP() when storing it or set the node server TZ to UTC too.

    mplungjan comment to add Z to token_time_utc would work too.

    You could also use Date.UTC() and pass the parts from the UTC Timestamp to it.

    Which should be something like:

    const [str, year, month, day, hour, minute, seconds, ms] = 
  '2003-08-14 18:08:04.123'.match(
    /^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})\.?(\d+)?$/
  )
console.log(
  new Date(Date.UTC(year, month -1, day, hour, minute, seconds, ms)).toLocaleString()
)