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?
@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.
@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:
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.
