I need help with a MySQL query that pulls data from a specific date range. I want to get records starting from the first day of the previous month at midnight up to the first day of this month at midnight.
My main issues are:
The datetime fields in my database are stored in UTC but I need them converted to my local timezone
I can’t figure out how to properly set the exact time (midnight) in my WHERE clause
Here’s what I have so far:
select product_name, created_at from orders.customer_data
where created_at >= date_format(last_day(now() - interval 2 month) + interval 1 day, '%Y-%m-%d 08:00:00')
and created_at < date_format(last_day(now() - interval 1 month) + interval 1 day, '%Y-%m-%d 08:00:00')
order by created_at;
I’m trying to use 08:00:00 as my midnight time (UTC offset), but when I check the results I still see 00:00:00 timestamps. This makes me think my time formatting is wrong or not being applied correctly. What’s the right way to handle this?
Had the same issue last year. DATE() makes this way simpler than your LAST_DAY() approach:
SELECT product_name, CONVERT_TZ(created_at, 'UTC', 'America/New_York') as local_created_at
FROM orders.customer_data
WHERE created_at >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01')
ORDER BY created_at;
This grabs the first day of last month and current month without all the LAST_DAY stuff. Keep your WHERE conditions in UTC since that’s how you’re storing data, then convert only in SELECT for display. Your original query was filtering fine - you just weren’t seeing the timezone conversion in the output.
you’re mixing up the timezone conversion. use CONVERT_TZ() instead of hardcoding the offset in date_format. try CONVERT_TZ(created_at, '+00:00', '+08:00') to convert from UTC to your local time before comparing dates.
You’re applying the time offset to your boundary dates but not converting the stored UTC timestamps for comparison. Your WHERE clause creates correct date boundaries, but the database timestamps stay in UTC when displayed. You need to either convert the stored timestamps to local timezone in your SELECT, or convert your boundary conditions to UTC instead. Since your data’s stored in UTC, it’s easier to convert your local midnight times back to UTC for the WHERE clause. Try this: replace your hardcoded ‘08:00:00’ with ‘00:00:00’ in the WHERE clause (keeps UTC boundaries), then add CONVERT_TZ(created_at, 'UTC', 'your_timezone') in your SELECT to display converted times. Your query logic stays in UTC but results show in local time.
you’re seeing 00:00:00 because that’s how the data’s actually stored in UTC. your boundary dates look right, but you need to convert the timestamps in your select clause if you want local time. try select product_name, convert_tz(created_at, 'utc', 'america/new_york') as local_time - that should do it.
Hardcoding timezone offsets can lead to issues during daylight saving changes. Instead of manually adding hours, utilize MySQL’s built-in timezone functions for accuracy.
To achieve what you’re aiming for, you might consider adjusting your query as follows:
SELECT product_name, created_at
FROM orders.customer_data
WHERE created_at >= DATE_SUB(DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH)
AND created_at < DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE())-1 DAY)
ORDER BY created_at;
This will allow you to determine the first day of the last month and the first day of the current month without relying on fixed offsets. If you need to display the timestamps in your local timezone, simply wrap the created_at field with the CONVERT_TZ function in your SELECT statement, while keeping your WHERE conditions in UTC to match the stored data format.