使用SQL生成日期范围

Question

我有一个SQL查询，其中包含一个日期参数（如果我将其放入函数中），我需要在过去一年的每一天运行它。

如何生成最近365天的列表，以便我可以使用原始的SQL来完成此操作？

显然，生成0..364的列表也可以工作，因为我总是可以：

SELECT SYSDATE - val FROM (...);

- George Mauer

---

别忘了，不是每一年都有365天哦 :P - TravisO

真的，但考虑到这只运行一年一次，我认为他们可以自己进行调整。 - George Mauer

直到下一个闰年他们忘记在运行之前调整流程... - Tom H

从Oracle 11.2开始，可以使用递归子查询因式分解（又称递归WITH），请参见此处的示例：https://dev59.com/3bH3oIgBc1ULPQZFKGXG#70821671。 - Marmite Bomber

Answer 1

没有必要使用额外大的表或 ALL_OBJECTS 表：

SELECT TRUNC (SYSDATE - ROWNUM) dt
  FROM DUAL CONNECT BY ROWNUM < 366

这样就可以解决问题。

- user34850

---

非常感谢！我能够使用这种思路来获取sysdate +/- 30之间的所有日期。供其他人参考，问题和适用答案位于https://dev59.com/emDVa4cB1Zd3GeqPaB67。 - SeanKilleen

Answer 2

最近我遇到了类似的问题，并使用以下简单的查询解决了它：

SELECT
  (to_date(:p_to_date,'DD-MM-YYYY') - level + 1) AS day
FROM
  dual
CONNECT BY LEVEL <= (to_date(:p_to_date,'DD-MM-YYYY') - to_date(:p_from_date,'DD-MM-YYYY') + 1);

例子

SELECT
  (to_date('01-05-2015','DD-MM-YYYY') - level + 1) AS day
FROM
  dual
CONNECT BY LEVEL <= (to_date('01-05-2015','DD-MM-YYYY') - to_date('01-04-2015','DD-MM-YYYY') + 1);

结果

01-05-2015 00:00:00
30-04-2015 00:00:00
29-04-2015 00:00:00
28-04-2015 00:00:00
27-04-2015 00:00:00
26-04-2015 00:00:00
25-04-2015 00:00:00
24-04-2015 00:00:00
23-04-2015 00:00:00
22-04-2015 00:00:00
21-04-2015 00:00:00
20-04-2015 00:00:00
19-04-2015 00:00:00
18-04-2015 00:00:00
17-04-2015 00:00:00
16-04-2015 00:00:00
15-04-2015 00:00:00
14-04-2015 00:00:00
13-04-2015 00:00:00
12-04-2015 00:00:00
11-04-2015 00:00:00
10-04-2015 00:00:00
09-04-2015 00:00:00
08-04-2015 00:00:00
07-04-2015 00:00:00
06-04-2015 00:00:00
05-04-2015 00:00:00
04-04-2015 00:00:00
03-04-2015 00:00:00
02-04-2015 00:00:00
01-04-2015 00:00:00

- AndrewMcCoist

Answer 3

 SELECT (sysdate-365 + (LEVEL -1)) AS DATES
 FROM DUAL connect by level <=( sysdate-(sysdate-365))

如果将sysdate和sysdate-365替换为“from”和“to”日期，则输出结果将是从“from”到“to”日期之间的一系列日期。

- user853322

---

我喜欢你不使用rownum，在子查询中可能会有问题。 - towi

Answer 4

这是Oracle特有的功能，并且不依赖于预先存在的大型表或复杂的系统视图，也不依赖于数据字典对象。

SELECT c1 from dual
  MODEL DIMENSION BY (1 as rn)  MEASURES (sysdate as c1)
  RULES ITERATE (365) 
  (c1[ITERATION_NUMBER]=SYSDATE-ITERATION_NUMBER)
order by 1

- Gary Myers

Answer 5

Oracle中经常使用的一种方法是这样的：

select trunc(sysdate)-rn
from
(   select rownum rn
    from   dual
    connect by level <= 365)
/

就我个人而言，如果一个应用程序需要一系列日期，则我会创建一个包含这些日期的表格，或者创建一个包含一百万个整数序列的表格，可用于此类事情。

- David Aldridge

Answer 6

日期范围为1996年12月31日至2020年12月31日

SELECT dt, to_char(dt, 'MM/DD/YYYY') as date_name, 
  EXTRACT(year from dt) as year, 
  EXTRACT(year from fiscal_dt) as fiscal_year,
  initcap(to_char(dt, 'MON')) as month,
  to_char(dt, 'YYYY')        || ' ' || initcap(to_char(dt, 'MON')) as year_month,
  to_char(fiscal_dt, 'YYYY') || ' ' || initcap(to_char(dt, 'MON')) as fiscal_year_month,
  EXTRACT(year from dt)*100        + EXTRACT(month from dt) as year_month_id,
  EXTRACT(year from fiscal_dt)*100 + EXTRACT(month from fiscal_dt) as fiscal_year_month_id,
  to_char(dt, 'YYYY')        || ' Q' || to_char(dt, 'Q') as quarter,
  to_char(fiscal_dt, 'YYYY') || ' Q' || to_char(fiscal_dt, 'Q') as fiscal_quarter
  --, EXTRACT(day from dt) as day_of_month, to_char(dt, 'YYYY-WW') as week_of_year, to_char(dt, 'D') as day_of_week
  FROM (
    SELECT dt, add_months(dt, 6) as fiscal_dt --starts July 1st
    FROM (
      SELECT TO_DATE('12/31/1996', 'mm/dd/yyyy') + ROWNUM as dt 
      FROM DUAL CONNECT BY ROWNUM < 366 * 30 --30 years
    )
    WHERE dt <= TO_DATE('12/31/2020', 'mm/dd/yyyy')
  )

- Igor Krupitsky

Answer 7

半年前的一周。

SELECT (date'2015-08-03' + (LEVEL-1)) AS DATES
 FROM DUAL 
 where ROWNUM < 8
 connect by level <= (sysdate-date'2015-08-03'); 

如果省略 ROWNUM，则只会获取50行数据，与值无关。

- Victor H

Answer 8

大约一年半的时间太晚了，但为了纪念在此提供Teradata版本：

SELECT calendar_date 
FROM SYS_CALENDAR.Calendar
WHERE SYS_CALENDAR.Calendar.calendar_date between '2010-01-01' (date) and '2010-01-03' (date)

- Chris

Answer 9

啊哈哈哈，我刚想出了一个有趣的方法来做这件事：

select SYSDATE - ROWNUM
from shipment_weights sw
where ROWNUM < 365;

where shipment_weights 是任何大型表；

- George Mauer

Answer 10

我有同样的需求 - 我只是使用了这个。用户输入他/她想要将日历范围限制到的天数。

  SELECT DAY, offset
    FROM (SELECT to_char(SYSDATE, 'DD-MON-YYYY') AS DAY, 0 AS offset
            FROM DUAL
          UNION ALL
          SELECT to_char(SYSDATE - rownum, 'DD-MON-YYYY'), rownum
            FROM all_objects d)
            where offset <= &No_of_days

我将上述结果集作为驱动视图，在涉及具有日期的表的其他视图中使用LEFT OUTER JOIN。

- Suddha