Skip to content
Advertisement

SQLite query to find datetime difference between multiple rows

Here are my two tables’ structures in SQLite

enter image description here

enter image description here

  • if startJob is 1 it implies that the employee is starting the job and if startJob is 0 it means employee is stopping the job.

attendanceTable is sorted by mydate column

I want output as worked hour by individual employees.

Input of query can be two different dates e.g. 2021-08-20 and 2021-08-22

From which I want to know “How much each person has worked?”

Output should be:

12:00 to 16:00 + 22:00 to 03:00 = 9 hours
13:00 to 17:00 + 12:00 to 15:00 = 7 hours

Input of query 2021-08-20 and 2021-08-21 – output should be:

12:00 to 16:00 + 22:00 to 03:00 = 9 hours
13:00 to 17:00 = 4 hours

It may possible that Alice starts her job at 11 PM and end her job at 3 AM on next day[So working hour would be 4 hours]

Advertisement

Answer

I believe that the following will accomplish the results you desire:-

e.g. :-

enter image description here

and :-

enter image description here

Note All results with the exception of the time_worked are arbitrary values from the underlying data. However, userid and name will be correct as they would be the same for each group. The other values will be a value from the group.

  • you can easily apply changes to the final query to include or exclude columns.

The full testing SQL being :-

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement