当时间在午夜到早上6点之间时，减去一天作为日期-Oracle

Question

我正在尝试做一个CASE WHEN语句，其中午夜之后到早上6点之前的时间戳的日期更改为前一天。我的公司将工作日定为早上6点到6点，所以我想滚动午夜到6点之间发生的任何事情，作为前一天的实例显示。每当试图运行这个程序时，它都会给我以下错误，但我不知道如何修复它。我试过去掉INTERVAL部分，去掉TRUNC日期，但我想不出什么是有效的。

ORA-30081: invalid data type for datetime/interval arithmetic
30081. 00000 -  "invalid data type for datetime/interval arithmetic"
*Cause:    The data types of the operands are not valid for datetime/interval
arithmetic.
*Action:
Error at Line: 2 Column: 104

查询：

SELECT 
CASE WHEN TO_CHAR(ADJUSTMENTTIME, 'HH24') BETWEEN 00 AND 06 THEN TO_CHAR(ADJUSTMENTTIME, 'YYYY-MM-DD') - INTERVAL '1' DAY
ELSE TO_CHAR(ADJUSTMENTTIME, 'YYYY-MM-DD')
END AS ADJ_DATE, 
CASE WHEN TO_CHAR(ADJUSTMENTTIME, 'HH24') BETWEEN 06 AND 17 THEN ' DAYS' 
ELSE ' NIGHTS'
END AS ADJUSTMENTHOUR, SUM(ADJUSTEDAMOUNT) AS ADJ_QTY
From
*DATASOURCE*
WHERE
DESTINATIONSTATUS = 'RIP' AND
ADJUSTMENTTIME BETWEEN '05-Aug-20 06.00.00.000000000 AM' AND '06-Aug-20 06.00.00.000000000 AM'
GROUP BY ADJUSTMENTTIME

Answer 1

从日期前减去6小时更简单：

select 
to_char(adjustmenttime - interval '6' hour, 'yyyy-mm-dd') as adj_date,
case when to_char(adjustmenttime, 'hh24') between '06' and '17'
then 'days'
else 'nights'
end as adj_hour,
sum(adjustmentamount) adj_amount
from datasource
where 
destinationstatus = 'RIP'
and adjustmenttime >= timestamp '2020-08-05 06:00:00'
and adjustmenttime < timestamp '2020-08-06 06:00:00'
group by 1, 2