Sql больше чем дата
Блог веб-разработчика
О разработке веб-приложений на Symfony
MySQL: cравнение даты (datetime) через оператор BETWEEN
Оператор BETWEEN идеально подходит для сравнения диапазона между датами (datetime). Но тут есть подводные камни. Например, есть задача — выбрать данные из таблицы за некоторый промежуток времени (с ‘2008-08-14’ по ‘2008-08-23’).
Рекомендации:
1. Выполняя любые сравнения, приводить все данные к одному типу.
2. Если один операнд имеет значение типа TIMESTAMP или DATETIME, а другой является константой, операнды сравниваются как значения типа TIMESTAMP. А это значит, что если была строка в виде ‘2008-08-14’, то она автоматически преобразуется в TIMESTAMP ‘2008-08-14 00:00:00’ и это влияет на результат запроса.
3. Над данными, которые участвуют в условиях сравнения желательно не делать никаких операций — это позволяет для них использовать индексы, иначе они игнорируются.
Примеры запросов с BETWEEN и без него:
Получаем самый лучший запрос:
9 Responses to “MySQL: cравнение даты (datetime) через оператор BETWEEN”
автору респект.
Очень доходчиво
А в случае если дату брать как TIMESTAMP, по идее индексы должны работать
SELECT * FROM `ps_feed` WHERE created_at BETWEEN TIMESTAMP(‘2009-08-20’) AND TIMESTAMP(‘2009-08-27’)
«Самый лучший» у меня запрос не работает. Но за то прекрасно работает
SELECT * FROM news WHERE calldate BETWEEN ‘2009-09-03%’ AND ‘2009-09-08%’
где calldate — типа datetime
SELECT * FROM news WHERE DATE(calldate) BETWEEN ‘2009-09-03’ AND ‘2009-09-08’
[…] MySQL: cравнение даты (datetime) через оператор BETWEEN […]
Автору — спасибо.
Кратко, чётко и без «писательских соплей». 8)
Больше бы таких статей в интернете.
Спасибо автору!
Как раз то, что я искал)!
В 4-м варианте мы явно приводим данные к нужному типу — и сюрпризов в этом случае не будет. 3-й вариант тоже будет работать. Стоит всегда проверять запросы с помощью EXPLAIN — и выбирать лучший запрос.
SQL MAX () на значение даты
Функция MAX () на дату
В этой части вы увидите использование функции SQL MAX () для типа даты столбца таблицы.
Пример:
Пример таблицы: заказы
Чтобы получить максимальное значение ord_date из таблицы orders, можно использовать следующий оператор SQL:
SQL MAX () для значения даты с помощью где
Чтобы получить данные «ord_num», «ord_amount», «ord_date», «agent_code» из таблицы «orders» при следующих условиях:
1. «ord_date» равно максимальному «ord_date»,
2. максимальное значение ‘ord_date’ для тех агентов, чей код_агента ‘A002’,
можно использовать следующий оператор SQL:
SQL MAX () на дату с группировкой по
Чтобы получить данные «agent_code» и максимального значения «ord_date» с заданным пользователем псевдонимом столбца «Max Date» для каждого агента из таблицы заказов со следующим условием:
1. ‘agent_code’ должен входить в группу
можно использовать следующий оператор SQL:
SQL MAX () на значение даты с подзапросом
Чтобы получить данные «agent_code», «ord_date» и «cust_code» из таблицы «orders» при следующих условиях:
‘agent_code’ таблицы ‘orders’ должен быть равен ‘agent code’ из
таблица ‘orders’ упоминается как псевдоним ‘S’
можно использовать следующий оператор SQL:
SQL MAX () для значения даты с использованием соединения
Пример таблицы: заказы
Пример таблицы: отправка
Чтобы получить все столбцы из таблиц ‘orders’ и ‘despatch’ после объединения, с соблюдением следующего условия:
1. «ord_date» должно быть наибольшим (максимальным) из таблицы «orders»,
2. наибольшее (максимальное) значение ord_date должно быть равно таблице ord_date of orders,
3. ‘agent_code’ таблицы ‘orders’ должен быть равен ‘agent_code’ таблицы ‘despatch’ для присоединения,
можно использовать следующий оператор SQL:
Примечание. Выводы указанного оператора SQL, показанного здесь, взяты с использованием Oracle Database 10g Express Edition.
Вот слайд-презентация всех агрегатных функций.
Упражнения по SQL
Хотите улучшить вышеуказанную статью? Вносите свои заметки / комментарии / примеры через Disqus.
MySQL запрос сравнения дат
У меня есть эта таблица в моей базе данных:
Date_x имеет тип DATE
Я хотел бы выбрать строки, где a = 81 или b = 81 и date_x до 2019-05-28.
Поэтому я выполняю следующий запрос в MySQL Workbench:
Я ожидаю, что 2018-03-16 не позднее, чем 2019-05-28. Более того, почему вернулось только 2 строки? В столбце date_x есть еще один с такой же датой.
Этот запрос возвращает то же самое:
Я выполнил следующий запрос для отладки:
Затем оба возвращаются, как и ожидалось:
Я прочитал следующее Q / A, но я все еще что-то упускаю:
Любой намек? Спасибо
3 ответа
Ваш запрос имеет форму
SELECT * FROM t WHERE condition_a OR condition_b AND condition_c
SELECT * FROM t WHERE condition_a OR (condition_b AND condition_c)
Отсюда и путаница. Ничего общего с датами как таковыми.
Может иметь больше смысла, особенно когда вы рассматриваете производительность, рассмотрите эту структуру таблицы и индексацию.
Нельзя использовать индексы, см. демонстрационную версию, и, скорее всего, в конечном итоге вы закончите сканирование полной таблицы / индексные файлы.
Поскольку оптимизатор MySQL основан на затратах, я также добавил этот, чтобы убедиться, что ПОЛНЫЙ сканирование по первому запросу не было просто вызвано низким числом отключенных записей.
Но ясно, что у второго запроса более стабильный план.
Я считаю, что база данных Oracle позволяет OR переписать UNION ALL непосредственно в этом оптимизаторе, если он проиндексирован. Поскольку MySQL принадлежит Oracle, я надеюсь, что они также добавят эту оптимизацию в MySQL.
DATEDIFF_BIG (Transact-SQL)
Эта функция возвращает количество пересеченных границ (целое число со знаком), указанных в аргументе datepart, за период времени, указанный в аргументах startdate и enddate.
Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).
Синтаксические обозначения в Transact-SQL
Синтаксис
Аргументы
datepart
Часть аргументов startdate и enddate, которая задает тип пересекаемых границ.
DATEDIFF_BIG не принимает значения datepart из переменных, определяемых пользователем, или как строки в кавычках.
В приведенной ниже таблице перечислены все допустимые имена аргументов datepart и их сокращения.
| Имя datepart | Сокращение datepart |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Каждое конкретное имя аргумента datepart и сокращение этого имени datepart будут возвращать одно и то же значение.
startdate
Выражение, которое может быть разрешено в одно из следующих значений.
Для date DATEDIFF_BIG будет принимать столбец выражения, выражение, строковый литерал или определяемую пользователем переменную. Значение строкового литерала должно разрешаться в datetime. Во избежание неоднозначности используйте четырехзначную запись года. DATEDIFF_BIG вычитает startdate из enddate. Во избежание неоднозначности используйте четырехзначную запись года. Сведения о двузначном обозначении года см. в статье Настройка параметра конфигурации сервера two digit year cutoff.
enddate
См. описание аргумента startdate.
Тип возвращаемых данных
bigint со знаком
Возвращаемое значение
Значение типа bigint, представляющее разницу между аргументами startdate и enddate в границах, определяемых аргументом datepart.
В качестве возвращаемого значения вне диапазона для bigint (от –9 223 372 036 854 775 808 до 9 223 372 036 854 775 807) DATEDIFF_BIG возвращает сообщение об ошибке. В отличие от функции, которая возвращает значение типа int и поэтому может переполняться с точностью до минуты или более высокой точностью, DATEDIFF_BIG может переполняться только при использовании точности до наносекунды, если разница между enddate и startdate больше 292 лет, 3 месяцев, 10 дней, 23 часов, 47 минут и 16,8547758 секунд.
Если обоим аргументам, startdate и enddate, присвоено только значение времени, а аргумент datepart не содержит значения времени datepart, то DATEDIFF_BIG возвращает значение 0.
При вычислении возвращаемого значения DATEDIFF_BIG учитывает компонент смещения часовых поясов для аргументов startdate или enddate.
Так как значение типа smalldatetime имеет точность до минуты, то при использовании в аргументах startdate или enddate DATEDIFF_BIG всегда задает 0 в качестве возвращаемого значения smalldatetime секунд и миллисекунд.
Если аргументы startdate и enddate имеют разные типы данных даты, но при этом один из них имеет больше частей времени или обладает более высокой точностью, DATEDIFF_BIG присваивает значения 0 недостающим частям другого аргумента.
Границы, задаваемые аргументом datepart
Приведенные ниже инструкции имеют одинаковые значения аргументов startdate и enddate. Указанные даты являются соседними, а временная разница между ними составляет 100 наносекунд (0,0000001 секунды). Разница между аргументами startdate и enddate в каждой инструкции пересекает одну календарную или временную границу аргумента datepart. Каждое выражение возвращает значение 1. Если аргументы startdate и enddate имеют разные значения года, но одинаковые значения календарной недели, DATEDIFF_BIG вернет 0 для части week аргумента datepart.
Remarks
Функция DATEDIFF_BIG неявно приводит строковые литералы к типу datetime2. Это означает, что DATEDIFF_BIG не поддерживает формат ГЧМ (год, число, месяц) при передаче даты в виде строки. Для использования формата ГЧМ (год, число, месяц) необходимо явно привести строку к типу datetime или smalldatetime.
DATEDIFF_BIG может переполняться с точностью до наносекунды, если разница между enddate и startdate представляет собой значение, выходящее за пределы диапазона, допустимого для bigint.
Примеры
Указание столбцов в качестве начальной и конечной даты
В этом примере выражения различного типа используются в качестве аргументов для параметров startdate и enddate. В нем подсчитывается количество границ дней, пересекаемых между датами, указанными в двух столбцах таблицы.
Определение разницы между startdate и enddate в виде строковых компонентов даты
Тесно связанные примеры см. в статье DATEDIFF (Transact-SQL).
DATEDIFF (Transact-SQL)
Эта функция возвращает количество пересеченных границ (целое число со знаком), указанных в аргументе datepart, за период времени, указанный в аргументах startdate и enddate.
Сведения о функции, которая обрабатывает значительные отличия значений аргументов startdate и enddate, см. в статье DATEDIFF_BIG (Transact-SQL). Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
В приведенной ниже таблице перечислены все допустимые значения datepart. Функция DATEDIFF принимает либо полное имя datepart, либо любое приведенное сокращение полного имени.
| Имя datepart | Сокращение datepart |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Каждое конкретное имя аргумента datepart и сокращение этого имени datepart будут возвращать одно и то же значение.
startdate
Выражение, которое может быть разрешено в одно из следующих значений.
Во избежание неоднозначности используйте четырехзначную запись года. Сведения о двузначном обозначении года см. в статье Настройка параметра конфигурации сервера two digit year cutoff.
enddate
См. описание аргумента startdate.
Тип возвращаемых данных
Возвращаемое значение
Значение типа int, представляющее разницу между аргументами startdate и enddate в границах, определяемых аргументом datepart.
В качестве возвращаемого значения вне диапазона для int (от –2 147 483 648 до 2 147 483 647) DATEDIFF возвращает сообщение об ошибке. Для миллисекунды максимальная разница между startdate и enddate составляет 24 дня 20 часов 31 минута и 23 647 секунд. Для секунды максимальная разница составляет 68 лет, 19 дней, 3 часа, 14 минут и 7 секунд.
Если обоим аргументам, startdate и enddate, присвоено только значение времени, а аргумент datepart не содержит значения времени datepart, то DATEDIFF возвращает значение 0.
При вычислении возвращаемого значения DATEDIFF использует компонент смещения часовых поясов для аргументов startdate или enddate.
Так как значение типа smalldatetime имеет точность до минуты, то при использовании в аргументах startdate и enddate значений типа smalldatetime секунды и миллисекунды у возвращаемых значений всегда равны 0.
Если аргументы startdate и enddate имеют разные типы данных даты, но при этом один из них имеет больше частей времени или обладает более высокой точностью, DATEDIFF присваивает значения 0 недостающим частям другого аргумента.
Границы, задаваемые аргументом datepart
Приведенные ниже инструкции имеют одинаковые значения аргументов startdate и enddate. Указанные даты являются соседними, а временная разница между ними составляет 100 наносекунд (0,0000001 секунды). Разница между аргументами startdate и enddate в каждой инструкции пересекает одну календарную или временную границу аргумента datepart. Каждое выражение возвращает значение 1.
Если аргументы startdate и enddate имеют разные значения года, но одинаковые значения календарной недели, DATEDIFF вернет 0 для части week аргумента datepart.
Remarks
Функция DATEDIFF неявно приводит строковые литералы к типу datetime2. Это означает, что DATEDIFF не поддерживает формат ГЧМ (год, число, месяц) при передаче даты в виде строки. Для использования формата ГЧМ (год, число, месяц) необходимо явно привести строку к типу datetime или smalldatetime.
DATEDIFF может переполняться с точностью до минуты или более низкой точностью, если разница между enddate и startdate представляет собой значение, выходящее за пределы диапазона, допустимого для int.
Примеры
В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.
A. Указание столбцов в качестве начальной и конечной даты
В этом примере подсчитывается количество границ дней, пересекаемых между датами в двух столбцах таблицы.
Б. Указание определенных пользователем переменных в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают определенные пользователем переменные.
В. Указание скалярных системных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные системные функции.
Г. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.
Д. Указание констант в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate используются символьные константы.
Е. Указание числовых выражений и скалярных системных функций в качестве конечной даты
Ж. Указание ранжирующих функций в качестве начальной даты
В этом примере в качестве аргумента startdate. используется ранжирующая функция.
З. Указание агрегатной оконной функции в качестве начальной даты
В этом примере в качестве аргумента startdate используется агрегатная оконная функция.
И. Определение разницы между startdate и enddate в виде строковых компонентов даты
Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)
В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.
К. Указание столбцов в качестве начальной и конечной даты
В этом примере подсчитывается количество границ дней, пересекаемых между датами в двух столбцах таблицы.
Л. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.
М. Указание констант в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate используются символьные константы.
Н. Указание ранжирующих функций в качестве начальной даты
В этом примере в качестве аргумента startdate. используется ранжирующая функция.
О. Указание агрегатной оконной функции в качестве начальной даты
В этом примере в качестве аргумента startdate используется агрегатная оконная функция.