CODEWITHSUNDEEP
mysqlsql
draft
draft

Reputation: 303

Trying to find average start time within a range of start times with SQL?

I have data on employee punch-ins and punch-outs. I'm trying to write a SQL query that returns average start time and stop time.

For example, if employee A records the following punch-in data:

enter image description here

what are some ways to find average start and stop time?

So far I've tried:

"SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(`start_time`))) FROM `table` WHERE `date` BETWEEN 'dateRange1' AND 'dateRange2';"

Solution (thanks @Subin C Poonamgode for your help!):

SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(`start_time`))) 
    FROM `table` WHERE `date` BETWEEN 'dateRange1' AND 'dateRange2' AND `start_time` <> '00:00:00';

Upvotes: 0

Views: 178

Answers (1)

Subin Chalil
Subin Chalil

Reputation: 3660

This query prints avg. check in time for Employee A(employee_id = 1).

SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(`start_time`))) 
    FROM `table` WHERE `date` BETWEEN 'dateRange1' AND 'dateRange2' AND
    `table`.employee_id =1  AND `start_time` <> '00:00:00'

Hope this helps.

Upvotes: 1

Related Questions