日期列表+两个日期之间的小时数 [英] List of Date + Hours between two dates

问题描述

这给了我MM/DD/YYYY:

This gives me MM/DD/YYYY:

SELECT TRUNC (to_date('01/02/2018', 'MM/DD/YYYY') - ROWNUM + 1) dt FROM DUAL CONNECT BY ROWNUM <= ( TO_DATE('01/02/2018', 'mm/dd/yyyy') - to_date('12/31/2017', 'MM/DD/YYYY')) + 1 order by 1

我想要这种格式的输出

12/31 10 PM
12/31 11 PM
01/01 12 AM
01/02 1 AM
...

更新

这提供了动态日期范围的时间表(无论有没有数据，细分为每个小时)，报告的高层管理人员希望查看该时间表.但是，由于篇幅所限，显示4位数字的年是浪费的.这不是益智游戏.

Update

This provides a timeline for a dynamic date range (break-down into every hour regardless there is data) report upper management wants to see. However, due to space constraint, showing 4-digit year is a waste. This is not a puzzle game.

推荐答案

尝试一下，

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD HH AM';

SELECT to_date('12/31/2017', 'MM/DD/YYYY') + (FLOOR(rownum/24) + (MOD(rownum-1, 24)/24)) dt
 FROM DUAL 
 CONNECT BY rownum <= ((to_date('01/02/2018', 'MM/DD/YYYY')+(1/23)) - to_date('12/31/2017', 'MM/DD/YYYY') + (MOD(rownum, 24)/24)) * 24
ORDER BY 1;

输出:

DT        
-----------
12/31 12 AM
12/31 01 AM
12/31 02 AM
12/31 03 AM
12/31 04 AM
12/31 05 AM
12/31 06 AM
12/31 07 AM
12/31 08 AM
12/31 09 AM
12/31 10 AM
12/31 11 AM
12/31 12 PM
12/31 01 PM
12/31 02 PM
12/31 03 PM
12/31 04 PM
12/31 05 PM
12/31 06 PM
12/31 07 PM
12/31 08 PM
12/31 09 PM
12/31 10 PM
01/01 12 AM
01/01 01 AM
01/01 02 AM
01/01 03 AM
01/01 04 AM
01/01 05 AM
01/01 06 AM
01/01 07 AM
01/01 08 AM
01/01 09 AM
01/01 10 AM
01/01 11 AM
01/01 12 PM
01/01 01 PM
01/01 02 PM
01/01 03 PM
01/01 04 PM
01/01 05 PM
01/01 06 PM
01/01 07 PM
01/01 08 PM
01/01 09 PM
01/01 10 PM
01/01 11 PM
01/02 12 AM
01/02 01 AM
01/02 02 AM
01/02 03 AM
01/02 04 AM
01/02 05 AM
01/02 06 AM
01/02 07 AM
01/02 08 AM
01/02 09 AM
01/02 10 AM
01/02 11 AM
01/02 12 PM
01/02 01 PM
01/02 02 PM
01/02 03 PM
01/02 04 PM
01/02 05 PM
01/02 06 PM
01/02 07 PM
01/02 08 PM
01/02 09 PM
01/02 10 PM
01/02 11 PM

已选择71行

如果要指定开始时间和结束时间，请在下面的查询中使用，我刚刚修改了第一个查询的CONNECT BY和ORDER BY子句.

If you want to specify the start hour and end hour, use query below, I just modified the CONNECT BY and ORDER BY clause of my first query.

SELECT to_date('12/31/2017 10:00 PM', 'MM/DD/YYYY HH:MI AM') + (FLOOR(rownum/24) + (MOD(rownum-1, 24)/24)) dt
   FROM DUAL 
CONNECT BY rownum <= CEIL(((to_date('01/01/2018 02:00 AM', 'MM/DD/YYYY HH:MI AM') - to_date('12/31/2017 10:00 PM', 'MM/DD/YYYY HH:MI AM')) * 24))+1
  ORDER BY rownum;

输出

DT        
-----------
12/31 10 PM
12/31 11 PM
01/01 12 AM
01/01 01 AM
01/01 02 AM

这篇关于日期列表+两个日期之间的小时数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！