Timezones with Node, MySQL, and AWS Lambda

Photo by Jiyeon Park on Unsplash

I remember being at an investor dinner when I worked for an up and coming tech startup. This was after our official presentation and we were kicking back and enjoying some casual conversation. One of the investors asked our CTO what the hardest technical challenge he found while working on software was. Without skipping a beat, our CTO responded “timezones”.

Now, lest you think less of our CTO, he was a brilliant dude who had built some very complex systems ground up, and at the time we had built a system that depended heavily on having to-the-second accurate information. He knew what was hard and what wasn’t.

If you work on an application that deals with dates and times across time zones without a deliberate strategy behind it, you’re likely showing incorrect information.

Time Zones

Let’s cut to the chase.

In a standard web application with persistence (a database) you will likely have an architecture simplified to this:

Your web browser connects to a server somewhere and retrieves information from a database. Part of this information may include a date and time, such as `2022–03–22 12:00:00`. The question is, what time does this represent?

It’s impossible to know without knowing what the timezone of the database is assumed to be. Without explicitly storing the timezone with the datetime stamp, it’s meaningless. All database servers have an assumed timezone, though, and this is where our puzzle begins.

Most of the time, your database server will be set to UTC, or Coordinated Universal Time. This is the primary standard time of the world, and all other time zones are described as being “offset” to this time. For instance, Eastern time in the United States can either be 4 or 5 hours behind UTC, depending on daylight savings time. You would represent this as UTC-4 or UTC-5.

Now, in my case, the database server is set to represent Eastern time, which gives me a reference by which I, or any consuming application, can understand the dates and times retrieved from a database.

Node to the Rescue?

Node is a very popular runtime framework for servers these days, and when you work with MySql using the popular Node MySql adapter MySqlJS, it will convert a datetime stamp it finds to a native JavaScript Date object, unless you tell it not to. When it does this, it assumes a timezone of the incoming timestamp, and if it’s not given one it will use the timezone of the current runtime.

In Steps AWS Lambda

AWS Lambda runs in UTC which means that if you construct a new JavaScript Date object in Node, it assumes the timezone to be UTC. Let’s take our datetime stamp above and see what this would do:

Database datetime stamp:

2022–03–22 12:00:00

If I took that date timestamp in an AWS Lambda function running Node and created a new Date from it like so:

new Date(‘2022–03–22 12:00:00’);

You’ll end up with a datetime stamp in JavaScript like this:

2022–03–22T12:00:00Z

Looks great right?

Here’s the problem, that `Z` at the end of the string means that this date and time is in UTC, which actually means that in Eastern time (my original timezone), my date time is this:

2022–03–22 07:00:00

5 hours off. Ain’t it wacky? This is because MySQL knows the dates it holds are in Eastern, but we aren’t telling Node that by default, so when we query the database and Node becomes helpful and converts those times to a native Date within JavaScript, we’re distorting our data.

There are lots of ways to handle this, but the key point is that it must be handled.

The Solution

In our situation, the most common scenario is this:

In order to make sure the Client receives the right data, the server and database need to be talking the same time zone, or at least understand where the other stands.

With the MySqlJS library above, this is pretty simple. When I establish a connection, I can give it the timezone to work in:

const db_config = {
  host     : 'localhost',
  user     : 'xxx',
  password : '',
  database : 'xxx',
  timezone: 'utc-5' // <---- This one
};

That last property is the most important, because it will tell Node to use an offset of `-5` when establishing the connection.

When you do this and you rerun our example from above you get the magical, correct, value:

Database datetime stamp:

`2022–03–22 12:00:00`

Now we run new up our date and time in JavaScript:

new Date(‘2022–03–22 12:00:00’);

Annnd our datetime stamp in JavaScript:

2022–03–22T17:00:00Z

This may be confusing because this is a UTC timestamp, but that’s what we want.

“2022–03–22T17:00:00Z” == “2022–03–22 12:00:00 EST”

Summary

TLDR;

If you do date comparisons or casting on a server, make sure that you know the timezone of the server as well as the origin of the data.

In addition:

1. Be specific about the timezones you’re using when passing around dates.
2. Understand the timezone of the runtimes you’re using.
3. Pass your dates and times around in consistent formats.

Happy coding.