Date and Time - 1/6 - It can be tricky

After writing the code that deals with date and/or time I had to stop and think: will it work the same in the client's time zone, will it have problems with daylight saving changes, will it break if Google Sheets cell changes the date format, what if the client temporarily changes the time zone... All the typical coder doubts.

Then I summarized everything I learned in the Date and Time Guide. Now I have to think much less about how to code the solution. At the same time, I am more confident in my code.

Surprises

Date parsing

We all like the date in the format "YYYY-MM-DD", it's the perfect date for programmers, as explained in this meme. When the date in the "perfect date" format gets parsed into a Date variable:

function test() {
  const str = "2023-02-26";
  console.log(new Date(str));
}

We get:

OK, I am in Belgrade, CET zone, it's off by one hour, no big deal. But if my GAS project was in Los Angeles I would get a different date too:

The "perfect date" format is obviously parsed using the UTC time zone.

But another pretty usual format "MM/DD/YYYY" gets parsed using the GAS project time zone. Code:

function test() {
  const str = "02/26/2023";
  console.log(new Date(str));
}

Outputs the following logs for the GAS project in CET and PT zones:

It looks nice, we don't get the time offset.

Hmm, so the "perfect date" format is different.

Well, not exactly. If you add the time part - it uses a different rule, not UTC zone anymore, but GAS project zone:

function test() {
  const str = "2023-02-26 00:00:00";
  console.log(new Date(str));
}

You don't even need the complete time part to move it to the "local" zone, one space is enough.

function test() {
  const str = "2023-02-26 ";
  console.log(new Date(str));
}

Strange, isn't it? It's all inherited from JavaScript. But Sheets have their own surprises, let me show you.

Date Shifting

In Google Sheets, I made a formula to calculate the time difference in hours. One moment is yesterday noon, that is a manual input into cell B2. The other is NOW formula in cell B1. The calculation is as expected, a little above 25 hours.

My GS file was in the CET time zone. When I change it to Pacific Time it gets reloaded, but I get 16 instead of 25 hours:

Cell B1, the one with the formula, was "shifted" into the new time zone. But cell B2, with the manual input, wasn't shifted. Looks strange and slippery, but there is a perfectly reasonable explanation for this design, we'll get to that.

If you are already planning to make up some task similar to this one and give it to your colleagues (just to confuse them) - I won't stop you 😁.

Daylight Saving

Back to the code, of course, daylight saving has its own traps. The same date and time string can represent two moments. Let's check CET leaving the daylight saving:

function test() {
  const str = "2022-10-30 01:30:00";
  let dt = new Date(str);

  const MS_IN_HOUR = 60 * 60 * 1000;

  for (let i = 0; i < 4; i++) {
    console.log(dt);
    dt = new Date(dt.getTime() + MS_IN_HOUR);
  }
}

We get the string duplication:

When you try to convert the string ("2022-10-30 02:30:00") back to a time - it has to choose from these two moments. It will choose the first one (02:30:00 Summer Time).

When entering daylight saving mode, it's the opposite case, we get a gap:

So we can even pass the impossible moment ("2022-03-27 02:30:00") and it will jump one hour ahead to a valid value (03:30:00). Can we even get the impossible string from anywhere? Yes, different countries change to daylight saving on different dates. The impossible moment in CET is valid in the Pacific Time zone.

Quick note: These impossible moments are perfect for giving promises. My better half likes horses, last year I burst into the room and told her excitedly: "I saw a horse on the internet auction, tonight at 2:30 AM I am buying you one" 🤣.

The Plan

There are some more surprises but I'll leave them for later. Date and Time can be tricky, if we don't think about them they can bite us.

I can imagine the hypothetical worst-case scenario where I write the code, test it, everything works fine in my time zone. Then I give it to the client for testing, some guy that is not super-precise tries it, takes a quick look and approves it. After a while, when I am in the middle of an important and time-critical project, some other member of the client's team starts the precise analysis and finds problems: "The invoice #654321 from Feb 26th 00:00:30 was accounted for the previous day". Valid reports, real problems, caused by different zones, different daylight saving rules... I would have to investigate, debug, spend time, my current project would suffer.

It's better to be prepared than wait for Murphy's law to happen. On such slippery topics and problems - I like to bite the problem, spend some time chewing it and spit out some summary that I can use in the future.

For those looking to jump to the summary right away, I have the bad news: it won't do you much good if you don't understand what's going on under the hood. Luckily, you have to understand it just once, so let me take you on a journey, we will:

• Check the definitions of moment, date and time
• Define a way how to work with the timestamps
• Define a way how to work with the date and time when reading from Google Sheets
• Define a way how to work with the date and time when writing to Google Sheets
• Pack the conclusions into a summary, for future usage

Questions, Corrections and Suggestions are appreciated: contact me.