Как получить последовательность дат в указанном промежутке на T-SQL

Всем привет! Сегодня мы поговорим о том, как на языке T-SQL можно сформировать последовательность дат в указанном диапазоне, т.е. когда требуется получить все даты между двумя определенными датами, при этом чтобы каждое значение даты в результирующем наборе данных было в отдельной строке.

Допустим, Вам требуется вывести все даты, начиная с 01.01.2020 по 12.01.2020, иными словами, Вам необходимо сформировать следующую таблицу.

dt
01.01.2020
02.01.2020
03.01.2020
04.01.2020
05.01.2020
06.01.2020
07.01.2020
08.01.2020
09.01.2020
10.01.2020
11.01.2020
12.01.2020

И первое, что может прийти в голову, это использовать или оператор UNION, или конструктор табличных значений VALUES, и в этом конкретном случае, когда требуется сформировать всего 12 записей, это может показаться достаточно простой задачей, однако представим, что нам требуется сформировать даты за большой промежуток времени, например за год, или за несколько лет, тогда этот способ сразу отпадает, так как вручную формировать тысячи строк, наверное, как минимум очень трудоемко, т.е. не очень эффективно. А если еще представить, что нам требуется формировать такие списки дат постоянно и динамически, т.е. начало и конец периода постоянно будут меняться, то такой ручной способ точно не подходит.

Поэтому сейчас мы рассмотрим способы для автоматической генерации последовательности дат.

Способы реализации генерации последовательности дат

В интернете можно встретить решения, которые подразумевают использование вспомогательных таблиц, однако в языке T-SQL все это можно сделать без каких-то внешних вспомогательных инструментов, т.е. с использованием только стандартных конструкций языка.

При этом есть несколько способов, как можно генерировать последовательность дат, в частности мы рассмотрим 2, и для каждого решения создадим табличную функцию, чтобы можно было просто обращаться к функции, передав в нее две даты, т.е. начало периода и его окончание, а в ответ получать таблицу, состоящую из всех дат в заданном промежутке.

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

Способ 1 – использование цикла WHILE

Первый способ подразумевает использование обычного цикла WHILE.

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

Итак, вот инструкция T-SQL, которая создает табличную функцию для генерации последовательности дат.

Принимает она два параметра: первый — начальная дата, и второй — дата окончания. В ответ она возвращает таблицу со всеми датами в этом промежутке.

Способ 2 – использование рекурсивного обобщенного табличного выражения

Второй, альтернативный способ генерации последовательности дат, подразумевает использование рекурсивного обобщенного табличного выражения.

Данная табличная функция работает точно так же как и предыдущая, и принимает ровно те же самые параметры.

Пример использования функций для генерации последовательности дат

Теперь, когда у нас есть функция для генерации последовательности дат, давайте представим, что нам необходимо сформировать последовательность дат за 2020 год, т.е. нам нужны даты в промежутке начиная с 01.01.2020 и заканчивая 31.12.2020.

В итоге у нас должно быть 366 записей, т.е. отдельная запись для каждого дня года (в 2020 году 366 дней, так как это високосный год).

Таким образом, чтобы получить данную последовательность дат, мы обращаемся к нашей табличной функции и передаём в нее соответствующие значения (начало и конец года).

В результате мы получили то, что нам и было нужно.

Таким образом, мы можем генерировать последовательность дат за любой промежуток времени.

Developing.ru

Примитивный SQL запрос С (Дата время) по (Дата Время)

Модераторы:Yurich, Absurd

Как сформировать запрос «Вывести информацию из таблицы с (Дата Время) по (Дата Время)».

Есть таблица TBL, в которой кроме кучи полей есть поле Data (в формате Y.M.D) и поле Time в формате (H:M:S).

Какой SQL запрос должен быть, что бы выводить информацию,
например, с дата=‘2008.11.01’ время=‘10:12:11’ по дата=‘2009.10.12’ время=‘23:12:11’.

P.S
Простите за детский сад

