mysql - 当您在 mysql 或 tableau 中有登录和注销时间戳时，按小时计算登录时间

Question

我有登录和注销时间。需要按小时计算登录时间（按小时计算）

样本输入

Login At "2020-03-01 00:11:23" 
Logout AT "2020-03-02 00:01:00"

样本输出

Date               Hour            Login Hours
2020-03-01          0                  0.82
2020-03-01          1                   1
2020-03-01          2                   1
2020-03-01          3                   1
2020-03-01          4                   1
2020-03-01          5                   1
2020-03-01          6                   1
2020-03-01          7                   1
2020-03-01          8                   1
2020-03-01          9                   1
2020-03-01          10                  1
2020-03-01          11                  1
2020-03-01          12                  1
2020-03-01          13                  1
2020-03-01          14                  1
2020-03-01          15                  1
2020-03-01          16                  1
2020-03-01          17                  1
2020-03-01          18                  1
2020-03-01          19                  1
2020-03-01          20                  1
2020-03-01          21                  1
2020-03-01          22                  1
2020-03-01          23                  1
2020-03-02          0                   0.02

标签： mysqlsqldatetableau-apirecursive-query

Answer 1

如果您正在运行 MySQL 8.0，则可以使用递归查询来执行此操作。逻辑是在日期范围内的整个小时内迭代，并login_hours在每一步计算 ，使用timestampdiff()：

with recursive cte as (
    select 
        date_format(login_at, '%Y-%m-%d %H:00:00') login_at,
        logout_at,
        timestampdiff(
            second,
            login_at,
            date_format(login_at, '%Y-%m-%d %H:00:00') + interval 1 hour
        ) / 60 / 60 login_hours
    from mytable
    union all
    select 
        login_at + interval 1 hour,
        logout_at,
        timestampdiff(
            second,
            login_at + interval 1 hour,
            least(logout_at, login_at + interval 2 hour)
        ) / 60 / 60
    from cte
    where login_at + interval 1 hour < logout_at
)
select date(login_at) date, hour(login_at) hour, round(login_hours, 2) login_hours
from cte
order by login_at

DB Fiddle 上的演示：

原始数据集：

登录名 | 注销_at          
:----------------- | :-----------------
2020-03-01 00:11:23 | 2020-03-02 00:01:00

查询结果：

日期 | 小时 | login_hours
:--------- | ---: | ----------:
2020-03-01 | 0 | 0.81
2020-03-01 | 1 | 1.00
2020-03-01 | 2 | 1.00
2020-03-01 | 3 | 1.00
2020-03-01 | 4 | 1.00
2020-03-01 | 5 | 1.00
2020-03-01 | 6 | 1.00
2020-03-01 | 7 | 1.00
2020-03-01 | 8 | 1.00
2020-03-01 | 9 | 1.00
2020-03-01 | 10 | 1.00
2020-03-01 | 11 | 1.00
2020-03-01 | 12 | 1.00
2020-03-01 | 13 | 1.00
2020-03-01 | 14 | 1.00
2020-03-01 | 15 | 1.00
2020-03-01 | 16 | 1.00
2020-03-01 | 17 | 1.00
2020-03-01 | 18 | 1.00
2020-03-01 | 19 | 1.00
2020-03-01 | 20 | 1.00
2020-03-01 | 21 | 1.00
2020-03-01 | 22 | 1.00
2020-03-01 | 23 | 1.00
2020-03-02 | 0 | 0.02