mysql - 该表有两列，一列是日期，另一列是小时，我想比较 '2019-10-23 00' 和 '2019-10-26 12' 之间的日期，该怎么办？

Question

select date_format( date, '%Y-%m-%d' ) as date,
            substring(`hour`, 1, 2) as hour,
            sum(revenue) as revenue
        from dsp_platform_country_os_hourly_report as dr
        where concat(substring(dr.`date`, 1, 10), ' ', dr.`hour`)
                between CAST('2019-10-26 0' AS DATE) AND CAST('2019-10-26 15' AS DATE)
        group by date, hour order by dr.date, dr.hour

但是sql无法得到正确的答案。

Answer 1

要将 TIME 数据类型值添加到 DATE 数据类型值，我们可以使用如下表达式:

    dr.date + INTERVAL TIME_TO_SEC( dr.hour ) SECOND

结果将是DATETIME数据类型的值。

请注意，TIME 数据类型支持超过 24 小时的值(最多八百多个小时)，因此在一般情况下，我们应该预料到这一点。例如，TIME 值，例如“284:40:35”。

考虑到超过 100 小时和少于 10 小时的时间值，例如，获取 TIME 值的前两个字符的子字符串将会出现问题。 7:11:00。我们可以使用 HOUR 函数来提取小时，或者如果我们要进行字符串操作，我们可以使用 SUBSTRING_INDEX 函数和 : 作为分隔符。

像这样的查询可能满足规范:

SELECT DATE_FORMAT( dr.date ,'%Y-%m-%d') AS date
     , TIME_FORMAT( dr.hour ,'%H'      ) AS hour
     , SUM(dr.revenue)                   AS revenue
  FROM dsp_platform_country_os_hourly_report dr 

 WHERE dr.date + INTERVAL TIME_TO_SEC( dr.hour ) SECOND 

       >= '2019-10-26' + INTERVAL 0 HOUR 

   AND dr.date + INTERVAL TIME_TO_SEC( dr.hour ) SECOND 

       <  '2019-10-26' + INTERVAL 16 HOUR 

 GROUP
    BY date
     , hour 
 ORDER
    BY date
     , hour

<小时/>

或者，我们可以在文字中一起指定日期和时间；使用 + INTERVAL 将确保字符串文字转换为 DATETIME 数据类型。

       >= '2019-10-26 00:00' + INTERVAL 0 DAY

       <  '2019-10-26 16:00' + INTERVAL 0 DAY

对于较大的时间范围(跨越一天以上)，我们可能需要考虑如何处理导致相同 DATETIME< 的 date 和 hour 值值

      '2019-10-26' + INTERVAL 30  HOURS 
      '2019-10-27' + INTERVAL 6   HOURS 

我们是否希望具有这些日期和小时值的行被视为位于同一个存储桶中，或者它们位于两个单独的存储桶中(不同的日期值具有不同的小时值)

一般来说，我们倾向于通过在数据库中存储 DATETIME 值(小时部分限制为小于 24)来避免此类问题，而不是存储单独的 DATE code> 和 TIME 列。

引用文献:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_time-format

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_time-to-sec

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_hour