时间戳

首页 > 解决方案 > MySQL为每日报告创建自定义日期范围

mysql - MySQL为每日报告创建自定义日期范围

问题描述

mysql 8.0

我有看起来像这样的数据集：

user_id        transaction_time      item_price
   1          2020-01-01 13:00:00       100 
   1          2020-01-01 18:00:00       100 
   1          2020-01-01 19:00:00       100 
   1          2020-01-01 23:00:00       100 
   1          2020-01-02 04:00:00       100 
   1          2020-01-02 09:00:00       100

通常，如果我想找到每日价格的总和（价格），我会这样做

select date(transcation_time) as dt
     , sum(item_price) as sum_price
from table
group by date(transaction_time)

这将输出

   dt       sum_price
2020-01-01     400
2020-01-02     200

但是，我不想将价格从到2020-01-01求和，而是将价格从到的总和标记为2020-01-01 00:00:002020-01-01 23:59:592020-01-01 18:00:002020-01-02 06:00:002020-01-01

所以

2020-01-01   sums prices from 2020-01-01 18:00:00 ~ 2020-01-02 06:00:00 
2020-01-02   sums prices from 2020-01-02 18:00:00 ~ 2020-01-03 06:00:00 
2020-01-03   sums prices from 2020-01-03 18:00:00 ~ 2020-01-04 06:00:00

等等...

那么我得到的聚合将如下所示：

   dt       sum_price
2020-01-01     500
2020-01-02     100

请注意，第一行没有添加，因为 13:00:00 不在 18:00:00 ~06:00:00

我怎样才能做到这一点？

标签： mysqlsql

解决方案

我认为您只想减去 6 小时 - 或添加 18 小时：

select date(transaction_time + interval -6 hour) as dt,
       sum(item_price) as sum_price
from table
group by dt;

如果你真的只想要 12 小时，那么添加一个where子句：

select date(transaction_time + interval -6 hour) as dt,
       sum(item_price) as sum_price
from table
where time(transaction_time) >= '18:00:00' or
      time(transaction_time) < '06:00:00' 
group by dt

推荐阅读