17,140
社区成员




求助大神们。
现在我有个包含着日期值的数据,YYYY-MM-DD HH:24:MI:SS , 每条数据间隔一分钟
如,2024-04-01 00:00:00, 2024-04-01 00:01:00, 2024-04-01 00:02:00 ............2024-04-01 23:59:00, 2024-04-02 00:00:00 ..... 2024-04-10 23:59:00
我需要的结果是 3种情形,A. 按整点,B. 10分钟间隔 C. 30分钟间隔
A情形是:2024-04-01 00:00:00, 2024-04-01 01:00:00, 2024-04-01 02:00:00 ~ 2024-04-10 23:00:00
B情形是:2024-04-01 00:00:00, 2024-04-01 00:10:00, 2024-04-01 00:20:00 ~ 2024-04-10 23:50:00
C情形是:2024-04-01 00:00:00, 2024-04-01 00:30:00, 2024-04-01 01:00:00 ~ 2024-04-10 23:30:00
请问各情形的query应该怎么写呢?在线等 ~~~
试试 SELECT TRUNC(your_date_column, 'HH') AS hour_start, COUNT(*) AS total_records FROM your_table WHERE your_date_column >= TRUNC(SYSDATE, 'DD') GROUP BY TRUNC(your_date_column, 'HH') ORDER BY hour_start;
查询整点:
SELECT *
FROM your_table
WHERE DATE_FORMAT(your_column, '%Y-%m-%d %H:00:00') = your_column;
查询整点半:依次类推
SELECT *
FROM your_table
WHERE DATE_FORMAT(your_column, '%Y-%m-%d %H:30:00') = your_column;