Issue

This Content is from Stack Overflow. Question asked by sky_CSW

I have been beating my head against a wall trying to get this query to work. I am trying to get results where the “last_modified” datetime field are within the last hour. I gave up trying to the “DATE_SUB(DATE(), INTERVAL 1 HOUR” method and am now using Carbon. With either method, I’m only getting results when I set the interval to several days and none of what return are what I’m looking for. I have data with a datetime stamp from very recently. Please help.

Current code:

$day_ago = Carbon::now()->subDays(1)->format(‘Y-m-d’);

$sql = "SELECT appt_id, provider_id, start_datetime, end_datetime, added_zen, appt_notes, pt_fname, pt_lname, conf_status, last_modified, zen_block_out_time_id
    FROM appointments WHERE is_del = 0 AND (
  zen_block_out_time_id IS NOT NULL AND (last_modified < ($day_ago)) AND location_id = 2) ORDER BY start_datetime ASC";

Solution

you explain in the comment that this example is something you tried, but you’re actually trying to get the records from within the last hour.

If the part of your code that’s not working is the Carbon method, then remember to format the result according to your needs. These both work:

$last_day  = Carbon::now()->subDays(1)->format('Y-m-d');
$last_hour = Carbon::now()->subHours(1)->format('H:i');

Then, you need to make sure which kind of column ‘last_modified’ is.
It could be a TIMESTAMP or a DATETIME or just a DATE.

If it is a DATE, you’re out of luck, you’ll have to change the database if you want to achieve your goal, because the column doesn’t contain information about "the time", only the date.

If instead it’s a TIMESTAMP you need to format the output from Carbon as a timestamp.

$last_hour = Carbon::now()->subHours(1)->timestamp;

But are you using a framework such as Laravel?

This Question was asked in StackOverflow by sky_CSW and Answered by Valentino It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.