Блог веб-разработчика
О разработке веб-приложений на 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
У меня есть таблица с датами, которые все произошло в ноябре месяце. Я написал этот запрос
этот запрос должен возвращать все, что произошло в месяце 11 (ноябрь), потому что это произошло до дата ‘2013-04-12’ (в декабре)
может быть, это только сравнивая дня? и не весь дата?
Created_date типа дата
формат даты по умолчанию гггг-ДД-ММ
7 ответов
вместо «2013-04-12», значение которого зависит от местной культуры, используйте «20130412», который признан инвариантным форматом культуры.
статьи запись международных инструкций Transact-SQL из документации SQL Server объясняется, как писать операторы, которые инвариантные:
приложения, использующие другие API или скрипты Transact-SQL, хранимые процедуры и триггеры, должны использовать неразделенные числовые строки. Например, ггггммдд, как 19980924.
редактировать
поскольку вы используете ADO, лучшим вариантом является параметризация запроса и передача значения даты в качестве параметра даты. Таким образом, вы полностью избегаете проблемы с форматом и получаете преимущества параметризованной производительности запросов, а также.
обновление
чтобы использовать формат ISO 8601 в литерале, необходимо указать все элементы. Цитата из раздела ISO 8601 документации datetime
чтобы использовать формат ISO 8601, необходимо указать каждый элемент в формате. Это также включает T, двоеточия (:) и период (.), которые отображаются в формате.
. доля второго компонента необязательный. Компонент времени задается в 24-часовом формате.
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 используется агрегатная оконная функция.
Разница между двумя датами в MySQL
Как вычислить разницу между двумя датами в формате YYYY-MM-DD hh: mm: ss и получить результат в секундах или миллисекундах?
13 ответов
Итак, вы можете использовать TIMESTAMPDIFF для ваших целей.
Если вы работаете со столбцами дат (или можете использовать их как столбцы дат), попробуйте DATEDIFF (), а затем умножьте на 24 часа, 60 минут, 60 секунд (поскольку DATEDIFF возвращает diff в днях). С MySQL:
получить разницу дат в днях с помощью DATEDIFF
или вы можете использовать функцию TIMEDIFF
эта функция принимает разницу между двумя датами и показывает его в формат даты гггг-ММ-ДД. Все, что вам нужно, это выполнить приведенный ниже код, а затем использовать функцию. После выполнения вы можете использовать его следующим образом
этот код вычисляет разницу между двумя датами в формате гггг мм dd.
Если у вас есть дата, хранящаяся в текстовом поле в виде строки, вы можете реализовать этот код, он будет получать список прошлых дней в неделю, месяц или год сортировки:
%d%m%Y-ваш формат даты
этот запрос отображает запись между днями, которые вы установили там, как: ниже от последних 7 дней и выше от последних 14 дней, так что это будет ваша последняя запись недели, чтобы отобразить ту же концепцию для месяца или года. Независимо от значения, которое вы предоставляете ниже даты, как: ниже от 7-дней, поэтому другое значение будет его двойным, как 14 дней. То, что мы говорим здесь, получает все записи выше за последние 14 дней и ниже за последние 7 дней. Это запись недели вы можете изменить значение на 30-60 дней в течение месяца, а также в течение года.
SQL функции даты и времени
Приветствую Вас, уважаемые читатели блога webcodius.ru. В базе данных часто требуется хранить различные данные связанные с датой и временем. Это может быть дата добавления информации, дата регистрации пользователя, время последней автоизации и другие данные. В языке SQL есть множество функций связанных с датой и временем, сегодня их и рассмотрим.
Все ниже рассмотренные функции работают с календарными типами данных.
Получение текущей даты и времени.
Чтобы получить текущую дату и время используется функция NOW ().
SELECT NOW ()
Результат: 2015-09-25 14:42:53
Для получения только текущей даты есть функция CURDATE ().
SELECT CURDATE ()
Результат: 2015-09-25
И функция CURTIME (), которая возвращает только текущее время:
SELECT CURTIME ()
Результат: 14:42:53
Функции CURDATE () и NOW () удобно использовать для добавления в базу данных записей, для которых требуется хранить дату добавления. Например, при добавлении статьи на сайт хорошо бы хранить ее дату публикации. Тогда запрос на добавление статьи в базу будет примерно таким:
INSERT INTO posts (id_post, text_post, date_publication) VALUES (1, ‘текст статьи’, NOW ());
Прибавление и вычитание дат и времени
Функция ADDDATE (date, INTERVAL value) прибавляет к дате date значение value и возвращает полученное значение. В качестве value могут выступать следующие значения:
а также их комбинации:
SELECT ADDDATE (‘2015-09-28 10:30:20’, INTERVAL 1 DAY)
Результат: 2015-09-29 10:30:20
SELECT ADDDATE (‘2015-09-28 10:30:20’, INTERVAL ‘3 1:20’ DAY_MINUTE)
Результат: 2015-10-01 11:50:20
SELECT SUBDATE (‘2015-09-28 10:30:20’, INTERVAL 20 HOUR)
Результат: 2015-09-27 14:30:20
Функция PERIOD_ADD (period, n) прибавляет к значению period n месяцев. Значение период должно быть представлено в формате YYYYMM (например сентябрь 2015 года будет 201509). Пример:
SELECT PERIOD_ADD (201509, 4)
Результат: 201601
SELECT TIMESTAMPADD (QUARTER, 1, ‘2015-09-28’)
Результат: 2015-12-28
Функция SUBTIME (date, time) вычитает из даты date время time. Пример:
SELECT SUBTIME (‘2015-09-28 10:30:20′, ’50:20:19’)
Результат: 2015-09-26 08:10:01
Вычисление интервала между датами
Функция DATEDIFF (date1, date2) вычисляет разницу в днях между двумя датами, при этом часы, минуты и секунды при указании дат игнорируются. Пример:
SELECT DATEDIFF (‘2015-09-28 00:00:20’, ‘2015-09-27 23:40:20’)
Результат: 1
С помощью этой функции легко определить сколько дней прошло с даты публикации статьи:
SELECT DATEDIFF (CURDATE (), date_publication) FROM posts WHERE id_post = 1
SELECT PERIOD_DIFF (201509, 201501)
Результат: 9
SELECT TIMESTAMPDIFF (HOUR, ‘2015-09-28 10:30:20’, ‘2015-09-28 19:50:20’)
Результат: 9
Получение различных форматов даты и времени и другой информации
Функция DATE (datetime) возвращает дату, отсекая время. Пример:
SELECT DATE (‘2015-09-28 10:30:20’)
Результат: 2015-09-28
Функция TIME (datetime) возвращает время, отсекая дату. Пример:
SELECT TIME (‘2015-09-28 10:30:20’)
Результат: 10:30:20
TIMESTAMP (‘2015-09-28’)
Результат: 2015-09-28 00:00:00
DAY (date) и DAYOFMONTH (date). Функции-синонимы, которые возвращают порядковый номер дня месяца. Пример:
SELECT DAY (‘2015-09-28’), DAYOFMONTH (‘2015-09-28’)
Результат: 28 | 28
Функции DAYNAME (date), DAYOFWEEK (date) и WEEKDAY (date). Первая функция возвращает название дня недели, вторая — номер дня недели (отсчет от 1 — воскресенье до 7 — суббота), третья также номер дня недели только другой отсчет(отсчет от 0 — понедельник, до 6 — воскресенье). Пример:
SELECT DAYNAME (‘2015-09-28’), DAYOFWEEK (‘2015-09-28’), WEEKDAY (‘2015-09-28’)
Результат: Monday 2 | 0
Функции WEEK (date) и WEEKOFYEAR (datetime). Обе функции возвращают номер недели в году, только у первой неделя начинается с воскресенья, а у второй с понедельника. Пример:
SELECT WEEK (‘2015-09-28 10:30:20’), WEEKOFYEAR (‘2015-09-28 10:30:20’)
Результат: 39 | 40
Функция MONTH (date) возвращает числовое значение месяца (от 1 до 12), а MONTHNAME (date) название месяца. Пример:
SELECT MONTH (‘2015-09-28 10:30:20’), MONTHNAME (‘2015-09-28 10:30:20’)
Результат: 9 | September
Функция QUARTER (date) возвращает номер квартала года (от 1 до 4). Пример:
SELECT QUARTER (‘2015-09-28 10:30:20’)
Результат: 3
Функция YEAR (date) возвращает значение года (от 1000 до 9999). Пример:
SELECT YEAR (‘2015-09-28 10:30:20’)
Результат: 2015
Функция DAYOFYEAR (date) возвращает порядковый номер дня в году (от 1 до 366). Прмиер:
SELECT DAYOFYEAR (‘2015-09-28 10:30:20’)
Результат: 271
Функция HOUR (datetime) возвращает значение часа (от 0 до 23). Пример:
SELECT HOUR (‘2015-09-28 10:30:20’)
Результат: 10
Функция MINUTE (datetime) возвращает значение минут (от 0 до 59). Пример:
SELECT MINUTE (‘2015-09-28 10:30:20’)
Результат: 30
Функция SECOND (datetime) возвращает значение секунд (от 0 до 59). Пример:
SELECT SECOND (‘2015-09-28 10:30:20’)
Результат: 20
SELECT EXTRACT (YEAR FROM ‘2015-09-28 10:30:20’), EXTRACT (MONTH FROM ‘2015-09-28 10:30:20’), EXTRACT (DAY FROM ‘2015-09-28 10:30:20’), EXTRACT (HOUR FROM ‘2015-09-28 10:30:20’), EXTRACT (MINUTE FROM ‘2015-09-28 10:30:20’), EXTRACT (SECOND FROM ‘2015-09-28 10:30:20’)
Результат: 2015 | 9 | 28 | 10 | 30 | 20
Взаимообратные функции TO_DAYS (date) и FROM_DAYS (n). Первая преобразует дату в количество дней, прошедших с нулевого года. Вторая, наоборот, принимает число дней, прошедших с нулевого года и преобразует их в дату. Пример:
SELECT TO_DAYS (‘2015-09-28 10:30:20’), FROM_DAYS (736234)
Результат: 736234 | 2015-09-28
Взаимообратные функции UNIX_TIMESTAMP (date) и FROM_UNIXTIME (n). Первая преобразует дату в количество секунд, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд, с 1 января 1970 года и преобразует их в дату. Пример:
SELECT UNIX_TIMESTAMP (‘2015-09-28 10:30:20’), FROM_UNIXTIME (1443425420)
Результат: 1443425420 | 2015-09-28 10:30:20
Взаимообратные функции TIME_TO_SEC (time) и SEC_TO_TIME (n). Первая преобразует время в количество секунд, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время. Пример:
SELECT TIME_TO_SEC (’10:30:20′), SEC_TO_TIME (37820)
Результат: 37820 | 10:30:20
Функция MAKEDATE (year, n) принимает год year и номер дня в году n и преобразует их в дату. Пример:
SELECT MAKEDATE (2015, 271)
Результат: 2015-09-28