0

Trying to write a mySQL query that selects the most recent(**) number of times a light was turned on by a user.

**Most recent being all the times the light was turned on for the most recent day in the DB.

Sample Table: DB Name: LLL Table Name: Lights

UserID    | LightOn               | LightOff
-----------------------------------------------------
3         | 2018-01-08 09:00:00   | 2018-01-08 09:03:00
3         | 2018-01-08 10:15:00   | 2018-01-08 10:17:00
3         | 2018-01-07 15:00:00   | 2018-01-07 15:05:00

So, From this table, we can tell that

UserID 3 (Bob) turns the light on:
2 times on January 8th (at 9AM for 3 minutes and 10:15AM for 2 minutes) &
1 times on January 7th (at 3PM for 5 mins)

I want my query to return 2, because there are 2 records for the most recent day of January 8th.

I'm at the point where I can only get the number of records:

SELECT COUNT(C.LightOff) AS count FROM LLL.Lights AS C 
WHERE C.UserID = 3
ORDER BY C.LightsOff DESC

I get the following back:

count
-------
3

I need to figure out a way to convert the time stamp into a DAY and get all the records that match that MAX Day.

The desired result is:

count
-------
2

Any ideas?

baconLikeTheKevin725
  • 225
  • 1
  • 4
  • 11

1 Answers1

2

Assuming you have a proper datetime value in you lightoff column
you could get the most recent day and join with your count

  select count(*) from LLL.Lights 
  inner join  (
      select  max(date(LightOff)) max_date
      FROM LLL.Lights 
      WHERE UserID = 3
      ) t on t.max_date = date(LightOff)
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107