MYSQL one day interval from 00:00:00 to 23:59:00

Question

I am trying to pickup 1 day previous records based on few dates and time stamps but my is not retrieving recorders from 00:00:00 to 23:59:00. I am using the follwoing criteria.

and ((cast(from_unixtime(schedule.last_change_timestamp) as date) = (curdate() - interval 1 day)) or (cast(schedule.date_added as date) = (curdate() - interval 1 day)) or (left(jobsheet.actual_completion_date_set, 10) = (curdate() - interval 1 day)) or (cast(from_unixtime(orders.dateadded) as date) = (curdate() - interval 1 day)))),

The records which I am missing are most likely related to jobsheet.actual_completion_date_set which is in this format 'yyyy-mm-dd hh:mm:ss' and I am using left 10 characters for interval. Please could someone provide some help as to how I can force all these dates to select all records from 00:00:00 to 23:59:00 the previous day.

Thanks,

Answer 1

Is this what you want?

left(jobsheet.actual_completion_date_set, 10) = date_format(date_sub(curdate(), interval 1 day, '%Y-%m-%d')

In MySQL, I find it safer to use date_sub() and date_add() for the built-in constants. These are not returned as datetime or date, so strange things can happen with conversions to the dates.