-1

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 is within the last hour. I gave up trying the DATE_SUB(DATE(), INTERVAL 1 HOUR method and am now using Carbon. Either way, I'm only getting results when I set the interval to several days, and none of what returns are what I'm looking for. In addition, I have data with a DateTime stamp from very recently.

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";
Karl Hill
  • 12,937
  • 5
  • 58
  • 95
sky_CSW
  • 1
  • so you want the last hour of today ? Because in your code you're checking whether `last_modified` is from yesterday or older. – ThS Sep 18 '22 at 21:13
  • Sorry, I should have said subHours. I was trying different things to test, one of them being if I could at least take it back a full day. – sky_CSW Sep 18 '22 at 22:49
  • 1
    Does this answer your question? [Fetching rows added last hour](https://stackoverflow.com/questions/3681345/fetching-rows-added-last-hour) – Don't Panic Sep 19 '22 at 10:00

1 Answers1

0

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?

Valentino
  • 465
  • 6
  • 17