Ответ 1
Нет необходимости использовать дополнительные большие таблицы или таблицу ALL_OBJECTS:
SELECT TRUNC (SYSDATE - ROWNUM) dt
FROM DUAL CONNECT BY ROWNUM < 366
сделает трюк.
Создание диапазона дат с использованием SQL
У меня есть SQL-запрос, который принимает параметр даты (если я должен был его бросить в функцию), и мне нужно запускать его каждый день прошлого года.
Как сгенерировать список из последних 365 дней, поэтому я могу использовать прямой SQL для этого?
Очевидно, что создание списка 0..364 тоже будет работать, поскольку я всегда мог:
SELECT SYSDATE - val FROM (...);
Ответы
Ответ 2
SELECT (sysdate-365 + (LEVEL -1)) AS DATES
FROM DUAL connect by level <=( sysdate-(sysdate-365))
Если вместо "sysdate" и "sysdate-365" вместо "s" и "sysdate-365" заменить "from" и "to" date, будет отображаться диапазон дат между датами и датами.
Ответ 3
В последнее время у меня была аналогичная проблема и она была решена с помощью этого простого запроса:
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
Ответ 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
Ответ 5
Метод, довольно часто используемый в Oracle, выглядит примерно так:
select trunc(sysdate)-rn
from
( select rownum rn
from dual
connect by level <= 365)
/
Лично, если у приложения есть потребность в списке дат, я просто создаю таблицу с ними или создаю таблицу с целым числом до чего-то смешного, как миллион, который можно использовать для этого типа вещи.
Ответ 6
Примерно через полтора года, но для потомков здесь есть версия для Teradata:
SELECT calendar_date
FROM SYS_CALENDAR.Calendar
WHERE SYS_CALENDAR.Calendar.calendar_date between '2010-01-01' (date) and '2010-01-03' (date)
Ответ 7
Ахахаха, здесь забавный способ, который я только что придумал, чтобы сделать это:
select SYSDATE - ROWNUM
from shipment_weights sw
where ROWNUM < 365;
где shipment_weights - любая большая таблица;
Ответ 8
У меня было такое же требование - я просто использую это. Пользователь вводит количество дней, с помощью которых он/она хочет ограничить диапазон календаря.
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 с другими представлениями, включающими таблицы с датами.
Ответ 9
Неделя от 6 месяцев назад
SELECT (date'2015-08-03' + (LEVEL-1)) AS DATES
FROM DUAL
where ROWNUM < 8
connect by level <= (sysdate-date'2015-08-03');
если вы опускаете ROWNUM, вы получаете только 50 строк, независимо от значения.
Ответ 10
Диапазон дат между 12/31/1996 и 12/31/2020
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')
)
Ответ 11
Для удовольствия, вот какой код, который должен работать в SQL Server, Oracle или MySQL:
SELECT current_timestamp - CAST(d1.digit + d2.digit + d3.digit as int)
FROM
(
SELECT digit
FROM
(
select '1' as digit
union select '2'
union select '3'
union select '4'
union select '5'
union select '6'
union select '7'
union select '8'
union select '9'
union select '0'
) digits
) d1
CROSS JOIN
(
SELECT digit
FROM
(
select '1' as digit
union select '2'
union select '3'
union select '4'
union select '5'
union select '6'
union select '7'
union select '8'
union select '9'
union select '0'
) digits
) d2
CROSS JOIN
(
SELECT digit
FROM
(
select '1' as digit
union select '2'
union select '3'
union select '4'
union select '5'
union select '6'
union select '7'
union select '8'
union select '9'
union select '0'
) digits
) d3
WHERE CAST(d1.digit + d2.digit + d3.digit as int) < 365
ORDER BY d1.digit, d2.digit, d3.digit -- order not really needed here
Бонусные баллы, если вы можете дать мне кросс-платформенный синтаксис для повторного использования таблицы цифр.
Ответ 12
У меня нет ответа на повторное использование таблицы цифр, но вот пример кода, который будет работать, по крайней мере, на SQL-сервере и немного быстрее.
print("code sample");
select top 366 current_timestamp - row_number() over( order by l.A * r.A) as DateValue
from (
select 1 as A union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 union
select 11 union
select 12 union
select 13 union
select 14 union
select 15 union
select 16 union
select 17 union
select 18 union
select 19 union
select 20 union
select 21
) l
cross join (
select 1 as A union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 union
select 11 union
select 12 union
select 13 union
select 14 union
select 15 union
select 16 union
select 17 union
select 18
) r
print("code sample");
Ответ 13
Этот запрос генерирует список дат 4000 дней в будущем и 5000 в прошлом на сегодняшний день (вдохновленный http://blogs.x2line.com/al/articles/207.aspx)
SELECT * FROM (SELECT
(CONVERT(SMALLDATETIME, CONVERT(CHAR,GETDATE() ,103)) + 4000 -
n4.num * 1000 -
n3.num * 100 -
n2.num * 10 -
n1.num) AS Date,
year(CONVERT(SMALLDATETIME, CONVERT(CHAR,GETDATE() ,103)) + 4000 -
n4.num * 1000 -
n3.num * 100 -
n2.num * 10 -
n1.num) as Year,
month(CONVERT(SMALLDATETIME, CONVERT(CHAR,GETDATE() ,103)) + 4000 -
n4.num * 1000 -
n3.num * 100 -
n2.num * 10 -
n1.num) as Month,
day(CONVERT(SMALLDATETIME, CONVERT(CHAR,GETDATE() ,103)) + 4000 -
n4.num * 1000 -
n3.num * 100 -
n2.num * 10 -
n1.num) as Day
FROM (SELECT 0 AS num union ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) n1
,(SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) n2
,(SELECT 0 AS num union ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) n3
,(SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8) n4
) GenCalendar ORDER BY 1