mysql разность дат в днях
Функция DATEDIFF стр. 2
Задача. Посчитать число минут в интервале между двумя датами – ‘2011-10-07 23:43:00’ и ‘2011-10-08 01:23:00’
SQL Server
Встроенная функция DATEDIFF решает проблему:
Результат – 100 минут.
В запросе используется стандартное представление даты (ISO) в виде текстовой строки ‘yyyy-mm-ddThh:mm:ss’, которое не зависит ни от локальных настроек сервера, ни и от самого сервера.
MySQL
Один день мы получим даже в случае, если интервал между датами составляет все одну секунду:
PostgreSQL
В PostgreSQL нет функции, подобной DATEDIFF (SQL Server) или TIMESTAMPDIFF (MySQL). Поэтому для решения задачи можно применить следующую последовательность действий:
Для получения интервала можно взять разность двух значений темпорального типа, При этом требуется явное преобразование типа:
Для получения числа секунд в интервале воспользуемся функцией
Значение интервала представим последним способом как наиболее коротким. Для получения окончательного решения задачи поделим полученный результат на 60:
В данном случае мы имеем результат, выраженный целым числом, поскольку оба значения времени в задаче не содержали секунд. В противном случае, будет получено десятичное число, например: 99.75:
Oracle
Oracle тоже не имеет в своем арсенале функции типа DATEDIFF. Кроме того, Oracle не поддерживает стандартное текстовое представление даты/времени.
Мы можем посчитать интервал в минутах, приняв во внимание, что разность дат (значений типа date ) в Oracle дает в результате число дней (суток). Тогда для вычисления интервала в минутах нужно просто разность дат умножить на 24 (число часов в сутках), а затем на 60 (число минут в часе):
Подобным образом можно получить и другие временные интервалы.
6 запросов для MySQL
Искусство в построении запросов с использованием Structured Query Language (язык структурированных запросов) заключается в создании правильных, эффективных вопросов и команд для базы данных. В запросах SELECT можно использовать ключевые слова JOIN, WHERE и HAVING для уточнения результатов, GROUP BY для комбинирования результатов в легко анализируемые блоки, и UNION для комбинирования результатов нескольких запросов. Команды INSERT, DELETE и UPDATE могут использовать JOIN. Запрос INSERT … SELECT вставляет результаты в другую таблицу. В запросах DELETE и UPDATE можно использовать ключевое слово WHERE, чтобы указать область действия.
Ниже приводятся 6 запросов для MySQL.
1. Возраст в годах
2. Разница между двумя датами
Находим разницу между двумя датами, выраженную в секундах, минутах, часах или днях. Если dt1 и dt2 значения дат в формате ‘yyyy-mm-dd hh:mm:ss’, то количество секунд между dt1 и dt2 вычисляется:
3. Выводим значение, которое встречается в столбце N раз
4. Вычисляем количество рабочих дней между двумя датами
Простейшим способом вычислить количество рабочих дней между двумя датами является использование таблицы календаря calendar со столбцами даты d и логического флага праздников holiday для всех дней во всех возможных годах. Затем используется соответствующий запрос, который возвращает количество рабочих дней между двумя датами Start и Stop включительно:
5. Находим основной ключ для таблицы
6. Определяем, насколько велика таблица
Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: www.codeforest.net/6-useful-mysql-queries
Перевел: Сергей Фастунов
Урок создан: 15 Марта 2011
Просмотров: 39311
Правила перепечатки
5 последних уроков рубрики «Разное»
Как выбрать хороший хостинг для своего сайта?
Выбрать хороший хостинг для своего сайта достаточно сложная задача. Особенно сейчас, когда на рынке услуг хостинга действует несколько сотен игроков с очень привлекательными предложениями. Хорошим вариантом является лидер рейтинга Хостинг Ниндзя — Макхост.
Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.
Разработка веб-сайтов с помощью онлайн платформы Wrike
20 ресурсов для прототипирования
Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.
Топ 10 бесплатных хостингов
Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.
Разница между двумя датами в MySQL
Как рассчитать разницу между двумя датами в формате YYYY-MM-DD hh: mm: ss и получить результат в секундах или миллисекундах?
Таким образом, вы можете использовать TIMESTAMPDIFF для своих целей.
Если вы работаете со столбцами DATE (или можете преобразовать их в столбцы даты), попробуйте DATEDIFF (), а затем умножьте на 24 часа, 60 минут и 60 секунд (поскольку DATEDIFF возвращает diff в днях). Из MySQL:
Получите разницу дат в днях, используя DATEDIFF
SELECT ( DATEDIFF(‘1993-02-20′,’1993-02-19’)*( 24*60*60) )AS ‘seccond’;
Или вы можете использовать функцию TIMEDIFF
Эта функция берет разницу между двумя датами и показывает ее в формате даты гггг-мм-дд. Все, что вам нужно, это выполнить приведенный ниже код и затем использовать функцию. После выполнения вы можете использовать его следующим образом
ОК, это не совсем то, что попросил ОП, но это то, что я хотел сделать 🙂
Этот код вычисляет разницу между двумя датами в формате гггг ММ дд.
Если у вас есть дата, хранящаяся в текстовом поле в виде строки, вы можете реализовать этот код, он будет извлекать список прошлых дней в неделю, месяц или год сортировки:
Этот запрос отображает запись между днями, которые вы установили там, например: ниже за последние 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
Изучаем MySQL: работа с датами и временем
В этой статье мы рассмотрим основы работы с датой и временем в MySQL.
Формат даты и времени
MySQL date format поддерживает несколько форматов даты и времени. Их можно определить следующим образом:
DATE — хранит значение даты в виде ГГГГ-ММ-ДД. Например, 2008-10-23.
DATETIME — хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон дат и времени: 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP — похож на DATETIME с некоторыми различиями в зависимости от версии MySQL и режима, в котором работает сервер.
Создание полей даты и времени
Форматы даты и времени
Наиболее часто используемым разделителем для дат является тире ( — ), а для времени — двоеточие ( : ). Но мы можем использовать любой символ, или вообще не добавлять никакого символа.
Например, все следующие форматы являются правильными:
Функции даты и времени
MySQL содержит множество функций, которые используются для обработки даты и времени. В приведенной ниже таблице представлен список наиболее часто используемых функций:
| Функция | Описание |
| ADDDATE() | Добавляет дату. |
| ADDTIME() | Добавляет время. |
| CONVERT_TZ() | Конвертирует из одного часового пояса в другой. |
| CURDATE() | Возвращает текущую дату. |
| CURTIME() | Возвращает текущее системное время. |
| DATE_ADD() | Добавляет одну дату к другой. |
| MySQL DATE FORMAT () | Задает указанный формат даты. |
| DATE() | Извлекает часть даты из даты или выражения дата-время. |
| DATEDIFF() | Вычитает одну дату из другой. |
| DAYNAME() | Возвращает день недели. |
| DAYOFMONTH() | Возвращает день месяца (1-31). |
| DAYOFWEEK() | Возвращает индекс дня недели из аргумента. |
| DAYOFYEAR() | Возвращает день года (1-366). |
| EXTRACT | Извлекает часть даты. |
| FROM_DAYS() | Преобразует номер дня в дату. |
| FROM_UNIXTIME() | Задает формат даты в формате UNIX. |
| MySQL DATE_SUB () | Вычитает одну дату из другой. |
| HOUR() | Извлекает час. |
| LAST_DAY | Возвращает последний день месяца для аргумента. |
| MAKEDATE() | Создает дату из года и дня года. |
| MAKETIME () | Возвращает значение времени. |
| MICROSECOND() | Возвращает миллисекунды из аргумента. |
| MINUTE() | Возвращает минуты из аргумента. |
| MONTH() | Возвращает месяц из переданной даты. |
| MONTHNAME() | Возвращает название месяца. |
| NOW() | Возвращает текущую дату и время. |
| PERIOD_ADD() | Добавляет интервал к месяцу-году. |
| PERIOD_DIFF() | Возвращает количество месяцев между двумя периодами. |
| QUARTER() | Возвращает четверть часа из переданной даты в качестве аргумента. |
| SEC_TO_TIME() | Конвертирует секунды в формат ‘ЧЧ:MM:СС’. |
| SECOND() | Возвращает секунду (0-59). |
| MySQL STR TO DATE () | Преобразует строку в дату. |
| SUBTIME() | Вычитает время. |
| SYSDATE() | Возвращает время, в которое была выполнена функция. |
| TIME_FORMAT() | Задает формат времени. |
| TIME_TO_SEC() | Возвращает аргумент, преобразованный в секунды. |
| TIME() | Выбирает часть времени из выражения, передаваемого в качестве аргумента. |
| TIMEDIFF() | Вычитает время. |
| TIMESTAMP() | С одним аргументом эта функция возвращает дату или выражение дата-время. С двумя аргументами возвращается сумма аргументов. |
| TIMESTAMPADD() | Добавляет интервал к дате-времени. |
| TIMESTAMPDIFF() | Вычитает интервал из даты — времени. |
| TO_DAYS() | Возвращает аргумент даты, преобразованный в дни. |
| UNIX_TIMESTAMP() | Извлекает дату-время в формате UNIX в формат, принимаемый MySQL. |
| UTC_DATE() | Возвращает текущую дату по универсальному времени (UTC). |
| UTC_TIME() | Возвращает текущее время по универсальному времени (UTC). |
| UTC_TIMESTAMP() | Возвращает текущую дату-время по универсальному времени (UTC). |
| WEEK() | Возвращает номер недели. |
| WEEKDAY() | Возвращает индекс дня недели. |
| WEEKOFYEAR() | Возвращает календарную неделю даты (1-53). |
| YEAR() | Возвращает год. |
| YEARWEEK() | Возвращает год и неделю. |
Вы можете попробовать сочетание нескольких функций в одном запросе (например, чтобы найти день недели):
Внесение значений даты и времени в столбцы таблицы
Данный запрос создает заказ для указанного элемента с датой, временем выполнения заказа, и интервалом через две недели после этого в качестве даты доставки:
Точно так же можно заказать товар с датой доставки через два месяца:
Извлечение данных по дате и времени
В MySQL мы можем отфильтровать извлеченные данные в зависимости от даты и времени. Например, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь: