如何总结每月每周使用的总时间? [英] How to sum total time utilize in each week of a month?

问题描述

在 mysql db 中，我有一个名为 time_utilize 的字段，其类型为 time ，它存储一天中花费的总时间，该表包含每个月的记录.现在我想找到用户在选定月份的每周花费的总时间.我搜索了那个，但我发现了一个很好的查询来查找整个月花费的总时间，即:

In mysql db I have a field named time_utilize with type time which store total time spend in a day and that table contain records for every month. Now I want to find total time spend by a user in each week of a selected month. I searched for that but I found an excellent query to find total time spend in whole month which is:

SELECT Sec_to_Time(Sum(Time_to_Sec(time_utilize))) FROM attendance WHERE staff_id = 'sajid'

现在谁能找到一个月中每周使用的总时间?

Now who can I find the sum of total time utilize in each week of a month?

推荐答案

假设 attendance 表有一个日期列 attendance_date，下面的查询可能会给你总时间当月每周使用:

Assuming that the attendance table has a date column attendance_date, the below query may give you total time utilized per week in the month:

SELECT WEEK(`attendance_date`) `attendance_week`,
SEC_TO_TIME(SUM(TIME_TO_SEC(`time_utilize`))) `attendance_time_utilized`
FROM `attendance`
GROUP BY `attendance_week`;

在上述查询中，attendance_week 计算为一年中的第几周，介于 1 和 52 之间.

In the above query, the attendance_week is calculated as the week of the year, between 1 and 52.

另一种输出形式可能是将周数显示为 1、2、3、4.为此，您可以试试这个查询:

Another form of output might be to show weeks as 1,2,3,4. For it, you may try this query:

SELECT (`attendance_week` - `min_week` + 1) `att_week`, `attendance_time_utilized`
FROM (
    SELECT WEEK(`ATT`.`attendance_date`) `attendance_week`,
    SEC_TO_TIME(SUM(TIME_TO_SEC(`ATT`.`time_utilize`))) `attendance_time_utilized`,
    `T1`.`min_week`
    FROM `attendance` `ATT`
    INNER JOIN (SELECT MIN(WEEK(`attendance_date`)) `min_week` FROM `attendance`) T1
    GROUP BY `attendance_week`
) T2;

希望以上查询能帮助您获得所需的结果.请根据您的架构更改表名和列名.

Hope the above queries help you with the desired results. Please change the table and column names as per your schema.

这篇关于如何总结每月每周使用的总时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！