Timezones with Node, MySQL, and AWS Lambda - Softrams

  • Press
  • Software Development

Chris Hand
April 13, 2022 154 views

A monitor is displayed in front of clocks reprinting various international time zones.

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: 

Database Connection Example

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: 

  1. Be specific about the time zones you’re using when passing around dates. 
  1. Understand the time zone of the runtimes you’re using. 
  1. Pass your dates and times around in consistent formats. 
     

Happy coding. 

 



More Stories

  • Softrams Latest Collaboration With a Local University

    Zara Ikram
    June 28, 2021

    Softrams and Maryland's largest HBCU, Morgan State University partnership seeks to address innovative pioneering solutions for the benefit of Morgan’s student body.

  • Logs Matter!

    Larry Bensky
    March 29, 2021

    Take a look at how we can keep our information safe, secure, and impenetrable from malicious attacks by looking into your log activity. Logs are records of events that happen on your computer, either by a person or by a running process.

  • Mobile Security Often Overlooked

    Yusuf Richardson
    October 30, 2021

    The extensive usage of mobile devices in today’s work environment has led to a significant rise in the risk for data theft. Securing mobile devices requires a multi-layered approach and investment in enterprise solutions.