Как в sql задать диапазон дат

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE FUNCTION GeneratingDates ( @DateStart DATE, -- Дата начала @DateEnd DATE -- Дата окончания ) RETURNS @ListDates TABLE (dt DATE) AS BEGIN --Запускаем цикл. Он будет завершен, когда дойдем до даты окончания. WHILE @DateStart 
Способ 2 – использование рекурсивного обобщенного табличного выражения
Второй, альтернативный способ генерации последовательности дат, подразумевает использование рекурсивного обобщенного табличного выражения.
Данная табличная функция работает точно так же как и предыдущая, и принимает ровно те же самые параметры.
--Табличная функция для генерации последовательности дат (способ 2 – WITH) CREATE FUNCTION GeneratingDates ( @DateStart DATE, -- Дата начала @DateEnd DATE -- Дата окончания ) RETURNS @ListDates TABLE (dt DATE) AS BEGIN --Рекурсивное обобщенное табличное выражение. WITH Dates AS ( SELECT @DateStart AS DateStart -- Задаем якорь рекурсии UNION ALL SELECT DATEADD(DAY, 1, DateStart) AS DateStart -- Увеличиваем значение даты на 1 день FROM Dates WHERE DateStart < @DateEnd -- Прекращаем выполнение, когда дойдем до даты окончания ) INSERT INTO @ListDates SELECT DateStart FROM Dates OPTION (MAXRECURSION 0); /* Значением 0 снимаем серверное ограничение на количество уровней рекурсии (которое по умолчанию равно 100), чтобы иметь возможность формировать даты в большом диапазоне. */ RETURN END
Пример использования функций для генерации последовательности дат
Теперь, когда у нас есть функция для генерации последовательности дат, давайте представим, что нам необходимо сформировать последовательность дат за 2020 год, т.е. нам нужны даты в промежутке начиная с 01.01.2020 и заканчивая 31.12.2020.
В итоге у нас должно быть 366 записей, т.е. отдельная запись для каждого дня года (в 2020 году 366 дней, так как это високосный год).
Таким образом, чтобы получить данную последовательность дат, мы обращаемся к нашей табличной функции и передаём в нее соответствующие значения (начало и конец года).
SELECT * FROM GeneratingDates('01.01.2020','31.12.2020');
 
В результате мы получили то, что нам и было нужно.
Таким образом, мы можем генерировать последовательность дат за любой промежуток времени.
На сегодня это все, надеюсь, материал был Вам полезен, пока!
SQL запрос на выборку диапазона даты и времени

