将具有某一天时间的所有时间戳更改为上一个日期

Question

我正在尝试将时间戳的时间为05:59:59 AM或以下的所有数据行转换为上一个日期。因此，例如“2018-12-08 05:05:00”将是“2018-12-07”，“2018-11-06 03:02:00”将是“2018-11-05”。

时间戳的格式为“yyyy-mm-dd hh：mm：ss”，并存储为varchar

SELECT lift.spot_datetime AS broadcastdate,
    CASE when broadcastdate > to_date(>='06:00:00', 'hh:mm:ss') then to_date('MM/DD/YYYY') end as same_date,
    ELSE when broadcastdate > to_date(<='05:59:59', 'hh:mm:ss') then DATE_SUB(to_date('MM/DD/YYYY' - 1) end as subtract_date

“从AWS Athena客户端引发错误。syntax_error:第1:17行：列'day'无法解析”

运行上述查询时得到的当前错误如下：“An error has been throw from The AWS Athena client.line 2:39：no viable alternative at input'to_date(>='[Execution ID not available]”

Answer 1

从您的SQL查询中，并不清楚您想要什么样的结果结构，但让我来试试看：

SELECT
    spot_datetime AS broadcastdate,
    IF ( TIME(spot_datetime) < '06:00:00',
        DATE(spot_datetime) - INTERVAL 1 DAY,
        DATE(spot_datetime)
    ) AS your_desired_date
FROM lift;

+---------------------+-------------------+
| broadcastdate       | your_desired_date |
+---------------------+-------------------+
| 2018-12-08 05:05:00 | 2018-12-07        |
| 2018-11-06 03:02:00 | 2018-11-05        |
| 2018-11-06 06:00:00 | 2018-11-06        |
+---------------------+-------------------+
3 rows in set (0.00 sec)