Selecting the last entry for the day in laravel

I am having a hard time figuring this one out, heres an example from my development database:

+------------------+-------------------+---------------------+---------------------+
| amount_signed_in | amount_registered | created_at          | updated_at          |
+------------------+-------------------+---------------------+---------------------+
|                1 |                 0 | 2021-07-07 09:23:09 | 2021-07-07 09:23:09 |
|                1 |                 1 | 2021-07-07 09:33:07 | 2021-07-07 09:33:07 |
|                2 |                 1 | 2021-07-07 09:33:07 | 2021-07-07 09:33:07 |
|                2 |                 1 | 2021-07-07 13:50:47 | 2021-07-07 13:50:47 |
|                3 |                 1 | 2021-07-07 13:57:12 | 2021-07-07 13:57:12 |
|                4 |                 1 | 2021-07-07 13:58:42 | 2021-07-07 13:58:42 |
|                4 |                 2 | 2021-07-07 15:20:29 | 2021-07-07 15:20:29 |

Lets call this table: site_access

As we can see, every time some one logins or registers, we take the last number and add one to it. This will continue to increase through out the day for both amount_signed_in and amount_registered.

The thing is, at the end of the day (midnight) if some one then logs in say at midnight or 12:01am, the table will reset signed in to 1, and registered to 0 and continue again.

My question is:

How do I get the last entry for the day for all days in the table, based on created_at?

So, for example, lets add a couple more rows to this table:

|               10 |                 2 | 2021-07-07 20:18:25 | 2021-07-07 20:18:25 |
|                1 |                 0 | 2021-07-08 13:04:27 | 2021-07-08 13:04:27 |

So if we add this to the table above, we can see that we were at 10 on the 7th, and then 1 on the 8th. The query would return me a collection that contains:

  • One entry for the 7th (10, 2, created, updated)
  • One Entry for the 8th (1, 0, created, updated)
  • And so on ….

It should only be one entry per day and it should be the last entry for that day. The issue is, the “last entry for the day” could be 2pm, while the next one (and could only be one) could be 3 pm the next day.

I cannot figure out how to come up with a query that does this. Thoughts?

If a query would not be appropriate, is there another way? There could be thousands of entries

Answer

If you’re using a MySQL version that supports window function, you can use ROW_NUMBER(). Older version uses a slightly longer and possibly require more understanding of the process compared to ROW_NUMBER() function but it does deliver the same result in the example.

Using ROW_NUMBER() function:

SELECT amount_signed_in, amount_registered, created_at, updated_at, rnum 
FROM
(SELECT *,
       ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at DESC) AS rnum
FROM mytable) A
WHERE rnum=1;

On older MySQL version:

SELECT amount_signed_in, amount_registered, created_at, updated_at, rnum 
FROM
(SELECT *,
       @rownumber:=CASE
        WHEN @date = DATE(created_at) THEN @rownumber + 1
          ELSE 1 END AS rnum,
    @date:=DATE(created_at)
FROM mytable
CROSS JOIN (SELECT @date := 0, @rownumber := 0) AS N 
ORDER BY created_at DESC) A
WHERE rnum=1
ORDER BY created_at;

The idea is to assign row number 1 for the last entry of the day partitioned by the date portion of created_at column and created_at is sorted in descending order. Once that done, make that as a sub-query then add WHERE that will only return row number 1.

Demo fiddle