Issue
I’m working with MySQL, and attempting a specific time range query. For example if it’s august now, I’m attempting to query from September 1st at midnight, to August 1st at midnight.
So far I’m able to get my date range successfully selected thanks to a prior question, but the problems I’m having are:
-My database columns for time are in UTC, and I need to convert to my timezone
-I’m having trouble adding the specific time into my query.
Here is my current query:
select column1,date_time_column from table.x
where date_time_column > concat(last_day(curdate() - interval 2 month), '%Y-%m-%d 05:00:00') + interval 1 day
and date_time_column < concat(last_day(curdate() - interval 1 month), '%Y-%m-%d 05:00:00') + interval 1 day
order by date_time_column
;
I’m attempting to put my specific time as 05:00:00, which is midnight in my DB, but right now when I run my select, it’s still showing 00:00:00 times, which makes me think my time isn’t applying or I have my syntax for adding the time incorrect. Any help would be much appreciated.
Solution
Use DATE_FORMAT instead of CONCAT
SELECT DATE_FORMAT(last_day(curdate() - interval 2 month), '%Y-%m-%d 05:00:00') + interval 1 day, DATE_FORMAT(last_day(curdate() - interval 1 month), '%Y-%m-%d 05:00:00') + interval 1 dayDATE_FORMAT(last_day(curdate() - interval 2 month), '%Y-%m-%d 05:00:00') + interval 1 day | DATE_FORMAT(last_day(curdate() - interval 1 month), '%Y-%m-%d 05:00:00') + interval 1 day :---------------------------------------------------------------------------------------- | :---------------------------------------------------------------------------------------- 2021-07-01 05:00:00 | 2021-08-01 05:00:00
db<>fiddle here
select column1,date_time_column from table.x
where date_time_column > DATE_FORMAT(last_day(curdate() - interval 2 month), '%Y-%m-%d 05:00:00') + interval 1 day
and date_time_column < DATE_FORMAT(last_day(curdate() - interval 1 month), '%Y-%m-%d 05:00:00') + interval 1 day
order by date_time_column
;
Answered By – nbk
This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0