Генерировать дни из диапазона дат
Я хотел бы запустить запрос типа
select ... as days where `date` is between '2010-01-20' and '2010-01-24'
И возвратите данные вроде:
days
----------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24
Ответы
Ответ 1
В этом решении не используются циклы, процедуры или временные таблицы. Подзапрос генерирует даты за последние 10 000 дней и может быть расширен, чтобы перейти так далеко назад или вперед, как вы пожелаете.
select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
from (select 0 as a 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) as a
cross join (select 0 as a 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) as b
cross join (select 0 as a 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) as c
cross join (select 0 as a 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) as d
) a
where a.Date between '2010-01-20' and '2010-01-24'
Выход:
Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20
Примечания о производительности
Тестирование здесь показывает, что производительность на удивление хорошая: приведенный выше запрос занимает 0,0009 сек.
Если мы расширим подзапрос для генерации ок. 100 000 чисел (и, следовательно, около 274 лет дат), это работает в 0,0458 сек.
Кстати, это очень переносимый метод, который работает с большинством баз данных с небольшими изменениями.
Пример SQL Fiddle, возвращающий 1000 дней
Ответ 2
Вот еще одна вариация, использующая представления:
CREATE VIEW digits AS
SELECT 0 AS digit 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;
CREATE VIEW numbers AS
SELECT
ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
FROM
digits as ones,
digits as tens,
digits as hundreds,
digits as thousands;
CREATE VIEW dates AS
SELECT
SUBDATE(CURRENT_DATE(), number) AS date
FROM
numbers;
И тогда вы можете просто сделать (посмотрите, насколько он элегантный):
SELECT
date
FROM
dates
WHERE
date BETWEEN '2010-01-20' AND '2010-01-24'
ORDER BY
date
Обновление
Стоит отметить, что вы сможете создавать только прошлые даты, начиная с текущей даты. Если вы хотите создать какой-либо диапазон дат (прошлый, будущий и промежуточный), вам придется использовать это представление вместо:
CREATE VIEW dates AS
SELECT
SUBDATE(CURRENT_DATE(), number) AS date
FROM
numbers
UNION ALL
SELECT
ADDDATE(CURRENT_DATE(), number + 1) AS date
FROM
numbers;
Ответ 3
Принятый ответ не работал для PostgreSQL (синтаксическая ошибка в или рядом с "a" ).
Как вы это делаете в PostgreSQL, используйте generate_series, т.е.:
SELECT day::date
FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day;
day
------------
2010-01-20
2010-01-21
2010-01-22
2010-01-23
2010-01-24
(5 rows)
Ответ 4
Используя рекурсивное выражение общей таблицы (CTE), вы можете сгенерировать список дат, а затем выбрать из него. Очевидно, вы обычно не хотели бы создавать три миллиона дат, поэтому это просто иллюстрирует возможности. Вы можете просто ограничить диапазон дат внутри CTE и опустить предложение where из оператора select с помощью CTE.
with [dates] as (
select convert(datetime, '1753-01-01') as [date] --start
union all
select dateadd(day, 1, [date])
from [dates]
where [date] < '9999-12-31' --end
)
select [date]
from [dates]
where [date] between '2013-01-01' and '2013-12-31'
option (maxrecursion 0)
В Microsoft SQL Server 2005 генерация списка CTE всех возможных дат заняла 1:08. Создание столетий заняло менее секунды.
Ответ 5
Запрос MSSQL
select datetable.Date
from (
select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date
from (select 0 as a 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) as a
cross join (select 0 as a 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) as b
cross join (select 0 as a 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) as c
) datetable
where datetable.Date between '2014-01-20' and '2014-01-24'
order by datetable.Date DESC
Выход
Date
-----
2014-01-23 12:35:25.250
2014-01-22 12:35:25.250
2014-01-21 12:35:25.250
2014-01-20 12:35:25.250
Ответ 6
Старое решение школы для этого без цикла/курсора заключается в создании таблицы NUMBERS, которая имеет один столбец Integer со значениями, начинающимися с 1.
CREATE TABLE `example`.`numbers` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Вам нужно заполнить таблицу достаточным количеством записей, чтобы удовлетворить ваши потребности:
INSERT INTO NUMBERS (id) VALUES (NULL);
Как только у вас есть таблица NUMBERS, вы можете использовать:
SELECT x.start_date + INTERVAL n.id-1 DAY
FROM NUMBERS n
JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date
FROM DUAL) x
WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'
Абсолютным низкотехнологичным решением будет:
SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
FROM DUAL
UNION ALL
SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
FROM DUAL
Для чего вы его используете?
Чтобы создать списки дат или цифр, чтобы ЛЕВАЯ ВСТУПЛЕНИЕ на. Вы должны были бы это сделать, чтобы увидеть, где имеются пробелы в данных, потому что вы LEFT JOINING в список секвенциальных данных - нулевые значения станут очевидными там, где существуют пробелы.
Ответ 7
Для Access 2010 - требуется несколько шагов; Я следовал той же схеме, что и выше, но думал, что смогу помочь кому-то в Access. Отлично работал у меня, мне не нужно было хранить посеянную таблицу дат.
Создайте таблицу с именем DUAL (подобно тому, как работает таблица Oracle DUAL)
- ID (AutoNumber)
- DummyColumn (текст)
- Добавить значения строк (1, "DummyRow" )
Создайте запрос с именем "ZeroThru9Q"; вручную введите следующий синтаксис:
SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL
SELECT 9
FROM dual;
Создайте запрос с именем "TodayMinus1KQ" (для дат до сегодняшнего дня); вручную введите следующий синтаксис:
SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
(SELECT *
FROM ZeroThru9Q) AS a,
(SELECT *
FROM ZeroThru9Q) AS b,
(SELECT *
FROM ZeroThru9Q) AS c
Создайте запрос с именем "TodayPlus1KQ" (для дат после сегодняшнего дня); вручную введите следующий синтаксис:
SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM
(SELECT *
FROM ZeroThru9Q) AS a,
(SELECT *
FROM ZeroThru9Q) AS b,
(SELECT *
FROM ZeroThru9Q) AS c;
Создайте объединенный запрос с именем "TodayPlusMinus1KQ" (для дат +/- 1000 дней):
SELECT MyDate
FROM TodayMinus1KQ
UNION
SELECT MyDate
FROM TodayPlus1KQ;
Теперь вы можете использовать запрос:
SELECT MyDate
FROM TodayPlusMinus1KQ
WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#
Ответ 8
Процедура + временная таблица:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `days`(IN dateStart DATE, IN dateEnd DATE)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS date_range (day DATE);
WHILE dateStart <= dateEnd DO
INSERT INTO date_range VALUES (dateStart);
SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
END WHILE;
SELECT * FROM date_range;
DROP TEMPORARY TABLE IF EXISTS date_range;
END
Ответ 9
спасибо Pentium10 - вы заставили меня присоединиться к stackoverflow:) -
это мой портирование на msaccess - думаю, что он будет работать в любой версии:
SELECT date_value
FROM (SELECT a.espr1+(10*b.espr1)+(100*c.espr1) AS integer_value,
dateadd("d",integer_value,dateserial([start_year], [start_month], [start_day])) as date_value
FROM (select * from
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as a,
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as b,
(
select top 1 "0" as espr1 from MSysObjects
union all
select top 1 "1" as espr2 from MSysObjects
union all
select top 1 "2" as espr3 from MSysObjects
union all
select top 1 "3" as espr4 from MSysObjects
union all
select top 1 "4" as espr5 from MSysObjects
union all
select top 1 "5" as espr6 from MSysObjects
union all
select top 1 "6" as espr7 from MSysObjects
union all
select top 1 "7" as espr8 from MSysObjects
union all
select top 1 "8" as espr9 from MSysObjects
union all
select top 1 "9" as espr9 from MSysObjects
) as c
) as d)
WHERE date_value
between dateserial([start_year], [start_month], [start_day])
and dateserial([end_year], [end_month], [end_day]);
ссылающиеся MSysObjects просто "заставляют доступ нуждаться в таблице countin" как минимум в 1 записи, в предложении from - любая таблица с по меньшей мере 1 записью будет делать.
Ответ 10
попробуйте это.
SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day
from DUAL
connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ;
Ответ 11
если вам понадобится больше двух дней, вам понадобится таблица.
Создать диапазон дат в mysql
тогда
select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;
Ответ 12
Создание дат между двумя полями даты
Если вам известен запрос SQL CTE, это решение поможет вам решить свой вопрос
Вот пример
У нас есть даты в одной таблице
Название таблицы: "testdate"
STARTDATE ENDDATE
10/24/2012 10/24/2012
10/27/2012 10/29/2012
10/30/2012 10/30/2012
Требовать результат:
STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012
Решение:
WITH CTE AS
(SELECT DISTINCT convert(varchar(10),StartTime, 101) AS StartTime,
datediff(dd,StartTime, endTime) AS diff
FROM dbo.testdate
UNION ALL SELECT StartTime,
diff - 1 AS diff
FROM CTE
WHERE diff<> 0)
SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime
FROM CTE
Объяснение: Обоснование рекурсивного запроса CTE
-
Первая часть запроса:
SELECT DISTINCT convert(varchar(10), StartTime, 101) AS StartTime, datediff(dd, StartTime, endTime) AS diff FROM dbo.testdate
Объяснение: firstcolumn - "startdate", второй столбец - разность начала и конца
дату в днях, и это будет рассматриваться как столбец "diff".
-
Вторая часть запроса:
UNION ALL SELECT StartTime, diff-1 AS diff FROM CTE WHERE diff<>0
Объяснение: Союз все наследует результат вышеуказанного запроса до тех пор, пока результат не станет нулевым,
Таким образом, результат "StartTime" наследуется от сгенерированного запроса CTE, а от diff, уменьшения - 1, поэтому он выглядит как 3, 2 и 1 до 0
Например
STARTDATE DIFF
10/24/2012 0
10/27/2012 0
10/27/2012 1
10/27/2012 2
10/30/2012 0
Спецификация результата
STARTDATE Specification
10/24/2012 --> From Record 1
10/27/2012 --> From Record 2
10/27/2012 --> From Record 2
10/27/2012 --> From Record 2
10/30/2012 --> From Record 3
-
Третья часть запроса
SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime FROM CTE
Он добавит день "diff" в "startdate", поэтому результат должен быть ниже
Результат
STARTDATE
10/24/2012
10/27/2012
10/28/2012
10/29/2012
10/30/2012
Ответ 13
Как указано (или, по крайней мере, намечено) во многих замечательных ответах, уже заданных, эта проблема легко решается после того, как у вас есть набор чисел для работы.
Примечание. Следующим является T-SQL, но это просто моя конкретная реализация общих понятий, уже упомянутых здесь и в Интернете в целом. Это должно быть относительно просто преобразовать код на ваш выбор.
Как? Рассмотрите этот запрос:
SELECT DATEADD(d, N, '0001-01-22')
FROM Numbers -- A table containing the numbers 0 through N
WHERE N <= 5;
Вышеприведенный диапазон дат 1/22/0001 - 1/27/0001 и чрезвычайно тривиален. В приведенном выше запросе есть две ключевые части информации: дата начала 0001-01-22 и смещение 5. Если мы объединим эти две части информации, мы, очевидно, имеем дату окончания. Таким образом, учитывая две даты, генерация диапазона может быть разбита следующим образом:
-
Найдите разницу между двумя заданными датами (смещение), просто:
-- Returns 125
SELECT ABS(DATEDIFF(d, '2014-08-22', '2014-12-25'))
Использование ABS() здесь гарантирует, что порядок дат не имеет значения.
-
Создайте ограниченный набор чисел, также легко:
-- Returns the numbers 0-2
SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
FROM(SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A')
Обратите внимание, что нам действительно не важно, что мы выбираем FROM здесь. Нам просто нужен набор для работы, чтобы мы подсчитали количество строк в нем. Я лично использую TVF, некоторые используют CTE, другие используют таблицу чисел, вы получаете идею. Я выступаю за использование наиболее эффективного решения, которое вы также понимаете.
Объединение этих двух методов позволит решить нашу проблему:
DECLARE @date1 DATE = '9001-11-21';
DECLARE @date2 DATE = '9001-11-23';
SELECT D = DATEADD(d, N, @date1)
FROM (
SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
FROM (SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A') S
) Numbers
WHERE N <= ABS(DATEDIFF(d, @date1, @date2));
Приведенный выше пример - ужасный код, но демонстрирует, как все объединяется.
Больше удовольствия
Мне нужно много чего делать, поэтому я включил логику в два TVF. Первый генерирует ряд чисел, а второй использует эту функцию для создания диапазона дат. Математика заключается в том, чтобы гарантировать, что порядок ввода не имеет значения, и потому, что я хотел использовать полный диапазон чисел, доступных в GenerateRangeSmallInt.
Следующая функция занимает ~ 16 мс процессорного времени, чтобы вернуть максимальный диапазон в 65536 дат.
CREATE FUNCTION dbo.GenerateRangeDate (
@date1 DATE,
@date2 DATE
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
SELECT D = DATEADD(d, N + 32768, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
FROM dbo.GenerateRangeSmallInt(-32768, ABS(DATEDIFF(d, @date1, @date2)) - 32768)
);
GO
CREATE FUNCTION dbo.GenerateRangeSmallInt (
@num1 SMALLINT = -32768
, @num2 SMALLINT = 32767
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
WITH Numbers(N) AS (
SELECT N FROM(VALUES
(1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
, (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
) V (N)
)
SELECT TOP(ABS(CAST(@num1 AS INT) - CAST(@num2 AS INT)) + 1)
N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
FROM Numbers A
, Numbers B
);
Ответ 14
Короче принятого ответа, та же идея:
(SELECT TRIM('2016-01-05' + INTERVAL a + b DAY) date
FROM
(SELECT 0 a UNION SELECT 1 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 ) d,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20
UNION SELECT 30 UNION SELECT 40) m
WHERE '2016-01-05' + INTERVAL a + b DAY <= '2016-01-21')
Ответ 15
Для тех, кто хочет это как сохраненное представление (MySQL не поддерживает вложенные команды выбора в представлениях):
create view zero_to_nine as
select 0 as n 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;
create view date_range as
select curdate() - INTERVAL (a.n + (10 * b.n) + (100 * c.n)) DAY as date
from zero_to_nine as a
cross join zero_to_nine as b
cross join zero_to_nine as c;
Затем вы можете сделать
select * from date_range
чтобы получить
date
---
2017-06-06
2017-06-05
2017-06-04
2017-06-03
2017-06-02
...
Ответ 16
Хорошая идея с генерацией этих дат на лету. Однако я не чувствую себя комфортно делать это с довольно большим диапазоном, поэтому я нашел следующее решение:
- Создана таблица "DatesNumbers", в которой будут храниться числа, используемые для расчета дат:
CREATE TABLE DatesNumbers (
i MEDIUMINT NOT NULL,
PRIMARY KEY (i)
)
COMMENT='Used by Dates view'
;
- Заполните таблицу, используя вышеуказанные методы, с числами от -59999 до 40000. Этот диапазон даст мне даты от 59999 дней (~ 164 года) до 40000 дней (109 лет) вперед:
INSERT INTO DatesNumbers
SELECT
a.i + (10 * b.i) + (100 * c.i) + (1000 * d.i) + (10000 * e.i) - 59999 AS i
FROM
(SELECT 0 AS i 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) AS a,
(SELECT 0 AS i 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) AS b,
(SELECT 0 AS i 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) AS c,
(SELECT 0 AS i 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) AS d,
(SELECT 0 AS i 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) AS e
;
- Создано представление "Даты":
SELECT
i,
CURRENT_DATE() + INTERVAL i DAY AS Date
FROM
DatesNumbers
Вот оно.
- (+) Легко читаемые запросы
- (+) Нет на лету чисел поколений
- (+) Дает даты в прошлом и будущем, и в этом нет никакого СОЮЗА, как в этом посте.
- (+) Даты "Только в прошлом" или "Только в будущем" можно отфильтровать с помощью
WHERE i < 0 или WHERE i > 0 (PK)
- (-) "временная" таблица & представление используется
Ответ 17
Вы хотели бы получить диапазон дат.
В вашем примере вы хотели бы получить даты между '2010-01-20' и '2010-01-24'
возможное решение:
select date_add('2010-01-20', interval row day) from
(
SELECT @row := @row + 1 as row FROM
(select 0 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 6 union all select 7 union all select 8 union all select 9) t,
(select 0 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 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 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 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 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 6 union all select 7 union all select 8 union all select 9) t4,
(SELECT @row:=-1) r
) sequence
where date_add('2010-01-20', interval row day) <= '2010-01-24'
объяснение
MySQL имеет функцию date_add, так
select date_add('2010-01-20', interval 1 day)
дам тебе
2010-01-21
Функция datediff позволит вам часто знать, что вам придется повторить это
select datediff('2010-01-24', '2010-01-20')
который возвращается
4
Получение списка дат в диапазоне дат сводится к созданию последовательности целых чисел, см. Генерацию последовательности целых чисел в MySQL.
В качестве наиболее одобренного ответа здесь был взят подход, аналогичный fooobar.com/questions/134269/...:
SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(SELECT @row:=0) r
limit 4
что приведет к
row
1.0
2.0
3.0
4.0
Строки теперь можно использовать для создания списка дат с заданной начальной датой. Чтобы включить дату начала, мы начинаем со строки -1;
select date_add('2010-01-20', interval row day) from
(
SELECT @row := @row + 1 as row FROM
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(SELECT @row:=-1) r
) sequence
where date_add('2010-01-20', interval row day) <= '2010-01-24'
Ответ 18
Хорошо.. Попробуйте следующее:
http://www.devshed.com/c/a/MySQL/Delving-Deeper-into-MySQL-50/
http://dev.mysql.com/doc/refman/5.0/en/loop-statement.html
http://www.roseindia.net/sql/mysql-example/mysql-loop.shtml
Используйте это, чтобы, скажем, создать временную таблицу, а затем сделать select * в таблице temp. Или выводить результаты по одному.
То, что вы говорите, что вы хотите сделать, не может быть сделано с помощью инструкции SELECT, но это может быть выполнимо с вещами, специфичными для MySQL.
Опять же, возможно, вам нужны курсоры: http://dev.mysql.com/doc/refman/5.0/en/cursors.html
Ответ 19
Для Oracle мое решение:
select trunc(sysdate-dayincrement, 'DD')
from dual, (select level as dayincrement
from dual connect by level <= 30)
Sysdate можно изменить на определенную дату, а номер уровня можно изменить, чтобы указать больше дат.
Ответ 20
если вам нужен список дат между двумя датами:
create table #dates ([date] smalldatetime)
while @since < @to
begin
insert into #dates(dateadd(day,1,@since))
set @since = dateadd(day,1,@since)
end
select [date] from #dates
* скрипта здесь: http://sqlfiddle.com/#!6/9eecb/3469
Ответ 21
set language 'SPANISH'
DECLARE @table table(fechaDesde datetime , fechaHasta datetime )
INSERT @table VALUES('20151231' , '20161231');
WITH x AS
(
SELECT DATEADD( m , 1 ,fechaDesde ) as fecha FROM @table
UNION ALL
SELECT DATEADD( m , 1 ,fecha )
FROM @table t INNER JOIN x ON DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta
)
SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id
,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio
,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio
,DATEPART ( mm , fecha ) Mes_Id
,DATEPART ( yy , fecha ) Anio
,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin
,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia
,datename(MONTH, fecha) mes
,'Q' + convert(varchar(10), DATEPART(QUARTER, fecha)) Trimestre_Name
FROM x
OPTION(MAXRECURSION 0)
Ответ 22
DELIMITER $$
CREATE PROCEDURE GenerateRangeDates(IN dateStart DATE, IN dateEnd DATE)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS dates (day DATE);
loopDate: LOOP
INSERT INTO dates(day) VALUES (dateStart);
SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
IF dateStart <= dateEnd
THEN ITERATE loopDate;
ELSE LEAVE loopDate;
END IF;
END LOOP loopDate;
SELECT day FROM dates;
DROP TEMPORARY TABLE IF EXISTS dates;
END
$$
-- Call procedure
call GenerateRangeDates(
now() - INTERVAL 40 DAY,
now()
);
Ответ 23
SQLite версия верхнего решения RedFilters
select d.Date
from (
select
date(julianday('2010-01-20') + (a.a + (10 * b.a) + (100 * c.a))) as Date
from (select 0 as a 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) as a
cross join (select 0 as a 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) as b
cross join (select 0 as a 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) as c
) d
where
d.Date between '2010-01-20' and '2010-01-24'
order by d.Date
Ответ 24
улучшено с будним днем присоединение к специальной праздничной таблице
microsoft MSSQL 2012 для таблицы даты powerpivot
https://gist.github.com/josy1024/cb1487d66d9e0ccbd420bc4a23b6e90e
with [dates] as (
select convert(datetime, '2016-01-01') as [date] --start
union all
select dateadd(day, 1, [date])
from [dates]
where [date] < '2018-01-01' --end
)
select [date]
, DATEPART (dw,[date]) as Wochentag
, (select holidayname from holidaytable
where holidaytable.hdate = [date])
as Feiertag
from [dates]
where [date] between '2016-01-01' and '2016-31-12'
option (maxrecursion 0)
Ответ 25
Еще одно решение для mysql 8.0.1 и mariadb 10.2.2 с использованием рекурсивных общих табличных выражений:
with recursive dates as (
select '2010-01-20' as date
union all
select date + interval 1 day from dates where date < '2010-01-24'
)
select * from dates;
Ответ 26
WITH
Digits AS (SELECT 0 D UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
Dates AS (SELECT adddate('1970-01-01',t4.d*10000 + t3.d*1000 + t2.d*100 + t1.d*10 +t0.d) AS date FROM Digits AS t0, Digits AS t1, Digits AS t2, Digits AS t3, Digits AS t4)
SELECT * FROM Dates WHERE date BETWEEN '2017-01-01' AND '2017-12-31'
Ответ 27
Элегантное решение с использованием новой рекурсивной (Common Table Expressions) функциональности в MariaDB> = 10.3 и MySQL> = 8.0.
WITH RECURSIVE t as (
select '2019-01-01' as dt
UNION
SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
select * FROM t;
Выше приведена таблица дат между "2019-01-01" и "2019-04-30". Это также прилично быстро. Возвращение дат на 1000 лет (~ 365 000 дней) занимает около 400 мс на моей машине.
Ответ 28
Можно также создать процедуру для создания таблицы календаря с графиком времени, отличным от дня. Если вы хотите таблицу на каждый квартал
например
2019-01-22 08:45:00
2019-01-22 09:00:00
2019-01-22 09:15:00
2019-01-22 09:30:00
2019-01-22 09:45:00
2019-01-22 10:00:00
ты можешь использовать
CREATE DEFINER='root'@'localhost' PROCEDURE 'generate_calendar_table'()
BEGIN
select unix_timestamp('2014-01-01 00:00:00') into @startts;
select unix_timestamp('2025-01-01 00:00:00') into @endts;
if ( @startts < @endts ) then
DROP TEMPORARY TABLE IF EXISTS calendar_table_tmp;
CREATE TEMPORARY TABLE calendar_table_tmp (ts int, dt datetime);
WHILE ( @startts < @endts)
DO
SET @startts = @startts + 900;
INSERT calendar_table_tmp VALUES (@startts, from_unixtime(@startts));
END WHILE;
END if;
END
а затем манипулировать через
select ts, dt from calendar_table_tmp;
которые дают вам также тс
'1548143100', '2019-01-22 08:45:00'
'1548144000', '2019-01-22 09:00:00'
'1548144900', '2019-01-22 09:15:00'
'1548145800', '2019-01-22 09:30:00'
'1548146700', '2019-01-22 09:45:00'
'1548147600', '2019-01-22 10:00:00'
отсюда вы можете начать добавлять другую информацию, такую как
select ts, dt, weekday(dt) as wd from calendar_table_tmp;
или создать реальную таблицу с помощью оператора создания таблицы
