126

Вопрос по mysql, sql, between, datetime – генерировать дни из диапазона дат

Я хотел бы выполнить запрос как

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

@ Нэнн, именно поэтому я сохранил этот вопрос. Мне нужно выше, чтобы присоединиться к данным, которые могут не существовать на определенные даты.

от Josh Diehl

Примером его использования может быть создание статистики и включение строки для дат, по которым у вас нет данных. Если вы выполняете какое-то группирование, может быть гораздо быстрее на самом деле сгенерировать всю информацию в SQL и добавить ее в любой нужный вам формат, вместо того, чтобы выгружать данные как есть на свой язык, и начинать циклы и добавлять свои пустеет.

от Nanne

Я думаю об использовании, чтобы найти вам проблему ... Если у вас есть задача, чтобы заполнить некоторые пропущенные записи в вашей таблице. И вы должны выполнить запрос для каждого дня, я думаю, что-то вродеinsert into table select ... as days date between '' and ''

от Pentium10

Вам просто нужен массив дат на основе выбранного диапазона дат?

от Derek Adair

Нет другой проблемы, связанной с этим вопросом. Выше вопрос является проблемой, освоив курсы SQL.

от Pentium10
28 ответов
296

Это решение используетselect a.Date

Это решение используетнет циклов, процедур или временных таблиц, Подзапрос генерирует даты за последние 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 дней

Необходим следующий твик для PostgreSQL: часть "INTERVAL (a.a + ... * ca) DAY" должна быть "(a.a + ... * ca) * INTERVAL '1' DAY" - В любом случае, очень полезный ответ, работает с герою датаклипс, спасибо!

от corg

@BoratSagdiyev Вы говорите, что пример SQL Fiddle возвращает ноль строк для вас? У меня отлично работает.

от RedFilter

почему бы просто не указать дату и покончить с этим - потому что описанный выше метод позволяет вам создавать произвольно большие наборы чисел (и дат), не требующие создания таблицы, было бы больно жестко кодировать предложенным вами способом. Очевидно, для 5 дат это излишне; но даже в этом случае, если вы объединяетесь с таблицей, в которой вы заранее не знаете даты, а только потенциальные минимальные и максимальные значения, это имеет смысл.

от RedFilter

+1 за один из самых сюрреалистических запросов, которые я когда-либо видел.

от Lluis Martinez
31

Вот еще один вариант с использованием представлений

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;

Это не работает во всех случаях. ВЫБРАТЬ дату ИЗ ДАТЫ ГДЕ ДАТА МЕЖДУ «2014-12-01» И «2014-12-28» ПОРЯДОК ПО ДАТЕ

от vasanth

Работает очень хорошо

от phpmysqlguy

Хороший звонок @ user927258. Это потому что первый взглядdates упомянутый выше вычисляет даты, начиная с текущей даты, поэтому вы не сможете получить даты, установленные в будущем. Ответ от @RedFilter страдает тем же недостатком дизайна. Я добавил обходной путь в своем ответе.

от Stéphane

Использование некоторых представлений определенно упрощает запросы и делает их многократно используемыми. Хотя по сути они делают то же самое, все теUNION пункты выглядят странно в одном выражении SQL.

от Stewart
19

Принятый ответ не работает для 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)
13

