I have an issue where I have two datetime components (from and to). I am trying to get the timezone in CST/CDT (depending on what time of year it is, it will either be CST or CDT with Chicago).
The issue is - our MySQL DB stores the datetime in UTC. I have the datetime component set to UTC format and when I add the datetime component value to my query I am using this:
BETWEEN {{ moment(dateAndTimeFrom.value).utc().format("YYYY-MM-DD HH:mm:ss").toString() }} AND {{ moment(dateAndTimeTo.value).utc().format("YYYY-MM-DD HH:mm:ss").toString() }}
I have tried several varieties of the above like this:
BETWEEN {{ moment(dateAndTimeFrom.value).utc().tz("America/Chicago").format("YYYY-MM-DD HH:mm:ss").toString() }} AND {{ moment(dateAndTimeTo.value).utc().tz("America/Chicago").format("YYYY-MM-DD HH:mm:ss").toString() }}
however, no matter what I try, it doesn't seem to want to bring back the correct datetime.
The datetime for the "from" date should be 2025-03-01 at midnight and the "to" date should be 2025-03-31 at 11:59:59pm. And then this has to be converted to CST/CDT depending on the time of the year which right now the offset is 0600 hours (can we just all agree just to abolish timezones lol)?
Anyway, any insights or thoughts on this would be quite helpful. Thanks!
When the user is selecting dates, are those dates implicitly in central time? Do you actually want time as part of this? Or is this just a reporting system so the user can see data for March 1st (in central time)?
The dates (in the DB) are UTC. The date format on screen is their local time (which could be Chicago timezone or NewYork timezone, etc). But, the query itself has to be Chicago timezone since that is the datetime stamp everyone goes by from the company. Hope this makes sense.
What I am asking is if the user is selecting a date and time on their screen (they’re in New York): Say 2025-03-01 12:00 to 2025-03-01 14:00. When they get results:
Do they expect to see data for 12pm to 2pm ET? (11am to 1pm CT)
The above returns "2025-03-31 16:59:59" which is incorrect. It should be "2025-04-01 04:59:00"
Is there a way to get the right datetime back with just moment instead? Then I could push the correct dates and times inside the mysql statement without having to have the convert_tz() function in there (although I agree, this should technically work). Thanks for all your help!
It’s not 11:59 it’s 23:59 if you want midnight. There’s no AM/PM.
Also don’t try and maximize the minutes. Use less than the next hour or date and two operations as I showed, instead of the BETWEEN operator. Otherwise you’ll miss records created in the last minute, or last second, or last millisecond for that range.
Sorry, typo there. I changed it and now when I run this in my mysql query (without retool) it brings back "2025-04-01 04:59:00" which is correct for "2025-03-31 23:59:00".
I went to retool and it "still" wasn't working correctly. Finally, I did figure it out. You have to turn "off" the "manage timezones" on the date from and date to components for it to work correctly. When I did that, everything else lined up!
Oh, and for any one else who runs into this issue - I did have to change the mysql statement in retool like this:
date_entered BETWEEN convert_tz({{dateAndTimeFrom.value}}, 'America/Chicago', 'UTC') AND convert_tz({{dateAndTimeTo.value}}, 'America/Chicago', 'UTC')
Basically, leaving out the "moment" portion of it that was in the solution from @offpath since I took off the "Manage Timezones" from the date components.
Sorry, typo there. I changed it and now when I run this in my mysql query (without retool) it brings back "2025-04-01 04:59:00" which is correct for "2025-03-31 23:59:00".