👀 Views: 1267 💬 Answers: 1 📅 Created: 2025-06-28

mysql datetime timezone sql

I need some guidance on Quick question that's been bugging me - After trying multiple solutions online, I still can't figure this out. I'm working with an scenario with MySQL 8.0 where time zone conversions seem to behave unexpectedly when comparing datetime values. I have a table `events` that stores timestamps in UTC but I need to retrieve and compare these values in 'America/New_York' time zone. My query looks like this: ```sql SELECT id, event_time, CONVERT_TZ(event_time, 'UTC', 'America/New_York') AS local_time FROM events WHERE CONVERT_TZ(event_time, 'UTC', 'America/New_York') > '2023-10-01 00:00:00'; ``` This query runs without errors, but it returns no results even though there are events in October. To troubleshoot, I've tried running just the conversion on the timestamps to see the output: ```sql SELECT event_time, CONVERT_TZ(event_time, 'UTC', 'America/New_York') AS local_time FROM events; ``` The output shows correct local times, but when I check the event times directly in UTC, I see entries scheduled after the specified datetime. I also attempted to set the time zone globally using: ```sql SET GLOBAL time_zone = 'America/New_York'; ``` However, this didn't change the results of my original query. I've ensured that the `event_time` field is indeed stored as DATETIME without timezone information. Is there any additional configuration I might be missing? What could be causing this discrepancy in the results? Any insights or best practices for handling timezone conversions effectively in MySQL would be greatly appreciated. For context: I'm using Sql on Linux. Any help would be greatly appreciated! This is part of a larger service I'm building. I'd really appreciate any guidance on this.