Timezones with Node, MySQL, and AWS Lambda
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 “time zones”.
Now, lest you think less of our CTO, he was a brilliant person 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 time zone of the database is assumed to be. Without explicitly storing the time zone with the datetime stamp, it’s meaningless. All database servers have an assumed time zone, 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 time zone of the incoming timestamp, and if it’s not given one it will use the time zone 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 time zone 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 time zone), 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, 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 time zone to work in:
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
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 time zone of the server as well as the origin of the data.
In addition:
- Be specific about the time zones you’re using when passing around dates.
- Understand the time zone of the runtimes you’re using.
- Pass your dates and times around in consistent formats.
Happy coding.