Странно, что дата и время хранятся в разных полях. Если нужно сохранять момент «времени на оси времени», то понятно, что это должно быть единое комбинированное поле. Так как в противном случае поле «время» без поля «дата» все равно не имеет никакого смысла.
На практике, конечно, можно придумать такую задачу, где поле «время» само по себе может иметь смысл. Но, разумеется, отличный от фиксировании «некоторого момента на оси времени». Например, при описании некоторых ежедневных событий. Скажем, время открытия и закрытия магазина.

В связи с этим фраза «с дата=‘2008.11.01’ время=‘10:12:11’ по дата=‘2009.10.12’ время=‘23:12:11’.» может иметь двоякий смысл. Если надо найти все, что между этими двумя точками, то вариант AiK, конечно, не подходит. Т.к, не найдет, например, дата=‘2008.12.01’ время=‘09:00:00’. Тут надо писать примерно следующее:

Странного в раздельном хранении даты и времени нет. Много средств разработки имеют отдельные типы данных для даты и для времени. На это есть очень много достойных причин. Ты и сам об одной пишешь — не всем надо знать точную дату события.

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

Банальный пример, открутка баннеров на этом сайте. Ночные часы — баннеры, в дневное ппц реклама. И таких сайтов — целый интернет, и у каждого есть своя статистика посещений и подсчета доходов и т.д. и т.п.
———————————————————————————
Добавленное сообщение
———————————————————————————

Sasha8111 писал(а): Дорогие специалисты!

Как сформировать запрос «Вывести информацию из таблицы с (Дата Время) по (Дата Время)».

Какой SQL запрос должен быть, что бы выводить информацию,
например, с дата=‘2008.11.01’ время=‘10:12:11’ по дата=‘2009.10.12’ время=‘23:12:11’.

Будь проще! Время тебя скоре всего вообще не должно беспокоить. Если у тебя «дата», то это дата! Времени у него как бы и нет. Теоретически это 00:00

Тогда так и без between!

select * from x where ‘01.11.2008’ < дата and дата <= ‘12.10.2009’

Конечно, если в твоём аргументе «дата» есть время, то. легче его оттуда вычленить и затем писать sql

Предикат SQL BETWEEN — поиск значений из указанного интервала

С помощью предиката SQL BETWEEN можно извлечь из таблицы строки, в которых значения некоторого проверяемого столбца находятся в интервале, границы которого обозначены некоторым выражением. Границы интервала также включены в него.

Запросы с предикатом SQL BETWEEN имеют следующий синтаксис:

Если перед предикатом BETWEEN поставить ключевое слово NOT, то в выборку попадут строки, в которых значение проверяемого столбца находится за пределами интервала: до ВЫРАЖЕНИЯ_1 и после ВЫРАЖЕНИЯ_2.

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

В первых примерах работаем с базой данных «Компания 2», содержащей данные о заработной плате сотрудников. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .

Запросы с BETWEEN: интервал задан указанными числами

В самых простых запросах с предикатом BETWEEN границы интервала для проверяемого столбца заданы непосредственным указанием числовых значений.

Пример 1. Итак, работаем с базой данных фирмы и её таблицей Staff, содержащей данные о заработной плате сотрудников.

ID	Name	Salary
1	Sanders	18357.5
2	Pernal	15430.0
3	Marenghi	17506.8
4	Doctor	12322.4
5	Factor	16228.7
6	Junkers	16232.8
7	Moonlight	21500.6
8	Aisen	19540.7
9	MacGregor	15790.8

Требуется вывести сотрудников, заработная плата которых находится в интервале от 16 000 до 18 000. Пишем следующий запрос с предикатом BETWEEN:

Запрос выведет следующую результирующую таблицу:

ID	Name	Salary
3	Marenghi	17506.8
5	Factor	16228.7
6	Junkers	16232.8

Пример 2. База данных и таблица — те же, что в примере 1. Требуется вывести сотрудников, заработная плата которых находится вне интервала от 16 000 до 18 000. Пишем следующий запрос с предикатом BETWEEN, перед которым ставим ключевое слово NOT:

Запрос выведет следующую результирующую таблицу:

