Time change issue

Hello,

I am running rows counts on MySQL tables every day at midnight.

My setup usually works well, but I had some issues (I suspect) related to French time change over the weekend.

Everyday at midnight, I want “today” to be equal to today’s date and “yesterday” to be equal to yesterday’s date.

Below is the syntax I used:

var today = new Date().toLocaleString("fr-FR", {timeZone: "Europe/Paris"});

var yesterday = new Date();
yesterday.setDate(yesterday.getDate()-1);
yesterday = yesterday.toLocaleString("fr-FR", {timeZone: "Europe/Paris"});  

French time changed from winter to summer time at 2.00am on Sunday March 31st.

When my code ran on Sunday March 31st at midnight (2 hours before time change), I got:

  • today = “2024-03-31”: OK
  • yesterday = “2024-03-30”: OK

When my code ran on Monday April 1st at midnight, I got:

  • today = “2024-04-01”: OK
  • yesterday = “2024-03-30”: KO, should have brought “2024-03-31”

When my code ran on Tuesday April 2nd at midnight, I got:

  • today = “2024-04-02”: OK
  • yesterday = “2024-04-01”: OK

Do you know why when the code ran on March 31st, “yesterday” was incorrect?

Thanks!

  • Database: MySQL
  • Running n8n via n8n cloud
  • Operating system: Mac OS

It looks like your topic is missing some important information. Could you provide the following if applicable.

  • n8n version:
  • Database (default: SQLite):
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app):
  • Operating system:

@matthieu , your initial yesterday does not have the time zone set. You set it after you already done the one day deduction. Try setting timezone first before taking away 1 day.

1 Like

@ihortom thank you for your reply and the suggestion! I actually tried that initially, but I get an error message if I apply the getDate() function to a date converted to a string (swapping lines 2 and 3 for yesterday’s definition). Do you know how to solve that?

I still do not understand why my code failed specifically on THAT night. It worked well before and still works well now. For example for last night (Apr 2nd > Apr 3rd) when it ran at midnight French time UTC+2 (+ 1 sec for the example), I get Apr 2nd for yesterday as expected:

var yesterday = new Date();

2024-04-02T22:00:01.000Z

yesterday.setDate(yesterday.getDate()-1);

2024-04-01T22:00:01.000Z

yesterday = yesterday.toLocaleString(“fr-FR”, {timeZone: “Europe/Paris”});

02/04/2024 00:00:01

Hi there,

I recommend to use the Luxon functionality.

for example=
today = $today
yesterday = today.minus({days:1})

to get a string
todayString = today.toISODate();
yesterdayString = yesterday.toISODate();

Ps. these can also be used inside expressions so you do not need the code node. :slight_smile:

1 Like

Because that is the only date (when the daylight saving time changes) when you can encounter such an issue if you do not take into consideration the timezone difference and the time when the clock switch takes place. It also depends on the server where you execute those calculations, what time and time zone is set there.

1 Like

Ok thanks @ihortom

Thanks @BramKn that looks great, I will implement that!

1 Like