Имею таблицу вот такого вида:  Не могу сделать выборку по дате и времени. Делаю так:
SELECT * FROM HistoryReaders WHERE (DateReaders BETWEEN "2018-08-30" AND "2018-08-31") OR (DateReaders = "2018-08-30" AND TimeReaders >= "04:00:00") AND (DateReaders = "2018-08-31" AND TimeReaders  

Но выдаются значения только за последнюю дату. Подскажите как правильней будет сделать запрос

Отслеживать

33.1k 2 2 золотых знака 33 33 серебряных знака 61 61 бронзовый знак

задан 31 авг 2018 в 11:10

Иван Жильников Иван Жильников

41 1 1 золотой знак 1 1 серебряный знак 7 7 бронзовых знаков
так запрос правильно отрабатывает - во второй части условия (DateReaders = "2018-08-31" AND TimeReaders >= "04:00:00") AND (DateReaders = "2018-08-31" AND TimeReaders

31 авг 2018 в 11:20

Ну например диапазон дат 2018-08-30 и 2018-08-31.

31 авг 2018 в 11:22
тогда уберите вот это условие из запроса (DateReaders = "2018-08-31" AND TimeReaders >= "04:00:00") AND (DateReaders = "2018-08-31" AND TimeReaders

31 авг 2018 в 11:25
либо же добавьте скобки, если хотите использовать or . Вот так - SELECT * FROM HistoryReaders WHERE (DateReaders BETWEEN "2018-08-30" AND "2018-08-31") OR ((DateReaders = "2018-08-30" AND TimeReaders >= "04:00:00") AND (DateReaders = "2018-08-31" AND TimeReaders

31 авг 2018 в 11:26
хотя, если честно, не понимаю смысла во второй части условия, т.к. оно в любом случае входит в первое.
31 авг 2018 в 11:33
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Вам нужно выбирать по сумме даты и времени
SELECT * FROM HistoryReaders WHERE (DateReaders BETWEEN "2018-08-30" AND "2018-08-31") AND (DateReaders + TimeReaders BETWEEN "2018-08-30 04:00:00"AND "2018-08-31 13:00:00") 
(DateReaders BETWEEN "2018-08-30" AND "2018-08-31") 
необходимо, чтобы изначально существенно ограничить выборку. Если у вас поле DateReaders индексировано, то вначале делаем быструю выборку по индексированному полю, а потом доуточняем ее.
Если же индекса по полю DateReaders нет, то и условие не нужно. В любом случае будет полный перебор записей
А вообще разделение полей даты и времени в 90% плохая архитектура. Если вам не нужны выборки за определенное время для каждого дня, то эти поля нужно объединить в одно поле типа TIMESTAMP
Выборка диапазона дат из MySQL?
Если даты хранятся в БД в формате DATE 
2017-08-11 
2017-08-22 
2017-09-14 
Можно ли выбрать все данные за август 2017 т.е. в данном примере 2017-08-11, 2017-08-22 не прибегая к утомительному формированию интервала BETWEEN 2017-08-01 AND 2017-08-31 ? 
А как-то попроще сказать ему, типа: дай мне всё что в 08 2017

Вопрос задан более трёх лет назад
5034 просмотра

Комментировать

Решения вопроса 2

Для правильного вопроса надо знать половину ответа

Лучшим по скорости вариантом будет индекс по дате и условие 
WHERE `date` >= '2017-08-01' AND `date` < '2017-09-01'
Можно, конечно, сделать запрос вида 
WHERE YEAR(`date`) = 2017 AND MONTH(`date`) = 8 
но такой запрос, как и BETWEEN не будет использовать индекс.
Ответ написан более трёх лет назад

Комментировать

Нравится 1 Комментировать

А чем плох between то? Тем более собирается проще.
where 
MONTH('2017-08-01') = 8 and YEAR('2017-08-01') =2017
Как сравнивать даты в MySQL

MySQL предоставляет возможность сравнивать различные даты между собой, или с каким-то определенным выражением. В этой статье мы обсудим, как работать с датой в Mysql, как из сравнивать и строить запросы с учетом дат.
Когда вам нужно сравнить дату какого-то столбца с произвольной датой, вы можете использовать функцию DATE() , которая извлекает дату из переданного аргумента (без учета времени) и сравнить ее со строкой нужной вам даты.
В прошлой своей статье я писал про работу с индексами. А в этой затрону не менее важную тему работы с датой в MySQL, понимание которое необходимо каждому разработчику.
Например, предположим, что у вас есть таблица MySQL под названием users со следующими строками:
mysql> SELECT * FROM users; +---------+------------+-----------+---------------------+ | user_id | first_name | last_name | last_update | +---------+------------+-----------+---------------------+ | 201 | Peter | Parker | 2021-08-01 16:15:00 | | 202 | Thor | Odinson | 2021-08-02 12:15:00 | | 204 | Loki | Laufeyson | 2021-08-03 10:43:24 | +---------+------------+-----------+---------------------+ 3 rows in set (0.00 sec) 
Теперь вам нужно выбрать все строки из таблицы users , у которых значение last_update больше 2021-08-01 .
И это достаточно просто сделать:
SELECT user_id, first_name, last_name, last_update FROM users WHERE DATE(last_update) > "2021-08-01" ORDER BY last_update ASC 
Результат запроса выше будет следующим:
+---------+------------+-----------+---------------------+ | user_id | first_name | last_name | last_update | +---------+------------+-----------+---------------------+ | 202 | Thor | Odinson | 2021-08-02 12:15:00 | | 204 | Loki | Laufeyson | 2021-08-03 10:43:24 | +---------+------------+-----------+---------------------+ 2 rows in set (0.00 sec) 
При сравнении столбца типа DATETIME или TIMESTAMP с датой в виде строки (как в запросе выше), MySQL автоматически преобразует значения к единому формату для сравнения и веронет подходящзие результаты.
Чтобы проверить результат запроса добавим оператор ORDER BY в приведенный выше запрос.
Вы можете сразу определить, удовлетворяет ли запрос вашему требованию, взглянув на первую строку. Как и в приведенном выше примере, самое раннее значение столбца last_update должно быть 2021-08-02 .
Вы также можете использовать оператор BETWEEN , чтобы выбрать все строки, в которых столбец даты находится между двумя указанными выражениями даты:
SELECT user_id, first_name, last_name, last_update FROM users WHERE BETWEEN "2021-08-01" AND "2021-08-02" ORDER BY last_update ASC 
Приведенный выше запрос выведет следующий результат:
+---------+------------+-----------+---------------------+ | user_id | first_name | last_name | last_update | +---------+------------+-----------+---------------------+ | 201 | Peter | Parker | 2021-08-01 16:15:00 | | 202 | Thor | Odinson | 2021-08-02 12:15:00 | +---------+------------+-----------+---------------------+ 2 rows in set (0.00 sec) 
НО! MySQL допускает только один формат даты: yyyy-mm-dd , поэтому вам нужно форматировать любое строковое выражение даты по которым вы хотите выбирать данные к нужному формату.
Зачем использовать функцию DATE() для сравнения дат
Функция MySQL DATE() извлекает часть даты из столбца DATETIME или TIMESTAMP и приводит её к формату строки, как показано ниже:
mysql> SELECT DATE('2005-08-28 01:02:03'); -> '2005-08-28' 
Эта функция используется для того, чтобы MySQL использовал для сравнения только часть даты вашего столбца, без учета времени.
Если не использовать функцию DATE() , то MySQL также будет сравнивать и время столбца с вашим строковым выражением. Таким образом, результатом будут записи, полностью совпадаюащие до секунды.
Возвращаясь к приведенному выше примеру таблицы, следующий запрос:
SELECT user_id, first_name, last_name, last_update FROM users WHERE last_update > "2021-08-01" ORDER BY last_update ASC 
Вернет такие результаты:
+---------+------------+-----------+---------------------+ | user_id | first_name | last_name | last_update | +---------+------------+-----------+---------------------+ | 201 | Peter | Parker | 2021-08-01 16:15:00 | | 202 | Thor | Odinson | 2021-08-02 12:15:00 | | 204 | Loki | Laufeyson | 2021-08-03 10:43:24 | +---------+------------+-----------+---------------------+ 3 rows in set (0.01 sec) 
Как видно из результатов, условие сравнения last_update > "2021-08-01" становится last_update > "2021-08-01 00:00:00" , и MySQL возвращает соответствующий набор результатов.
Если вы собираетесь сравнивать записи включая время, то вам нужно добавить часть времени в ваше строковое выражение.
Следующий запрос из той же таблицы:
SELECT user_id, first_name, last_name, last_update FROM users WHERE last_update > "2021-08-01 20:00:00" ORDER BY last_update ASC 
Даст следующий результат:
+---------+------------+-----------+---------------------+ | user_id | first_name | last_name | last_update | +---------+------------+-----------+---------------------+ | 202 | Thor | Odinson | 2021-08-02 12:15:00 | | 204 | Loki | Laufeyson | 2021-08-03 10:43:24 | +---------+------------+-----------+---------------------+ 2 rows in set (0.00 sec) 
Сравнение дат между двумя столбцами дат
Если у вас уже есть два столбца даты, то вы можете сразу же сравнить их с помощью операторов < , = , > или BETWEEN .
Предположим, что в вашей таблице есть столбцы last_update и last_login , как показано ниже:
+---------+------------+-----------+---------------------+---------------------+ | user_id | first_name | last_name | last_update | last_login | +---------+------------+-----------+---------------------+---------------------+ | 201 | Peter | Parker | 2021-08-01 16:15:00 | 2021-08-17 10:00:00 | | 202 | Thor | Odinson | 2021-08-02 12:15:00 | 2021-08-02 08:00:00 | | 204 | Loki | Laufeyson | 2021-08-03 10:43:24 | 2021-08-10 06:00:00 | +---------+------------+-----------+---------------------+---------------------+ 
Чтобы получить все строки, в которых столбец last_login находится позже столбца last_update , вы можете использовать следующий запрос:
SELECT user_id, first_name, last_name, last_update FROM users WHERE last_login > last_update ORDER BY last_update ASC 
Результат будет следующим:
+---------+------------+-----------+---------------------+---------------------+ | user_id | first_name | last_name | last_update | last_login | +---------+------------+-----------+---------------------+---------------------+ | 204 | Loki | Laufeyson | 2021-08-03 10:43:24 | 2021-08-10 06:00:00 | | 201 | Peter | Parker | 2021-08-01 16:15:00 | 2021-08-17 10:00:00 | +---------+------------+-----------+---------------------+---------------------+ 2 rows in set (0.00 sec) 
Вот каким образом вы можете выполнять сравнение дат в MySQL.
Не забудьте, что вам нужно иметь строковое выражение даты, отформатированное как yyyy-mm-dd или yyyy-mm-dd hh:mm:ss , если вы хотите сравнить и временную часть.
Выбор дат за определнный интервал
Очень часто приходится выбирать записи с датой за определнный интервал. Для этих целей в MySql существуют встроенная функции INTERVAL , упрощающая работу с датами за определенный период.
Например, для того, чтобы выбрать записи, обновленные за последний год, выполним запрос:
SELECT user_id, first_name, last_name, last_update FROM users WHERE last_login > CURDATE() - INTERVAL 1 YEAR ORDER BY last_update ASC 
При том, что INTERVAL может работать с различными интервалами времени:

SECOND - секунды
MINUTE - минуты
HOUR - часы
DAY - дни
MONTH - месяцы
YEAR - года

Те же самые действия применимы и к UPDATE-операциям:
UPDATE users SET last_update = CURDATE() - INTERVAL 10 DAY WHERE >
Резюме
В этой статье поговорили про работу с датами в Mysql. Как можно выбрать даты определенной даты, или по указанному интервалу. А так же, как работать с колонками дат разных типов.
Если вы хотите поупражняться с запросами, то вот вам демонстрационная таблица:
CREATE TABLE `users` ( `user_id` smallint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `last_login` timestamp NULL DEFAULT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `users` (`user_id`, `first_name`, `last_name`, `last_update`, `last_login`) VALUES (201,'Peter','Parker','2021-08-01 16:15:00','2021-08-17 10:00:00'), (202,'Thor','Odinson','2021-08-02 12:15:00','2021-08-02 08:00:00'), (204,'Loki','Laufeyson','2021-08-03 10:43:24','2021-08-10 06:00:00'); 

В серці. Назавжди.
Вчора у мене помер однокласник. А сьогодні бабуся. І хто б міг уявити, що цей рік принесе війну, смерть товариша, та смерть члена сім'ї? Це боляче. Проте це добре нагадування про те, як швидко тече час. І як його ціна збільшується кожної марно витраченої секунди. І я не скажу щось
 20 мая 2022 г. 1 min read

Ось такий він, руський мир
"Руський мир" - звучить дуже сильно та виправдовуюче. Гарна обгортка виправдання слабкості, аморальності та нікчемності своїх дійсних намірів. Руський мир, який дуже солодко звучить для всіх, хто хоче закрити очі на факт повномасштабної війни. Дуже добре виправдання вбивства для купки звірів. Втім, це ж росія, в якій все виглядає логічно
 16 апр. 2022 г. 3 min read

Перехват запросов и ответов JavaScript Fetch API
Перехватчики - это блоки кода, которые вы можете использовать для предварительной или последующей обработки HTTP-вызовов, помогая в обработке глобальных ошибок, аутентификации, логирования, изменения тела запроса и многом другом. В этой статье вы узнаете, как перехватывать вызовы JavaScript Fetch API. Есть два типа событий, для которых вы можете захотеть перехватить HTTP-вызовы:


Похожие публикации:

Где купить mac
Как в visual studio писать на python
Как очистить историю в mozilla
Что такое коллизия java