CODEWITHSUNDEEP
mysqlsqlselecttimestamp
M Miller
M Miller

Reputation: 5642

MySQL query to group timestamps by days

I have a table of employee sign-ins and I'm trying to generate a summary for the employee of the number of hours he has worked each day this week.

My table looks something like this:

id | user | time_in             | time_out            | break
-------------------------------------------------------------
1  | 6    | 2014-12-14 05:00:00 | 2014-12-14 07:00:00 | 15
2  | 6    | 2014-12-14 09:00:00 | 2014-12-14 14:00:00 | 30
3  | 6    | 2014-12-14 14:00:00 | 2014-12-14 20:00:00 | 0
4  | 6    | 2014-12-15 08:00:00 | 2014-12-15 11:00:00 | 15

The break value is in minutes, so I'm calculating the employee's hours for the day via time_out - time_in - 60 * break. The time_in and time_out values are of type TIMESTAMP.

This is my query so far. It seems to return null for day and I can't figure out why.

SELECT
    SUM(UNIX_TIMESTAMP(`time_out`) - UNIX_TIMESTAMP(`time_in`) - (`break` * 60)) AS `time`,
    DATE_FORMAT(FROM_UNIXTIME(`time_in`), "%Y-%m-%d") AS `day`
FROM `sign_ins`
WHERE
    `time_in` >= "2014-11-30 00:00:00" AND
    `time_out` <= "2014-12-07 00:00:00" AND
    `user` = 6
GROUP BY `day`
ORDER BY `day` ASC;

Upvotes: 0

Views: 191

Answers (1)

J A
J A

Reputation: 1766

From the schema above, it doesn't look like you have used timestamp for time_in and time_out fields. So the correction in your query should be..

SELECT
    SUM(UNIX_TIMESTAMP(`time_out`) - UNIX_TIMESTAMP(`time_in`) - (`break` * 60)) AS `time`,
    DATE_FORMAT(`time_in`, "%Y-%m-%d") AS `day`
FROM 
    `sign_ins`
WHERE
    `time_in` >= "2014-11-30 00:00:00" AND
    `time_out` <= "2014-12-07 00:00:00" AND
    `user` = 6
GROUP BY `day`
ORDER BY `day` ASC;

Also, I am not sure if you are using it right by subtracting to timestamp to get a time difference. It should be something like..

SUM(TIMESTAMPDIFF(MINUTE, `time_out`, `time_in`) - (`break` * 60))

Upvotes: 1

Related Questions