如何编写这个SQL (关于日期和时间)？

Question

spot_datetime

2019-04-10  17:36:00
2019-04-05  17:22:00
2019-04-03  18:52:00
2019-04-09  18:12:00
2019-04-05  18:14:00
2019-04-12  16:23:00
2019-04-09  18:43:00
2019-04-16  18:35:00
2019-04-11  16:53:00
2019-04-25  00:57:00
2019-04-09  00:25:00
2019-04-21  00:59:00
2019-04-24  01:04:00
2019-02-25  00:59:00
2019-04-09  02:41:00
2019-04-17  03:28:00
2019-02-24  02:55:00
2019-02-24  03:26:00
2019-02-25  02:47:00
2019-04-16  23:27:00
2019-04-23  21:01:00

..。

广播日期从东部时间上午6:00:00开始，到东部时间上午5:59:59结束。因此，如果一个广告在美国东部时间6月3日早上7:00:00播出，那么该广告的播出日期就是6月3日。但是如果一个广告在美国东部时间6月3日早上5:00:00播出，那么该广告的播出日期是6月2日。请在您的查询中包含一个计算字段，以提供每个广告位的广播日期。

我试着用CASE WHEN function、CAST和DATE_FORMAT、DATE_ADD来编写sql，通过比较spot_datetime来识别广播日期，但是不起作用。

select spot_datetime,
case when date_format(spot_datetime, '%h:%i:%s') < cast(06:00:00 as time)
then ...
eles date_add(spot_datetime, interval 1 day) end as broadcast_date
from TABLE_A;

显示每个位置的广播日期的新列。

Answer 1

为什么不从日期时间中减去6个小时，然后截断时间部分，例如

SELECT r.spot_datetime
     , DATE( r.spot_datetime + INTERVAL -6 HOUR ) AS bcast_date
  FROM ( SELECT '2019-06-03 05:00' AS spot_datetime
         UNION ALL SELECT '2019-06-03 06:00'
         UNION ALL SELECT '2019-06-03 07:00'
       ) r

返回

spot_datetime     bcast_date
----------------  ----------
2019-06-03 05:00  2019-06-02
2019-06-03 06:00  2019-06-03
2019-06-03 07:00  2019-06-03

(这假设不需要时区转换，即广播日期边界标记为与spot_datetime相同的时区。)

在春季和秋季，我们应该有夏令时周日更改的测试用例：

spot_datetime     bcast_date
----------------  ----------

2019-03-10 05:00  2019-03-09
2019-03-10 05:30  2019-03-09
2019-03-10 06:00  2019-03-10

2019-11-03 05:00  2019-11-02
2019-11-03 05:30  2019-11-02
2019-11-03 06:00  2019-11-03

但是，如果我们要使用DATE_FORMAT，请注意'%h'格式是12小时时钟(01..12)，这既适用于AM，也适用于PM，因此我们确实需要添加一个AM/PM鉴别器。或者我们可以使用'%H'格式的24小时时钟模型(00..23)

例如：

DATE(spot_datetime) + INTERVAL IF(DATE_FORMAT(spot_datetime,'%H') < '06',-1,0) DAY