Используя рекурсивное общее табличное выражение (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. Генерация ста лет заняла меньше секунды.

7

MSSQL Queryselect datetable.Date

MSSQL Query

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

Если бы я только прокрутил немного больше ... вздох. В любом случае, спасибо. Я добавил CAST (<выражение> AS DATE), чтобы удалить время в моей версии. Также используется, где a.Date между GETDATE () - 365 и GETDATE () ... если вы выполните свой запрос сегодня, он не даст никаких строк, если вы не заметите даты в WHERE = P

от Ricardo C
4

Старое школьное решение сделать это без цикла / курсора - создатьCREAT

Старое школьное решение сделать это без цикла / курсора - создать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
Что бы вы использовали для этого?

Сформировать списки дат или номеров для того, чтобы присоединиться к. Вы должны сделать это, чтобы увидеть, где есть пропуски в данных, потому что вы ЛЕВЫЕ ПРИСОЕДИНЯЕТЕСЬ к списку последовательных данных - нулевые значения сделают очевидным, где существуют пропуски.

DUAL Таблица поддерживается Oracle и MySQL для использования в качестве таблицы вFROM пункт. Он не существует, выбор значений из него вернет любое значение. Идея заключалась в том, чтобы иметь замену, потому что запрос SELECT требуетFROM пункт, указывающий хотя бы одну таблицу.

от OMG Ponies

+1 за создание таблицы постоянных чисел вместо того, чтобы СУБД собирала ее каждый раз, когда вам нужен запрос. Вспомогательные столы не зло, люди!

от Bacon Bits

какойdual ?

от Pentium10
4

Для доступа 2010 - требуется несколько шагов; Я следовал той же схеме, что и выше, но думал, что смогу помочь кому-то в Access. Отлично сработало для меня, мне не нужно было держать таблицу с датами.

Создайте таблицу с именем DUAL (аналогично тому, как работает таблица Oracle DUAL)

ID (AutoNumber)DummyColumn (Text)Добавить значения одной строки (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#

БЛАГОДАРЮ ВАС!!! Я сходил с ума из-за кода выше ... +1: D

от Kjenos
3

Thx 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 просто «потому что для доступа нужен счетчик таблиц» как минимум в 1 записи, в предложении from - подойдет любая таблица с хотя бы 1 записью.

2

Процедура + временная таблица:DELIMITER $$

Процедура + временная таблица:

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
2

попробуй это.SELECT TO_DATE(&apos

попробуй это.

SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day 
from DUAL
connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ;
1

если вам понадобится больше, чем пара дней, вам нужен стол.

Создать диапазон дат в MySQL

затем,

select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;

почему вы опубликовали это, так как приведенный выше ответ не нуждается в таблице и предоставляет решение?

от Pentium10
1

Как указывалось (или

по крайней мере, упоминалось) во многих замечательных ответах, которые уже были даны, эта проблема легко решается, когда у вас есть набор чисел для работы.

Замечания: Ниже приведен 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 и является чрезвычайно тривиальным. В приведенном выше запросе есть 2 ключевых элемента:Дата начала из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
);
1

Создать даты между двумя полями датыЕсли вы знакомы с запросом SQL CTE, то это решение поможет вам решить ваш вопрос.

Вот пример

У нас есть даты в одной таблице

Название таблицы: «дата теста»

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

Объяснение: первый столбец - «начальная дата», второй столбец - это разница начальной и конечной даты в днях, и он будет рассматриваться как столбец «diff».

Вторая часть запроса:

UNION ALL SELECT StartTime, diff-1 AS diff FROM CTE WHERE diff<>0

Объяснение: Union all будет наследовать результат вышеупомянутого запроса до тех пор, пока результат не станет нулевым, поэтому результат «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

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

-1: не могу сделать CTE в MySQL.

от Hogan
1

Короче, чем принятый ответ, та же идея:(SELECT TRIM(&apos

Короче, чем принятый ответ, та же идея:

(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')
1

Для тех

кто хочет сохранить это представление (MySQL не поддерживает вложенные операторы select в представлениях):

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
...
0

Еще одно решение для 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;
1

Это хорошая идея с генерацией этих дат на лету. Тем не менее

я не чувствую себя комфортно делать это с довольно большим диапазоном, поэтому я получил следующее решение:

Создана таблица «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 (ПК)(-) используется временная таблица и представление
0

select d.Date

SQLite версия топ-решения RedFilters

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
0

Хорошо .. Попробуйте это:

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, но это может быть выполнимо с вещами, специфичными для MySQL.
Опять же, может быть, вам нужны курсоры:http://dev.mysql.com/doc/refman/5.0/en/cursors.html

Loop звучит интересно

от Pentium10
0

улучшилось с выходным днемwith [dates] as (

улучшилось с выходным днемприсоединение к пользовательскому праздничному столу MicrosoftMSSQL 2012 для таблицы дат PowerPivothttps://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)
0

Для Oracle мое решение:select trunc(sysdate-dayincrement, &apos

Для Oracle мое решение:

select trunc(sysdate-dayincrement, 'DD') 
  from dual, (select level as dayincrement 
                from dual connect by level <= 30)

Системная дата может быть изменена на конкретную дату, а номер уровня может быть изменен, чтобы дать больше дат.

0

Можно создать процедуру также для создания

стол календаря с временной картойотличается от дня. Если вы хотите таблицу на каждый квартал

например

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;

или создать реальную таблицу ссоздать оператор таблицы

0

если вы хотите список дат между двумя датами

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

0

Code:

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)
0

Code:

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()
    );
0

Вы хотели бы получить диапазон дат.

В вашем примере вы хотели бы получить даты между '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

Ответ, получивший наибольшее количество голосов, использует такой же подход, какhttps://stackoverflow.com/a/2652051/1497139 за основу:

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'
0

Code:

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'
0

Элегантное решение

использующее новую рекурсивную (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 мс на моей машине.

Похожие вопросы