mysqlmindate

Select mininum timestamp per user per day per action MySQL


I am trying to attain the minimum timestamp organized per user then date for a particular action. Thank you for any help.

I researched the code below, but unsure.

SELECT a1.*
FROM accesscards a1
JOIN (SELECT name, MIN(entrydates) mindate
      FROM accesscards
      WHERE name != ''
      GROUP BY name, date(entrydates)) a2
ON a1.name = a2.name AND a1.entrydates = a2.mindate

from Get records for each person's each day's min datetime

Current Table

+----------+------+--------+---------------------+
| idRecord | user | action | recorded            |
+----------+------+--------+---------------------+
| 1        | bob  | start  | 2018-01-27 01:00:01 |
+----------+------+--------+---------------------+
| 2        | amy  | close  | 2018-01-27 01:00:01 |
+----------+------+--------+---------------------+
| 3        | bob  | start  | 2018-01-27 02:00:01 |
+----------+------+--------+---------------------+
| 4        | amy  | start  | 2018-01-27 00:00:01 |
+----------+------+--------+---------------------+
| 5        | amy  | start  | 2018-01-28 00:00:01 |
+----------+------+--------+---------------------+
| 6        | jim  | open   | 2018-01-28 00:00:01 |
+----------+------+--------+---------------------+
| 7        | bob  | close  | 2018-01-27 02:00:01 |
+----------+------+--------+---------------------+
| 8        | bob  | start  | 2018-01-28 00:00:01 |
+----------+------+--------+---------------------+
| 9        | jim  | close  | 2018-01-28 00:00:01 |
+----------+------+--------+---------------------+

Desired Result

+------+----------+--------+---------------------+
| User | Date     | action | recorded            |
+------+----------+--------+---------------------+
| amy  | 01-27-18 | start  | 2018-01-27 00:00:01 |
+------+----------+--------+---------------------+
| amy  | 01-28-18 | start  | 2018-01-28 00:00:01 |
+------+----------+--------+---------------------+
| bob  | 01-27-18 | start  | 2018-01-28 01:00:01 |
+------+----------+--------+---------------------+
| bob  | 01-28-18 | start  | 2018-01-28 00:00:01 |
+------+----------+--------+---------------------+
| jim  | 01-28-18 | start  | 2018-01-28 00:00:01 |
+------+----------+--------+---------------------+

Solution

  • You need to group by with name, action and date(entrydates)

    SELECT name, date(entrydates), action, MIN(entrydates)
    FROM accesscards
    WHERE action='start'
    group by name, action, date(entrydates)
    

    using WHERE action='start' ensure we don't group multiple instance ('start' and 'close') also, action type start is always going to be minimum.