使用oracle数据库查询早上8:30到第二天早上8:30的数据
SELECT
CASE
WHEN TO_CHAR( t.TXNDATE, 'HH24:MI') >= '08:30' THEN to_char(TRUNC( t.TXNDATE),'yyyy-mm-dd')
when TO_CHAR( t.TXNDATE + INTERVAL '1' DAY, 'HH24:MI') <= '08:30' THEN to_char(TRUNC( t.TXNDATE)-1,'yyyy-mm-dd')
end AS 日期,
t.FROMSPECNAME 工序,sum(t.FROMQTY) 数量,'AGV' AS ITEM
FROM interfaceuser.HISTORYMAINLINE_AGV t
WHERE t.TXNDATE >= to_date('2024-07-15 08:30:00','yyyy-mm-dd hh24:mi:ss')
and t.TXNDATE <= to_date('2024-07-22 08:30:00','yyyy-mm-dd hh24:mi:ss')
group by
CASE
WHEN TO_CHAR( t.TXNDATE, 'HH24:MI') >= '08:30' THEN to_char(TRUNC( t.TXNDATE),'yyyy-mm-dd')
when TO_CHAR( t.TXNDATE + INTERVAL '1' DAY, 'HH24:MI') <= '08:30' THEN to_char(TRUNC( t.TXNDATE)-1,'yyyy-mm-dd')
END ,
t.FROMSPECNAME