ID	Name	Salary
1	Sanders	18357.5
2	Pernal	15430.0
4	Doctor	12322.4
7	Moonlight	21500.6
8	Aisen	19540.7
9	MacGregor	15790.8

Запросы с BETWEEN: интервал задан вложенными запросами

В запросах с BETWEEN границы интервала для проверяемого столбца можно не указывать явно, задав его некоторыми условиями, которые определены вложенными запросами.

Пример 3. Продолжаем работать с базой данных Staff. Требуется вывести сотрудников, заработная плата которых находится в интервале между средней и максимальной. Пишем следующий запрос, в котором границы заданы вложенными запросами:

Результатом выполнения запроса будет следующая таблица:

ID	Name	Salary
1	Sanders	18357.5
3	Marenghi	17506.8
7	Moonlight	21500.6
8	Aisen	19540.7

Пример 4. База данных и таблица — те же. Требуется вывести сотрудников, заработная плата которых находится в интервале между заработной платой сотрудника MacGregor и заработной платой сотрудика Marenghi. Пишем следующий запрос, в котором границы заданы вложенными запросами:

Результатом выполнения запроса будет следующая таблица:

ID	Name	Salary
3	Marenghi	17506.8
5	Factor	16228.7
6	Junkers	16232.8
9	MacGregor	15790.8

Как видим, граничные значения интервала попали в результирующую таблицу.

Написать запрос с предикатом BETWEEN самостоятельно, а затем посмотреть решение

Пример 5. Продолжаем работать с таблицей Staff. Требуется вывести сотрудников с ID между ID сотрудника Marenghi и ID сотрудника Moonlight.

Запросы с BETWEEN: интервал задан значениями даты и времени

Интервал для извлечения строк с помощью предиката BETWEEN может быть задан не только значениями числового типа данных, но и значениями даты и времени.

Далее работаем с базой данных «Недвижимость». Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .

Пример 6. Итак, работаем с базой данных «Недвижимость».

Таблица Deal содержит данные о сделках. Нам потребуется столбец Date, в котором указывается дата совершения сделки. Для упрощения будем считать, что в таблице представлены сделки, заключённые в течение одного года. Требуется вывести сделки, заключённые в августе и сентябре. Пишем следующий запрос:

Примеры запросов к базе данных «Недвижимость» есть также в уроках по оператору GROUP BY, предикату EXISTS, функциям ALL и ANY и LIMIT.

Типы данных INTERVAL в PL/SQL: интервал между датами

Типы, рассматривавшиеся до настоящего момента, представляют определенные моменты времени. Типы данных INTERVAL , появившиеся в Oracle9i, предназначены для сохранения и обработки временных промежутков. Чтобы получить более полное представление о них, вспомним, с какими значениями даты/времени мы сталкиваемся в повседневной жизни:

Oracle поддерживает два типа данных INTERVAL . Оба типа были введены в Oracle9i, и оба соответствуют стандарту ISO SQL:

• Момент — временная точка, определенная с некоторой точностью. Например, когда мы собираемся встать утром в заданное время, это время представляет момент. Он может определяться с точностью до часа, до минуты и т. д. Все типы данных DATE и TIMESTAMP предназначены для определения моментов времени.
• Интервал — понятие, которое относится не к конкретной временн?ой точке, а к определенному количеству времени. В повседневной жизни мы постоянно имеем дело с временны? ми интервалами: работаем по восемь часов, обедаем в течение часа (если бы!) и т. д. Для представления интервалов используются два типа данных INTERVAL .
• Период — интервал, который начинается и заканчивается в заданный момент. Например, если вы встали в 8:00 и работали в течение восьми часов, то 8-часовой интервал времени, начинающийся в 8:00, можно считать периодом. В Oracle нет специализированного типа данных для непосредственной поддержки периодов, как нет его и в стандарте SQL.

• INTERVAL YEAR TO MONTH — позволяет определить интервал времени в годах и месяцах;
• INTERVAL DAY TO SECOND — позволяет определить интервал времени в днях, часах, минутах и секундах (с долями секунд).

Объявление интервальных переменных в PL/SQL

