Can't Figure Time Zone Issue

Questions
1

So I have two workflows one of them process Facebook leads as soon as they are submitted, by the end of the process I run a SQL query that will fetch how many leads I have TODAY by campaign then I have an google sheets node with upsert that will add this information to a google sheets for that day. I also have another workflow that will clear the sheet everyday at midnight.

However I am getting an issue, from 00:00 to 01:00 my SQL query is returning yesterdays leads / campaign putting it into my sheets, and then after 01:00 when it starts fetching correctly it’s too late because since I have my sheets node is with upsert I end up only with the correct amount of leads in the campaigns that had leads today, if I have campaigns with no leads today but leads yesterday they will display and this is quite critical because we then run Facebook Rules on Biirch bot depending on this information. Here is my query.

SELECT
a.campaign_id,
COUNT(*) AS approved_leads_today
FROM gerodev_repsol_pt.campaigns AS a
JOIN gerodev_repsol_pt.time_data AS b
ON a.lead_id = b.lead_id
JOIN gerodev_repsol_pt.status AS s
ON a.lead_id = s.lead_id
WHERE s.external_status = ‘Approved’
AND b.date = ‘{{
(() => {
const now = new Date();
const pad = (n) => n.toString().padStart(2, ‘0’);
return ${now.getFullYear()}-${pad(now.getMonth() + 1)}-${pad(now.getDate())};
})()
}}’
GROUP BY a.campaign_id;

I’ve ran multiple tests… And everything seems to be fine with time zones… My workflow timezone is correctly set to Europe/Lisbon, when I change my query date script to:

{{
(() => {
const now = new Date();
const pad = (n) => n.toString().padStart(2, ‘0’);
return ${now.getFullYear()}-${pad(now.getMonth() + 1)}-${pad(now.getDate())} ${pad(now.getHours())}:${pad(now.getMinutes())}:${pad(now.getSeconds())};
})()
}}

The time matches my time zone time perfectly…

On MySQL I run

SELECT
NOW() AS server_now,

The server_now displays perfectly in my time zone as well…

I can’t figure why the query is not correctly fetching the information from 00:00 to 01:00…

Anyone can help, can this be a workflow issue?

Information on your n8n setup

  • **n8n version:1.92.0
  • **Database (default: SQLite):MySQL
  • **n8n EXECUTIONS_PROCESS setting (default: own, main):default
  • **Running n8n via (Docker, npm, n8n cloud, desktop app):Cloud
  • **Operating system:MacOS
2

Hey @Joao_Marques

Please try to run this sql query

SELECT
    a.campaign_id,
    COUNT(*) AS approved_leads_today
FROM gerodev_repsol_pt.campaigns AS a
JOIN gerodev_repsol_pt.time_data AS b
    ON a.lead_id = b.lead_id
JOIN gerodev_repsol_pt.status AS s
    ON a.lead_id = s.lead_id
WHERE s.external_status = 'Approved'
AND DATE(CONVERT_TZ(b.date, '+00:00', '+01:00')) = '{{ 
  (() => {
    const now = new Date();
    const pad = (n) => n.toString().padStart(2, '0');
    return `${now.getFullYear()}-${pad(now.getMonth() + 1)}-${pad(now.getDate())}`;
  })()
}}'
GROUP BY a.campaign_id;

Here I actually tried Updating the SQL query with the CONVERT_TZ() function…

Reason: I think there is timezone mistmatch when you are comparing the dates.

  • Your b.date column in the database could be in UTC (or another time zone).
  • When you’re comparing it with today’s date (using new Date()), you’re comparing it in Lisbon time, which could be off by a day when it’s between 00:00 and 01:00 (since the time difference would push the comparison to the previous day in UTC).

So I thought changing the timezone would work…

So please feel free to try this and let me know if it works or not.

1 Like
3

Thanks for the answer, I’m actually going on vacation and will only be able to test this when I come back. But I’ll be sure to give feedback when I test it :slight_smile:

4

You’re very welcome!

enjoy your vacation! :palm_tree::palm_tree: