SQL функції дати та часу. Функції Transact-SQL Функції дати та часу SQL
SQL робота з датами- настільки важлива, що без знання основних sql операторів в жодному проекті не можна обійтися. Як не крути, але у всіх сервісах існує потреба роботи з часом. Як правило, це обчислення періодів з однієї по іншу дату, наприклад виведення списку користувачів, що зареєструвалися, за рік, місяць, день, годину.Я хочу привести ряд рішень на завдання, що часто зустрічаються, по роботі з датами в SQL, з якими сам щодня стикався, сподіваюся, це буде актуально і корисно для вас.
Як отримати поточну дату в SQL
WHERE date = CURDATE()
або інший варіант
WHERE date = STR_TO_DATE(now(), "%Y-%m-%d")
Додати до дати одну годину в SQL
DATE_ADD("2013-03-30", INTERVAL 1 HOUR)
Додати до дати один день у SQL
DATE_ADD("2013-03-30", INTERVAL 1 DAY)
Аналогічно можна додавати будь-яку кількість днів до поточної дати.
Додати до дати один місяць у SQL
DATE_ADD("2013-03-30", INTERVAL 1 MONTH)
Аналогічно можна додавати будь-яку кількість місяців до поточної дати.
Отримати вчорашній день у SQL
DATE_ADD(CURDATE(), INTERVAL -1 DAY)
або
DATE_SUB(CURDATE(), INTERVAL 1 DAY)
Отримати дату початку поточного тижня в SQL
ця одна з найскладніших на перший погляд завдань, але вирішується дуже просто
CURDATE()-WEEKDAY(CURDATE());
Отримати вибірку з цього понеділка до поточного дня тижня в SQL
Отримати вибірку з першого числа поточного місяця до поточного дня тижня в SQL
WHERE (date BETWEEN (CURDATE()-WEEKDAY(CURDATE())) AND CURDATE())
Як отримати дату народження користувача в SQL Знайти всіх користувачів у яких день народження в наступному місяці в SQL Крім вищенаведених кейсів по роботі з датами SQL, рекомендую ознайомитися з документацією по наступним операторам: Робота з датами в SQl, як виявляється не така складна, і тепер замість того, щоб обчислювати періоди засобами PHP, можна робити це ще на етапі виконання SQL запиту і отримувати необхідну вибірку даних. Вітаю Вас, шановні читачі блогу сайт. У базі даних часто потрібно зберігати різні дані пов'язані з датою та часом. Це може бути дата додавання інформації, дата реєстрації користувача, час останньої автозйомки та інші дані. У мові SQLє безліч функцій пов'язаних з датою та часом, сьогодні їх і розглянемо. Усі нижче розглянуті функції працюють із календарними типами даних. Щоб отримати поточну дату та часвикористовується функція NOW (). SELECT NOW () Для отримання тільки поточної датиє функція CURDATE (). SELECT CURDATE() І функція CURTIME (), яка повертає тільки поточний час: SELECT CURTIME () Функції 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) SELECT ADDDATE ("2015-09-28 10:30:20", INTERVAL "3 1:20" DAY_MINUTE) Функція SUBDATE (date, INTERVAL value)виробляє відніманнязначення value з дати date. Приклад: SELECT SUBDATE ("2015-09-28 10:30:20", INTERVAL 20 HOUR) Функція PERIOD_ADD (period, n) додаєдо значення period n місяців. Значення період має бути представлено у форматі YYYYMM (наприклад, вересень 2015 року буде 201509). Приклад: SELECT PERIOD_ADD (201509, 4) Функція TIMESTAMPADD (interval, n, date) додає до дати date часовий інтервал n, значення якого задаються параметром interval. Можливі значення параметра interval: SELECT TIMESTAMPADD (QUARTER, 1, "2015-09-28") Функція SUBTIME (date, time) віднімає з дати date час time. Приклад: SELECT SUBTIME ("2015-09-28 10:30:20", "50:20:19") Функція TIMEDIFF (date1, date2) обчислює різницю в годинах, хвилинах та секундахміж двома датами date1 та date2 . Приклад: SELECT TIMEDIFF ("2015-09-28 10:30:20", "2015-09-29 10:30:20") Функція DATEDIFF (date1, date2)обчислює різницю в дняхміж двома датами, години, хвилини і секунди при вказівці дат ігноруються. Приклад: SELECT DATEDIFF ("2015-09-28 00:00:20", "2015-09-27 23:40:20") За допомогою цієї функції легко визначити, скільки днів пройшло з дати публікації статті: SELECT DATEDIFF (CURDATE(), date_publication) FROM posts WHERE id_post = 1 Функція PERIOD_DIFF (period1, period2)обчислює різницю в місяцяхміж двома датами. Дати мають бути представлені у форматі YYYYMM. Наприклад, дізнаємося скільки місяців пройшло з січня 2015 по вересень 2015: SELECT PERIOD_DIFF (201509, 201501) Функція TIMESTAMPDIFF (interval, date1, date2)обчислює різницю між датами date2 та date1 в одиницях зазначених у параметрі interval. При цьому interval може приймати такі значення: SELECT TIMESTAMPDIFF (HOUR, "2015-09-28 10:30:20", "2015-09-28 19:50:20") Функція DATE (datetime)повертає дату, відтинаючи час. Приклад: SELECT DATE ("2015-09-28 10:30:20") Функція TIME (datetime)повертає час, відтинаючи дату. Приклад: SELECT TIME ("2015-09-28 10:30:20") Функція TIMESTAMP (date)повертає повний формат з часомдати date . Приклад: TIMESTAMP ("2015-09-28") DAY (date)і DAYOFMONTH (date). Функції-синоніми, які повертають порядковий номер дня місяця. Приклад: SELECT DAY ("2015-09-28"), DAYOFMONTH ("2015-09-28") Функції DAYNAME (date),DAYOFWEEK (date)і WEEKDAY (date). Перша функція повертає назва дня тижня, друга - номер дня тижня(відлік від 1 - неділя до 7 - субота), третя також номер дня тижня тільки інший відлік (відлік від 0 - понеділок, до 6 - неділя). Приклад: SELECT DAYNAME (2015-09-28), DAYOFWEEK (2015-09-28), WEEKDAY (2015-09-28) Функції WEEK (date)і WEEKOFYEAR (datetime). Обидві функції повертають номер тижня на рік, Тільки в першій тиждень починається з неділі, а в другій з понеділка. Приклад: SELECT WEEK (2015-09-28 10:30:20), WEEKOFYEAR (2015-09-28 10:30:20) Функція MONTH (date)повертає числове значення місяця(від 1 до 12), а MONTHNAME (date) назва місяця. Приклад: SELECT MONTH ("2015-09-28 10:30:20"), MONTHNAME ("2015-09-28 10:30:20") Функція QUARTER (date)повертає номер кварталуроку (від 1 до 4). Приклад: SELECT QUARTER ("2015-09-28 10:30:20") Функція YEAR (date)повертає значення року(Від 1000 до 9999). Приклад: SELECT YEAR ("2015-09-28 10:30:20") Функція DAYOFYEAR (date)повертає порядковий номер дняна рік (від 1 до 366). Прмієр: SELECT DAYOFYEAR ("2015-09-28 10:30:20") Функція HOUR (datetime)повертає значення години(Від 0 до 23). Приклад: SELECT HOUR ("2015-09-28 10:30:20") Функція MINUTE (datetime)повертає значення хвилин(Від 0 до 59). Приклад: SELECT MINUTE ("2015-09-28 10:30:20") Функція SECOND (datetime)повертає значення секунд(Від 0 до 59). Приклад: SELECT SECOND ("2015-09-28 10:30:20") Функція EXTRACT (type FROM date)повертає частину дати date, що визначається параметром type . Приклад: 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") Взаємозворотні функції TO_DAYS (date)і FROM_DAYS (n). Перша перетворює дату на кількість днівз нульового року. Друга, навпаки, приймає число днів, що пройшли з нульового року та перетворює їх на дату. Приклад: SELECT TO_DAYS ("2015-09-28 10:30:20"), FROM_DAYS (736234) Взаємозворотні функції UNIX_TIMESTAMP (date)і FROM_UNIXTIME (n). Перша перетворює дату на кількість секунд, що відбулися з 1 січня 1970 року. Друга, навпаки, приймає число секунд, з 1 січня 1970 року та перетворює їх на дату. Приклад: SELECT UNIX_TIMESTAMP ("2015-09-28 10:30:20"), FROM_UNIXTIME (1443425420) Взаємозворотні функції TIME_TO_SEC (time)і SEC_TO_TIME (n). Перша перетворює час на кількість секунд, що минули від початку доби Друга, навпаки, приймає число секунд з початку доби і перетворює їх у час. Приклад: SELECT TIME_TO_SEC ("10:30:20"), SEC_TO_TIME (37820) Функція MAKEDATE (year, n)приймає рік року та номер дня на рік n і перетворює їх на дату. приклад. Стандарт SQL-92 специфікує лише функції, що повертають системну дату/час. Наприклад, функція CURRENT_TIMESTAMP повертає одразу і дату, і час. Плюс є функції що повертають щось одне. DATEADD ( datepart , number, date) Ця функція повертає значення типу datetime, що виходить додаванням до дати dateкількості інтервалів типу datepart, рівного number. Наприклад, ми можемо до заданої дати додати будь-яке число років, днів, годин, хвилин тощо. Допустимі значення аргументу datepartнаведені нижче та взяті з BOL. DATEDIFF ( datepart , startdate , enddate) Функція повертає інтервал часу, що пройшов між двома часовими відмітками - startdate(початкова позначка) та enddate(Кінець). Цей інтервал може бути виміряний у різних одиницях. Можливі варіанти визначаються аргументом datepartі перераховані вище стосовно функції DATEADD.
DATEPART ( datepart , date) Ця функція повертає ціле число, що є зазначеною аргументом datepartчастина заданої другим аргументом дати ( date).
Тепер ми повинні порівняти, чи час прильоту перевищує час вильоту. Якщо це так, відняти з першого друге, щоб отримати тривалість рейсу. В іншому випадку до різниці потрібно додати одну добу (24 * 60 = 1440 хвилин). DATENAME ( datepart , date) Ця функція повертає символьне уявлення складової ( datepart) зазначеної дати ( date). Аргумент, що визначає складову дати, може приймати одне із значень, перерахованих у наведеній вище таблиці. @@DATEFIRST повертає число, яке визначає перший день тижня, встановлений для поточної сесії. При цьому 1 відповідає понеділку, а 7 відповідно неділі. Тобто. якщо SELECT CURDATE(), CURTIME(), NOW(); INSERT INTO incoming (id_vendor, date_incoming) VALUES ("2", curdate()); SELECT id_vendor, date_incoming FROM incoming; 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 - мікросекунди SELECT TIMESTAMPADD(DAY, 2, "2011-04-02"); SELECT TIMEDIFF("2011-04-17 23:50:00", "2011_04-16 14:50:00"); SELECT PERIOD_DIFF(201108, 201001); FRAC_SECOND - мікросекунди 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; SELECT SUBTIME("2011-04-18 23:17:00", "02:15:30"); SELECT DATE("2011-04-15 00:03:20"); SELECT TIME("2011-04-15 00:03:20"); SELECT TIMESTAMP("2011-04-17"); SELECT DAY("2011-04-17"), DAYOFMONTH("2011-04-17"); SELECT DAYNAME("2011-04-17"), DAYOFWEEK("2011-04-17"), WEEKDAY("2011-04-17"); SELECT WEEK("2011-04-17"), WEEKOFYEAR("2011-04-17 23:40:00"); SELECT MONTH("2011-04-17"), MONTHNAME("2011-04-17"); SELECT QUARTER("2011-04-17"); SELECT YEAR("2011-04-17"); SELECT DAYOFYEAR("2011-04-17"); SELECT HOUR("2011-04-17 18:20:03"); SELECT TIME_TO_SEC("22:10:30"), SEC_TO_TIME(45368); SELECT MAKEDATE(2011, 120);
SELECT name, birth, CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(birth)) - (RIGHT(CURRENT_DATE,5)
SELECT name, birth FROM user WHERE MONTH(birth) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
або інший варіант
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(NOW()), 12) + 1;
NOW()– Повертає поточну дату та час.
CURDATE()– Повертає поточну дату.
CURTIME()– Повертаємо поточний час.
DATE()– Складається із двох частин дати та часу.
EXTRACT()– Повертає значення дати/часу.
DATE_ADD()– Додає до вибірки вказану кількість днів/хв/годин тощо.
DATE_SUB()– Вичитуємо вказаний інтервал від дати.
DATEDIFF()– Повертає значення часу між двома датами.
DATE_FORMAT()– Функція різного виведення інформації про час.Отримання поточної дати та часу.
Результат: 2015-09-25 14:42:53
Результат: 2015-09-25
Результат: 14:42:53Додаток та віднімання дат і часу
Результат: 2015-09-29 10:30:20
Результат: 2015-10-01 11:50:20
Результат: 2015-09-27 14:30:20
Результат: 201601
Результат: 2015-12-28
Результат: 2015-09-26 08:10:01Обчислення інтервалу між датами
Результат: -24:10:00
Результат: 1
Результат: 9
Результат: 9Отримання різних форматів дати та часу та іншої інформації
Результат: 2015-09-28
Результат: 10:30:20
Результат: 2015-09-28 00:00:00
Результат: 28 | 28
Результат: Monday 2 | 0
Результат: 39 | 40
Результат: 9 | September
Результат: 3
Результат: 2015
Результат: 271
Результат: 10
Результат: 30
Результат: 20
Результат: 2015 | 9 | 28 | 10 | 30 | 20
Результат: 736234 | 2015-09-28
Результат: 1443425420 | 2015-09-28 10:30:20
Результат: 37820 | 10:30:20
Природно, через таку обмеженість реалізації мови розширюють стандарт за рахунок додавання функцій, що полегшує роботу користувачів з даними цього типу. Тут ми розглянемо функції обробки дати/часу T-SQL.Функція DATEADD
Синтаксис
Нехай сьогодні 23/01/2004, і ми хочемо дізнатися, який день буде за тиждень. Ми можемо написати тому, що дрібна частина значення аргументу datepartвідкидається, і ми отримаємо 0 замість однієї четвертої та, як наслідок, поточний день.
Крім того, ми можемо використовувати замість CURRENT_TIMESTAMPфункцію T-SQL GETDATE()з тим самим ефектом. Наявність двох ідентичних функцій підтримується, мабуть, в очікуванні подальшого розвитку стандарту.
приклад
(Схема 4). Визначити, який буде день за тиждень після останнього польоту.
Використання подзапроса як аргумент допустимо, т.к. це підзапит повертає ЄДИНЕ значення типу datetime.
SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip))
Функція DATEDIFF
Синтаксис
приклад
(Схема 4). Визначити кількість днів, що пройшли між першим та останнім скоєними рейсами.
По-перше, для рейсів, що вилітають в один день, а прилітають наступного, обчислене таким способом значення буде неправильним. По-друге, ненадійно робити будь-які припущення щодо дня, який є тільки через необхідність відповідати типу datetime.
Але як визначити, що літак приземлився наступного дня? Тут допомагає опис предметної області, де йдеться, що політ неспроможна продовжуватися понад добу. Отже, якщо час прильоту не більше, ніж час вильоту, цей факт має місце. Тепер друге питання: як порахувати тільки час, з яким би днем він не стояв?
Тут може допомогти функція T-SQL DATEPART.
Функція DATEPART
Синтаксис
Список допустимих значень аргументу datepart, описаний вище в цьому розділі, доповнюється ще одним значенням
Зауважимо, що повертається функцією DATEPARTзначення в цьому випадку (номер дня тижня) залежить від установок, які можна змінити за допомогою оператора SET DATEFIRST, що встановлює перший день тижня Для когось понеділок – день важкий, а для когось – неділя. До речі, останнє значення приймається за умовчанням.
Однак повернемось до нашого прикладу. У припущенні, що час вильоту/прильоту є кратним хвилині, ми можемо визначити його як суму годин і хвилин. Оскільки функції дати/часу працюють з цілими значеннями, наведемо результат до найменшого інтервалу - хвилин. Отже, час вильоту рейсу 1123 у хвилинах
Тут, щоб не повторювати довгі конструкції в операторі CASE, використано підзапит. Звичайно, результат вийшов досить громіздким, зате абсолютно коректним у світлі зроблених до цього зауважень. SELECT CASE WHEN time_dep>=time_arr THEN time_arr-time_dep+1440 ELSE time_arr-time_dep END dur FROM
(SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep, DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr FROM trip WHERE trip_no=1123
) tm
приклад
(4 Схема). Визначити дату та час вильоту рейсу 1123.
У таблиці скоєних рейсів Pass_in_trip міститься лише дата рейсу, але з час, т.к. відповідно до предметної області кожен рейс може виконуватися лише один раз на день. Для вирішення цього завдання потрібно додати, що зберігається в таблиці Pass_in_trip, додати час з таблиці Trip
DISTINCTнеобхідний тут, щоб унеможливити дублікати, оскільки номер і дата рейсу дублюються в цій таблиці для кожного пасажира даного рейсу. Функція DATENAME
Синтаксис
Це дає нам найпростішу можливість конкатенувати компоненти дати, отримуючи будь-який потрібний формат. Наприклад, конструкція
дасть нам 31, а SELECT DATENAME (day, "2003-12-31")
- 365.
SELECT DATENAME (dayofyear, "2003-12-31")
У ряді випадків функцію DATEPARTможна замінити більш простими функціями. Ось вони:
DAY (date) - цілісне подання дня зазначеної дати. Ця функція еквівалентна функції DATEPART(dd, date).
MONTH (date) - цілісне подання місяця зазначеної дати. Ця функція еквівалентна функції DATEPART(mm, date).
YEAR (date) - цілісне подання року зазначеної дати. Ця функція еквівалентна функції DATEPART(yy, date).
Функція @@DATEFIRST
повертає 7, то першим днем тижня вважається неділя (відповідає поточним налаштуванням на сайті). SELECT @@DATEFIRST;
SQL - Урок 13. Функції дати та часу
Ці функції призначені для роботи з типами календарних даних. Розглянемо найбільш застосовні.
Ну от, сьогодні все. Наступного разу розглянемо функції, які допомагають переводити дати з одного формату до іншого.
Функції CURDATE() і NOW() зручно використовуватиме додавання до бази даних записів, що використовують поточний час. У нашому магазині всі постачання та продаж використовують поточний час. Тому для додавання записів про поставки і продаж зручно використовувати функцію CURDATE(). Наприклад, нехай до нашого магазину прийшов товар, давайте додамо інформацію про це в таблицю Поставка (incoming):
Якби ми зберігали дату поставки з типом datatime, то більше підійшла б функція NOW().
Припустимо, ми помилилися при введенні дати для першого постачальника, давайте зменшимо його дату на одну добу:
Як значення value можуть виступати не лише дні, а й тижні (WEEK), місяці (MONTH), квартали (QUARTER) та роки (YEAR). Давайте для прикладу зменшимо дату поставки для другого постачальника на 1 тиждень:
У таблиці Поставки (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 хвилин:
SECOND - секунди
MINUTE - хвилини
HOUR - годинник
DAY - дні
WEEK - тижні
MONTH – місяці
QUARTER - квартали
YEAR - роки
SECOND - секунди
MINUTE - хвилини
HOUR - годинник
DAY - дні
WEEK - тижні
MONTH – місяці
QUARTER - квартали
YEAR - роки