По сравнению с другими объявлениями переменных PL/SQL синтаксис объявлений переменных обоих типов INTERVAL несколько необычен. Помимо того, что имена этих типов состоят из нескольких слов, для них задается не одно, а два значения, определяющих точность:

Здесь имя_переменной — имя объявляемой переменной INTERVAL ; точность_лет — количество цифр (от 0 до 4), выделенное для представления количества лет (по умолчанию 2); точность_дней — количество цифр (от 0 до 9), выделенное для представления количества дней (по умолчанию 2); точность_долей_секунды — количество цифр (от 0 до 9), выделенное для представления количества долей секунды (по умолчанию 6).

О точности значений интервалов, как правило, можно не беспокоиться. Значения типа INTERVAL YEAR TO MONTH всегда нормализуются таким образом, что количество месяцев лежит в диапазоне от 0 до 11. Фактически Oracle не позволяет задать месяц значением больше 11; интервал в 1 год и 13 месяцев должен быть выражен как 2 года и 1 месяц. Значение параметра точность_лет устанавливает максимальный размер интервала типа INTERVAL YEAR TO MONTH . Аналогичным образом значение параметра точность_дней устанавливает максимальный размер интервала типа INTERVAL DAY TO SECOND .

Точность для часов, минут и секунд для значения типа INTERVAL DAY TO SECOND не нужно задавать по той же причине, по которой не задается точность для месяцев значения типа INTERVAL YEAR TO MONTH . Интервалы INTERVAL DAY TO SECOND всегда нормализуются таким образом, что значения часов, минут и секунд находятся в естественных диапазонах: 0–23 часа, 0–59 минут и 0–59 секунд (за исключением долей секунды).

Доли секунды указываются потому, что значения типа INTERVAL DAY TO SECOND могут определять интервалы с указанной точностью до долей секунды. Значения типа INTERVAL YEAR TO MONTH не могут содержать долей месяца, и последние для них не задаются.

Когда используются типы INTERVAL в PL/SQL

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

Вычисление разности между двумя значениями даты/времени

Типы INTERVAL удобно использовать для вычисления разности между двумя значениями даты/времени. В следующем примере вычисляется срок работы сотрудника:

Непосредственное вычисление количества лет и месяцев работы выполняется в следующей строке:

Здесь YEAR TO MONTH — часть синтаксиса выражения, возвращающего интервал. Подробнее о нем рассказывается далее в этой главе. Как видите, вычисление продолжительности интервала сводится к простому вычитанию одной даты из другой. Без типа данных INTERVAL нам пришлось бы программировать вычисления самостоятельно:

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

Тип INTERVAL YEAR TO MONTH выполняет округление значений, и вы должны знать о возможных последствиях этой операции. За подробностями обращайтесь к разделу «Арифметические операции над значениями даты/времени».

Обозначение периода времени

В этом примере анализируется работа конвейерной сборки. Важной метрикой эффективности является время, необходимое для сборки каждого продукта. Сокращение этого интервала повышает эффективность работы конвейера, поэтому начальство желает постоянно контролировать его продолжительность. В нашем примере каждому продукту присваивается контрольный номер, используемый для его идентификации в процессе сборки. Информация хранится в следующей таблице:

Также нам понадобится функция PL/SQL, возвращающая время сборки для заданного идентификатора tracking_id . Значение вычисляется вычитанием текущего времени из времени начала сборки. Арифметические операции с датами более подробно рассматриваются позднее в этой главе. Функция вычисления времени сборки:

При передаче интервалов программам PL/SQL и из них необходимо использовать ключевое слово UNCONSTRAINED (см. далее раздел «Типы данных INTERVAL без ограничений»). Хранение времени сборки в таблице упрощает анализ данных. Мы можем легко определить минимальное, максимальное и среднее время сборки при помощи простых функций SQL, а также находить ответы на вопросы «Выполняется ли сборка по понедельникам быстрее, чем по вторникам?» или «Какая смена работает более производительно, первая или вторая?» Впрочем, я забегаю вперед. Этот тривиальный пример просто демонстрирует основные концепции интервалов. Ваша задача как программиста — найти творческое применение этим концепциям.