SQL — Урок 13. Функции даты и времени
Функции CURDATE() и NOW() удобно использовать для добавления в базу данных записей, использующих текущее время. В нашем магазине все поставки и продажи используют текущее время. Поэтому для добавления записей о поставах, и продажах удобно использовать функцию CURDATE(). Например, пусть в наш магазин пришел товар, давайте добавим информацию об этом в таблицу Поставка (incoming):
INSERT INTO incoming (id_vendor, date_incoming) VALUES (‘2’, curdate());
SELECT id_vendor, date_incoming FROM incoming;
Предположим, мы ошиблись при вводе даты для первого поставщика, давайте уменьшим его дату на одни сутки:
SELECT id_vendor, ADDDATE(date_incoming, INTERVAL -1 DAY) FROM incoming WHERE id_vendor=1;
В качестве значения value могут выступать не только дни, но и недели (WEEK), месяцы (MONTH), кварталы (QUARTER) и годы (YEAR). Давайте для пример уменьшим дату поставки для второго поставщика на 1 неделю:
SELECT id_vendor, ADDDATE(date_incoming, INTERVAL -1 WEEK) FROM incoming WHERE id_vendor=2;
В нашей таблице Поставки (incoming) мы использовали для столбца Дата поставки (date_incoming) тип date. Как вы помните из урока 2, этот тип данных предназначен для хранения только даты. А вот если бы мы использовали тип datatime, то у нас отображалась бы не только дата, но и время. Тогда мы могли бы использовать функцию ADDDATE и для времени. В качестве значения value в этом случае могут выступать секунды (SECOND), минуты (MINUTE), часы (HOUR) и их комбинации:
минуты и секунды (MINUTE_SECOND),
часы, минуты и секунды (HOUR_SECOND),
часы и минуты (HOUR_MINUTE),
дни, часы, минуты и секунды (DAY_SECOND),
дни, часы и минуты (DAY_MINUTE),
дни и часы (DAY_HOUR),
года и месяцы (YEAR_MONTH).
Например, давайте к дате 15 апреля 2011 года две минуты первого прибавим 2 часа 45 минут:
SELECT ADDDATE(‘2011-04-15 00:02:00′, INTERVAL ’02:45’ HOUR_MINUTE);
SELECT SUBDATE(‘2011-04-15 00:02:00′, INTERVAL ’23:53’ HOUR_MINUTE);
SELECT PERIOD_ADD(201102, 2);
FRAC_SECOND — микросекунды
SECOND — секунды
MINUTE — минуты
HOUR — часы
DAY — дни
WEEK — недели
MONTH — месяцы
QUARTER — кварталы
YEAR — годы
SELECT TIMESTAMPADD(DAY, 2, ‘2011-04-02’);
SELECT TIMEDIFF(‘2011-04-17 23:50:00’, ‘2011_04-16 14:50:00’);
SELECT date_incoming, CURDATE(), DATEDIFF(CURDATE(), date_incoming) FROM incoming WHERE id_vendor=1;
SELECT PERIOD_DIFF(201108, 201001);
FRAC_SECOND — микросекунды
SECOND — секунды
MINUTE — минуты
HOUR — часы
DAY — дни
WEEK — недели
MONTH — месяцы
QUARTER — кварталы
YEAR — годы
SELECT TIMESTAMPDIFF(DAY, ‘2011-04-02’, ‘2011-04-17’) AS days, TIMESTAMPDIFF(HOUR, ‘2011-04-16 20:14:00’, ‘2011-04-17 23:58:20’) AS houres;
Что делают в SQL текущая дата и другие функции даты и времени
Функция текущей даты SQL CURDATE() и её аналоги CURRENT_DATE() и CURRENT_DATE среди других функций даты и времени применяются наиболее часто из-за широких возможностей, обеспечиваемых ими для анализа данных. Знакомство с функциями даты и времени начнём с разбора практических примеров, демонстрирующих возможности функции текущей даты. А затем перейдём к остальным функциям даты и времени, соблюдая для удобства их классификацию по назначению.
Функция текущей даты SQL, её возможности
Функция текущей даты CURDATE() возвращает значение текущей даты в формате ‘YYYY-MM-DD’ и ‘YYYYDDMM’. Вычисляя несколькими способами (их как раз и разберём в этом параграфе) разницу значений дат, можно определить такие важные значения, как возраст человека, его трудовой стаж, продолжительность различных процессов и явлений и многое другое.
В примерах работаем с базой данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Это уже база с большим объёмом данных по сравнению с примерами ко многим другим темам нашего курса. Поэтому не будем приводить строки данных таблиц и таблицы результатов запросов. Однако это будет компенсировано подробным разбором логики построения запросов, которые, надо признать, имеют достаточно высокую сложность.
Пример 1. Сформировать список актеров старше 70 лет. Пишем следующий запрос:
SELECT FName, LName, BirthDate FROM ACTOR WHERE TIMESTAMPDIFF ( YEAR , BirthDate, CURDATE() ) > 70
В этом запросе вычисляется разница между текущей датой CURDATE() и датой рождения актёра BirthDate, содержащейся в таблице ACTOR. Для вычисления разницы применена функция TIMESTAMPDIFF(). Ключевое слово YEAR — задаёт единицу измерения — в годах интервала между датами. Вычисленное значение и результат его сравнения с числом 70 вполне пригодны в качестве условия выборки в секции WHERE. Следует учесть, что функция TIMESTAMPDIFF() существует лишь в MySQL. В других диалектах SQL для этого есть функция DATEDIFF, а для задания единицы измерения применяются различные ключевые слова в различных вариантах написания.
Для вычисления разницы дат можно использовать и оператор «минус». Это сделано в следующем примере.
Пример 2. Вывести список актеров, которые не задействованы в новых постановках (в постановках последних 3 лет). Использовать CURDATE(), NOT IN. Запрос будет следующим:
SELECT fname, lname FROM actor WHERE actor_id NOT IN ( SELECT actor_id FROM team WHERE play_id IN ( SELECT play_id FROM play WHERE YEAR (premieredate) — YEAR(CURDATE() ) SELECT DISTINCT a.Actor_ID, a.FName, a.LName, CURDATE() — p1.PremiereDate AS ExpDays FROM Play p1 JOIN Team t1 ON p1.play_id = t1.play_id JOIN Actor a ON t1.actor_id = a.Actor_id WHERE t1.ACTOR_ID = a.Actor_ID ORDER BY ExpDays, a.Actor_ID DESC
В этом запросе разница между текущей датой CURDATE() и датой премьеры постановки PremiereDate из таблицы Play вычисляется как имя столбца в результирующей таблице. Поскольку эти даты имеют один и тот же формат, для вычисления разницы достаточно использовать оператор «минус». Разница вычислена. Но из таблицы Play невозможно напрямую «достучаться» до таблицы Actor, содержащей данные об актёрах. Поэтому используем соединение (JOIN) этой таблицы с таблицей Team, которая уже связана с таблицей Actor при помощи ключа Actor_ID. Соединение таблиц Team и Actor — второе в этой цепочке из трёх таблиц.
Составить SQL запросы с текущей датой самостоятельно, а затем посмотреть решения
Пример 4. Определить самого востребованного актера за последние 5 лет. Оператор JOIN использовать 2 раза. Использовать CURDATE(), LIMIT 1.
Пример 5. Определить спектакли, в которых средний возраст актеров от 20 до 30 (использовать BETWEEN, GROUP BY, AVG).
В последующих параграфах приведено большинство функций даты и времени, используемых в СУБД MySQL. А примеры использования наиболее часто применимых в MS SQL Server функций DATEDIFF и DATEADD приведены соответственно на странице 2 и странице 3.
Функции, возвращающие текущие дату, время, дату и время
CURDATE(), CURRENT_DATE(), CURRENT_DATE — возвращают текущую дату в формате ‘YYYY-MM-DD’ или YYYYDDMM в зависимости от того, вызывается функция в текстовом или числовом контексте.
CURTIME(), CURRENT_TIME(), CURRENT_TIME — возвращают текущее время суток в формате ‘hh-mm-ss’ или hhmmss в зависимости от того, вызывается функция в текстовом или числовом контексте.
NOW() — возвращает текущие дату и время формате ‘YYYY-MM-DD hh:mm:ss’ или YYYYDDMMhhmmss в зависимости от того, вызывается функция в текстовом или числовом контексте.
Функции для вычисления разницы между моментами
TIMEDIFF(param1, param2) — возвращает разницу между значениями времени, заданными параметрами param1 и param2.
DATEDIFF(param1, param2) — возвращает разницу между датами param1 и param2. Значения param1 и param2 могут иметь типы DATE или DATETIME, а при вычислении разницы используется лишь часть DATE.
PERIOD_DIFF(param1, param2) — возвращает разницу в месяцах между датами param1 и param2. Значения param1 и param2 могут быть представлены в числовом формате YYYYMM или YYMM.
TIMESTAMPDIFF(interval, param1, param2) — возвращает разницу между значениями датами param1 и param2. Значения param1 и param2 могут быть представлены в форматах ‘YYYY-MM-DD’ или ‘YYYY-MM-DD hh:mm:ss’. Единица измерения разницы задаётся параметром interval. Он может принимать значения FRAC_SECOND (микросекунды), SECOND (секунды), MINUTE (минуты), HOUR (часы), DAY (дни), WEEK (недели), MONTH (месяцы), QUARTER (кварталы), YEAR (годы).
Функции для добавления (или вычитания) некоторого значения к моменту
ADDDATE(date, INTERVAL value) — возвращает дату, к которой прибавлено значение value. Ключевое слово INTERVAL обязательно следует в запросе, после него указывается значение value, а затем единицы измерения прибавляемого значения. Ими могут быть SECOND (секунды), MINUTE (минуты), HOUR (часы), MINUTE_SECOND (минуты и секунды), HOUR_MINUTE (часы и минуты), DAY_SECOND (дни, часы минуты и секунды), DAY_MINUTE (дни, часы и минуты), DAY_HOUR (дни и часы), YEAR_MONTH (годы и месяцы).
SUBDATE(date, INTERVAL value) — вычитает из величины даты date произвольный временной интервал и возвращает результат. Ключевое слово INTERVAL обязательно следует в запросе, после него указывается значение value, а затем единицы измерения вычитаемого значения. Возможные единицы измерения — те же, что и для функции ADDDATE().
SUBTIME(datetime, time) — вычитает из величины времени datetime вида ‘YYYY-MM-DD hh:mm:ss’ произвольно заданное значение времени time и возвращает результат.
PERIOD_ADD(period, N) — добавляет N месяцев к значению даты period. Значение period должно быть представлено в числовом формате ‘YYYYMM’ или ‘YYMM’.
TIMESTAMPADD(interval, param1, param2) — прибавляет к дате и времени суток param2 в полном или кратком формате временной интервал param1, единицы измерения которого заданы параметром interval. Возможные единицы измерения — те же, что и для функции TIMESTAMPDIFF().
Функции, характеризующие момент (значение аргумента)
DATE(datetime) — извлекает из значения даты и времени суток в формате DATETIME (‘YYYY-MM-DD hh:mm:ss’) только дату, отсекая часы, минуты и секунды.
TIME(datetime) — извлекает из значения даты и времени суток в формате DATETIME (‘YYYY-MM-DD hh:mm:ss’) только время суток, отсекая дату.
TIMESTAMP(param) — принимает в качестве аргумента дату и время суток в полном или кратком формате и возвращает полный вариант в формате DATETIME (‘YYYY-MM-DD hh:mm:ss’).
DAY(date), DAYOFMONTH(date) — принимают в качестве аргумента дату, и возвращают порядковый номер дня в месяце (от 1 до 31).
DAYNAME(date) — принимает в качестве аргумента дату, и возвращает день недели в виде полного слова на английском языке.
DAYOFWEEK(date) — принимает в качестве аргумента дату, и возвращает порядкоый номер дня недели от 1 (воскресенье) до 7 (суббота).
WEEKDAY(date) — принимает в качестве аргумента дату, и возвращает порядкоый номер дня недели от 0 (понедельник) до 6 (воскресенье).
WEEK(date) — принимает в качестве аргумента дату, и возвращает номер недели в году для этой даты от 0 до 53.
WEEKOFYEAR(datetime) — возвращает порядковый номер недели в году для даты datetime от 1 до 53.
MONTH(datetime) — возвращает числовое значение месяца года от 1 до 12 для даты datetime.
MONTHNAME(datetime) — возвращает строку с названием месяца для даты datetime.
QUARTER(datetime) — возвращает значение квартала от 1 до 4 для даты datetime, которая может быть передана в формате ‘YYYY-MM-DD’ или ‘YYYY-MM-DD hh:mm:ss’.
YEAR(datetime) — возвращает год от 1000 до 9999 для даты datetime.
DAYOFYEAR(date) — возвращает порядковый номер дня в году от 1 до 366 для даты date.
HOUR(datetime) — возвращает значение часа от 0 до 23 для времени datetime.
MINUTE(datetime) — возвращает значение минут от 0 до 59 для времени datetime.
SECOND(time) — возвращает количество секунд для времени суток time, которое задаётся либо в виде строки ‘hh:mm:ss’, либо числа hhmmss.
EXTRACT(type FROM datetime) — принимает дату и время суток datetime и возвращает часть, определяемую параметром type. Значениями параметра могут быть YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
Функции для преобразования разницы в дни и секунды
TO_DAYS(date) — принимает дату date в кратком ‘YYYY-MM-DD’ или полном формате ‘YYYY-MM-DD hh:mm:ss’ и возвращает количество дней, прошедших с нулевого года.
FROM_DAYS(N) — принимает количество дней N, прошедших с нулевого года, и возвращает дату в формате ‘YYYY-MM-DD’.
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(datetime) — если параметр не указан, то возвращает количество секунд, прошедших с 00:00 1 января 1970 года. Если параметр datetime указан (в кратком ‘YYYY-MM-DD’ или полном формате ‘YYYY-MM-DD hh:mm:ss’), то возвращает разницу в секундах между 00:00 1 января 1970 года и датой datetime.
FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp, format) — принимает количество секунд, прошедших с 00:00 1 января 1970 года и возвращает дату и время суток в виде строки ‘YYYY-MM-DD hh:mm:ss’ или в виде числа YYYYDDMMhhmmss в зависимости от того, вызвана функция в строковом или числовом контексте.
TIME_TO_SEC(time) — принимает время суток time в формате ‘hh:mm:ss’ и возвращает количество секунд, прошедших с начала суток.
SEC_TO_TIME(seconds) — принимает количество секунд seconds, прошедших с начала суток и возвращает время в формате ‘hh:mm:ss’ или hhmmss в зависимости от того, вызвана функция в строковом или числовом контексте.
MAKEDATE(year, dayofyear) — принимает год year, номер дня в году dayofyear и возвращает дату в формате ‘YYYY-MM-DD’.
MAKETIME(hour, minute, second) — принимает часы hour, минуты minute и секунды second и возвращает время суток в формате ‘hh:mm:ss’.
- Функция текущей даты SQL, её возможности
- Функции, возвращающие текущие дату, время, дату и время
- Функции для вычисления разницы между моментами
- Функции для добавления некоторого значения к моменту
- Функции, характеризующие момент (значение аргумента)
- Функции для преобразования разницы в дни и секунды
- Примеры с функцией DATEDIFF MS SQL Server
- Примеры с функцией DATEADD MS SQL Server
Как выполнить запрос SQL по текущей дате если тип даты datetime?
Есть столбец DATE с типом DATETIME , нужно выбрать все записи с сегодняшней датой.
select date from table where date=getdate() не работает. если ставить операторы меньше или равно либо between тогда работает.
- Вопрос задан более трёх лет назад
- 995 просмотров
2 комментария
Простой 2 комментария
heavybrain @heavybrain Автор вопроса
так же если просто написать например =’20190808′ тоже не работает, через trunkdate работает
Для понимания.
Тип данных date — это 20190101
datetime — 20190101 00:00:00.000
Соответственно, если ты просто хочешь выбрать по getdate(), то ты получаешь 20190101 14:20:21.333, например, а такого нет.
Поэтому, тебе нужно приводить к одному виду данные, либо же использовать период.
Как вывести текущую дату в sql
Функция EXTRACT извлекает из даты и времени какой-то определенный компонент. Ее формальный синтаксис:
EXTRACT(unit FROM datetime)
Значение datetime представляет исходную дату и (или) время, а значение unit указывает, какой компонент даты или времени будет извлекаться. Параметр unit может представлять одно из следующих значений:
- SECOND (секунды)
- MINUTE (минуты)
- HOUR (час)
- DAY (день)
- MONTH (месяц)
- YEAR (год)
- MINUTE_SECOND (минуты и секунды)
- HOUR_MINUTE (часы и минуты)
- DAY_HOUR (день и часы)
- YEAR_MONTH (год и месяц)
- HOUR_SECOND (часы, минуты и секунды)
- DAY_MINUTE (день, часы и минуты)
- DAY_SECOND (день, часы, минуты и секунды)
Примеры вызова функции:
EXTRACT( SECOND FROM ‘2018-05-25 21:25:54’)
EXTRACT( MINUTE FROM ‘2018-05-25 21:25:54’)
EXTRACT( HOUR FROM ‘2018-05-25 21:25:54’)
EXTRACT( DAY FROM ‘2018-05-25 21:25:54’)
EXTRACT( MONTH FROM ‘2018-05-25 21:25:54’)
EXTRACT( YEAR FROM ‘2018-05-25 21:25:54’)
EXTRACT( MINUTE_SECOND FROM ‘2018-05-25 21:25:54’)
EXTRACT( DAY_HOUR FROM ‘2018-05-25 21:25:54’)
EXTRACT( YEAR_MONTH FROM ‘2018-05-25 21:25:54’)
EXTRACT( HOUR_SECOND FROM ‘2018-05-25 21:25:54’)
EXTRACT( DAY_MINUTE FROM ‘2018-05-25 21:25:54’)
EXTRACT( DAY_SECOND FROM ‘2018-05-25 21:25:54’)
Функции для манипуляции с датами
Ряд функций позволяют производить операции сложения и вычитания с датами и временем:
- DATE_ADD(date, INTERVAL expression unit) возвращает объект DATE или DATETIME, который является результатом сложения даты date с определенным временным интервалом. Интервал задается с помощью выражения INTERVAL expression unit , где INTERVAL предоставляет ключевое слово, expression — количество добавляемых к дате единиц, а unit — тип единиц (часы, дни и т.д.) Параметр unit может иметь те же значения, что и в функции EXTRACT, то есть DAY, HOUR и т.д.
- DATE_SUB(date, INTERVAL expression unit) возвращает объект DATE или DATETIME, который является результатом вычитания из даты date определенного временного интервала
- DATEDIFF(date1, date2) возвращает разницу в днях между датами date1 и date2
- TO_DAYS(date) возвращает количество дней с 0-го года
- TIME_TO_SEC(time) возвращает количество секунд, прошедших с момента полуночи
DATE_ADD(‘2018-05-25’, INTERVAL 1 DAY)
DATE_ADD(‘2018-05-25’, INTERVAL 3 MONTH)
DATE_ADD(‘2018-05-25 21:31:27’, INTERVAL 4 HOUR)
DATE_SUB(‘2018-05-25’, INTERVAL 4 DAY)
Форматирование дат и времени
- DATE_FORMAT(date, format) возвращает объект DATE или DATETIME, отформатированный с помощью шаблона format
- TIME_FORMAT(date, format) возвращает объект TIME или DATETIME, отформатированный с помощью шаблона format
Обе функции в качестве второго параметра принимают строку форматирования или шаблон, который показывает, как оформатировать значение. Этот шаблон может принимать следующие значения:
- %m : месяц в числовом формате 01..12
- %с : месяц в числовом формате 1..12
- %M : название месяца (January. December)
- %b : аббревиатура месяца (Jan. Dec)
- %d : день месяца в числовом формате 00..31
- %e : день месяца в числовом формате 0..31
- %D : номер дня месяца с суффиксом (1st, 2nd, 3rd. )
- %y : год в виде двух чисел
- %Y : год в виде четырех чисел
- %W : название дня недели (Sunday. Saturday)
- %a : аббревиатура дня недели (Sun. Sat)
- %H : час в формате 00..23
- %k : час в формате 0..23
- %h : час в формате 01..12
- %l : час в формате 1..12
- %i : минуты в формате 00..59
- %r : время в 12-ти часовом формате (hh:mm:ss AM или PM)
- %T : время в 24-ти часовом формате (hh:mm:ss)
- %S : секунды в формате 00..59
- %p : AM или PM
DATE_FORMAT(‘2018-05-25 21:25:54’, ‘%d %M %Y’)
DATE_FORMAT(‘2018-05-25 21:25:54’, ‘%r’)
TIME_FORMAT(‘2018-05-25 21:25:54’, ‘%H:%i:%S’)
В качестве примера использования функций найдем заказы, которые были сделаны 5 дней назад:
SELECT * FROM Orders WHERE DATEDIFF(CURDATE(), CreatedAt) = 5;