→ SQL — Функції дати. Операції над датами та часом у SQL Робота з датами у процедурі sql

SQL — Функції дати. Операції над датами та часом у SQL Робота з датами у процедурі sql

Функції Transact-SQL можуть бути агрегатними або скалярними. Ці типи функцій розглядаються у цій статті.

Агрегатні функції

Агрегатні функції виконують обчислення над групою значень стовпця і повертають одне значення результату цих обчислень. Мова Transact-SQL підтримує кілька звичайних агрегатних функцій:

AVG

Обчислює середнє арифметичне значення даних, які у стовпці. Значення, над якими виконується обчислення, мають бути числовими.

MIN та MAX

Визначають максимальне та мінімальне значення з усіх значень даних, що містяться у стовпці. Значення можуть бути числовими, строковими або тимчасовими (дата/час).

SUM

Обчислює загальну суму значень у стовпці. Значення, над якими виконується обчислення, мають бути числовими.

COUNT

Підраховує кількість значень, відмінних від null у стовпці. Функція count(*) є єдиною агрегатною функцією, яка виконує обчислення над стовпцями. Ця функція повертає кількість рядків (незалежно від того, чи містять окремі стовпці значення null).

COUNT_BIG

Аналогічна функція count, з тією різницею, що повертає значення даних типу BIGINT.

Використання звичайних агрегатних функцій в інструкції SELECT розглядатиметься в одній із наступних статей.

Скалярні функції

Скалярні функції Transact-SQL використовуються для створення скалярних виразів. (Скалярна функція виконує обчислення над одним значенням або списком значень, тоді як агрегатна функція виконує обчислення над групою значень з кількох рядків.) Скалярні функції можна розбити на наступні категорії:

    числові функції;

    функції дати;

    рядкові функції;

    системні функції;

    функції метаданих.

Ці типи функцій розглядаються у наступних розділах.

Числові функції

Числові функції Transact-SQL - це математичні функції для модифікування числових значень. Список числових функцій та їх короткий опис наведено у таблиці нижче:

Числові функції Transact-SQL
Функція Синтаксис Опис Приклад використання
ABS ABS(n)

Повертає абсолютне значення (тобто негативні значення повертаються як позитивні) числового виразу n.

SELECT ABS(-5.320) -- Вернет 5.320 SELECT ABS(8.90) -- Вернет 8.90
ACOS, ASIN, ATAN, ATN2 ACOS(n), ASIN(n), ATAN(n), ATN2(n, m)

Зворотні тригонометричні функції, що обчислюють арккосинус, арксінус, арктангенс значення n (для ATN2 обчислюється арктангенс n/m). Вихідні значення n, m та результат мають тип даних FLOAT.
COS, SIN, TAN, COT COS(n), SIN(n), TAN(n), COT(n)

Тригонометричні функції, що обчислюють косинус, синус, тангенс, котангенс значення n. Результат має тип даних FLOAT.
DEGREES, RADIANS DEGREES(n), RADIANS(n)

Функція DEGREES перетворює радіани на градуси, RADIANS відповідно навпаки.

SELECT DEGREES(PI() / 4) -- Вернет 45 SELECT COS(RADIANS(60.0)) -- Вернет 0.5
CEILING CEILING(n)

Заокруглює число до більшого цілого значення.

SELECT CEILING(-5.320) -- Вернет -5 SELECT CEILING(8.90) -- Вернет 9
ROUND ROUND(n, p, [t])

Округлює значення n із точністю до p. Коли аргумент p позитивне число, округляється дрібна частина числа n, а коли негативне - ціла частина. При використанні необов'язкового аргументу t число n не округляється, а усікається (тобто округлюється в меншу сторону).

SELECT ROUND(5.3208, 3) -- Вернет 5.3210 SELECT ROUND(125.384, -1) -- Вернет 130.000 SELECT ROUND(125.384, -1, 1) -- Вернет 120.000
FLOOR FLOOR(n)

Округлює до меншого значення.

SELECT FLOOR(5.88) -- Поверне 5
EXP EXP(n)

Обчислює значення e n.
LOG, LOG10 LOG(n), LOG10(n)

LOG(n) - обчислює натуральний логарифм (тобто з основою e) числа n, LOG10(n) - обчислює десятковий (з основою 10) логарифм числа n.
PI PI()

Повертає значення π (3,1415).
POWER POWER(x, y)

Обчислює значення x y.
RAND RAND()

Повертає довільне число типу FLOAT у діапазоні значень між 0 та 1.
ROWCOUNT_BIG ROWCOUNT_BIG()

Повертає кількість рядків таблиці, оброблених останньою інструкцією Transact-SQL, виконаною системою. Значення, що повертається, має тип BIGINT.
SIGN SIGN(n)

Повертає знак значення n як числа: +1, якщо позитивне, -1, якщо негативне.
SQRT, SQUARE SQRT(n), SQUARE(n)

SQRT(n) – обчислює квадратний корінь числа n, SQUARE(n) – повертає квадрат аргументу n.

Функції дати

Функції дати обчислюють відповідні частини дати або часу вираження або повертають значення часового інтервалу. Функції дати, що підтримуються в Transact-SQL, та їх короткий опис наводяться в таблиці нижче:

Функції дати Transact-SQL
Функція Синтаксис Опис Приклад використання
GETDATE GETDATE()

Повертає поточну системну дату та час.

SELECT GETDATE()
DATEPART DATEPART (item, date)

Повертає вказану у параметрі item частину дати date у вигляді цілого числа.

Вернет 1 (Січень) SELECT DATEPART(month, "01.01.2012") -- Вернет 4 (Wednesday) SELECT DATEPART(weekday, "02.01.2012")
DATENAME DATENAME (item, date)

Повертає вказану у параметрі item частину дати date у вигляді рядка символів.

Вернет January SELECT DATENAME(month, "01.01.2012") -- Вернет Wednesday SELECT DATENAME(weekday, "02.01.2012")
DATEDIFF DATEDIFF (item, dat1, dat2)

Обчислює різницю між двома частинами дат dat1 і dat2 і повертає цілий результат в одиницях, зазначених в аргументі item.

Поверне 19 (19 років проміжок між датами) SELECT DATEDIFF(year, "01.01.1990", "01.01.2010") - Поверне 7305 (7305 днів проміжок між датами) SELECT DATEDIFF(day, "01.01.09.19 .2010")
DATEADD DATEADD (item, n, date)

Додає n кількість одиниць, зазначених в аргументі item до зазначеної дати date. (Значення аргументу n може бути негативним.)

Додасть 3 дні до поточної дати SELECT DATEADD(day, 3, GETDATE())

Рядкові функції

Рядкові функції маніпулюють значеннями стовпців, які зазвичай мають символьний тип даних. Термінові функції та їх короткий опис, що підтримуються в Transact-SQL, наводяться в таблиці нижче:

Рядкові функції Transact-SQL
Функція Синтаксис Опис Приклад використання
ASCII, UNICODE ASCII(char), UNICODE(char)

Перетворює зазначений символ на відповідне ціле число коду ASCII.

SELECT ASCII("W") - 87 SELECT UNICODE("ю") - 1102
CHAR, NCHAR CHAR(int), NCHAR(int)

Перетворює код ASCII (або Unicode якщо NCHAR) у відповідний символ.

SELECT CHAR(87) - "W" SELECT NCHAR(1102) - "ю"
CHARINDEX CHARINDEX (str1, str2)

Повертає початкову позицію входження підрядка str1 у рядок str2. Якщо рядок str2 не містить підстроки str1, повертається значення 0

Поверне 5 SELECT CHARINDEX ("морф", "поліморфізм")
DIFFERENCE DIFFERENCE (str1, str2)

Повертає ціле число від 0 до 4, яке є різницею між значеннями SOUNDEX двох рядків str1 та str2. Метод SOUNDEX повертає число, що характеризує звучання рядка. За допомогою цього методу можна визначити подібно рядки, що звучать. Працює лише для символів ASCII.

Поверне 2 SELECT DIFFERENCE ("spelling", "telling")
LEFT, RIGHT LEFT (str, length), RIGHT (str, length)

Повертає кількість перших символів рядка str, задану параметром length для LEFT та останні length символів рядка str для функції RIGHT.

DECLARE @str nvarchar(30) = "Синхронізація"; -- Поверне "Сінх" SELECT LEFT(@str, 4) -- Поверне "зація" SELECT RIGHT(@str, 5)
LEN LEN(str)

Повертає кількість символів (не кількість байт) рядка str, вказаного в аргументі, включаючи кінцеві пробіли.
LOWER, UPPER LOWER(str), UPPER(str)

Функція LOWER перетворює всі великі літери рядка str1 на малі. Малі літери та інші символи, що входять до рядка, не зачіпаються. Функція UPPER перетворює всі малі літери рядка str в великі.

DECLARE @str nvarchar(30) = "Синхронізація"; -- Поверне "СИНХРОНІЗАЦІЯ" SELECT UPPER(@str) -- Поверне "синхронізація" SELECT LOWER(@str)
LTRIM, RTRIM LTRIM(str), RTRIM(str)

Функція LTRIM видаляє початкові прогалини у рядку str, RTRIM відповідно видаляє прогалини в кінці рядка.
QUOTENAME QUOTENAME (char_string)

Повертає рядок у кодуванні Unicode з доданими обмежувачами, щоб перетворити рядок введення на дійсний ідентифікатор з обмежувачами.

DECLARE @str nvarchar(30) = "Синхронізація"; -- Поверне "[Синхронізація]" SELECT QUOTENAME(@str)
PATINDEX PATINDEX (%p%, expr)

Повертає початкову позицію першого входження шаблону p заданий вираз expr, або нуль, якщо даний шаблон не виявлений.

Поверне 4 SELECT PATINDEX("%хро%", "Синхронізація")
REPLACE REPLACE (str1, str2, str3)

Замінює всі входження підрядка str2 у рядку str1 підрядком str3.

Поверне "Десинхронізація" SELECT REPLACE("Синхронізація", "Сінхр", "Десинхр")
REPLICATE REPLICATE (str, i)

Повторює i раз рядок str.

Поверне "aBaBaBaBaB" SELECT REPLICATE("aB", 5)
REVERSE REVERSE (str)

Виводить рядок str у зворотному порядку.

Поверне "яєцазинорхніС" SELECT REVERSE("Синхронізація")
SOUNDEX SOUNDEX (str)

Повертає чотирисимвольний код soundex, який використовується для визначення схожості двох рядків. Працює лише для символів ASCII.
SPACE SPACE (length)

Повертає рядок пробілів довжиною, вказаним у параметрі length. Аналог REPLICATE(" ", length).
STR STR (f[, len[, d]])

Перетворює заданий вираз з плаваючою точкою f в рядок, де len - довжина рядка, включаючи десяткову точку, знак, цифри та прогалини (за замовчуванням дорівнює 10), а d - число розрядів дробової частини, які потрібно повернути.

Поверне "3.14" SELECT STR (3.1415, 4, 2)
STUFF STUFF (str1, a, length, str2)

Видаляє з рядка str1 length-символів, починаючи з позиції a, і вставляє на їхнє місце рядок str2.

Note in a book SELECT STUFF("Notebook", 5, 0," in a ") -- Handbook SELECT STUFF("Notebook", 1, 4, "Hand")
SUBSTRING SUBSTRING (str1, a, length)

Витягує з рядка str, починаючи з позиції a, підрядок довжиною length.

Системні функції

Системні функції Transact-SQL надають широку інформацію про об'єкти бази даних. Більшість системних функцій використовує внутрішній числовий ідентифікатор (ID), який надається кожному об'єкту бази даних під час його створення. Завдяки цьому ідентифікатору система може однозначно ідентифікувати кожен об'єкт бази даних.

У наступній таблиці наводяться деякі з найважливіших системних функцій разом з їх коротким описом:

Системні функції Transact-SQL
Функція Синтаксис Опис Приклад використання
CAST CAST (w AS type [(length)]

Перетворює вираз w на зазначений тип даних type (якщо це можливо). Аргумент w може бути будь-яким дійсним виразом.

Поверне 3 SELECT CAST (3.1258 AS INT)
COALESCE COALESCE (a1, a2)

Повертає перше значення виразу зі списку виразів a1, a2, ..., яке є значенням null.
COL_LENGTH COL_LENGTH (obj, col)

Повертає довжину стовпця col об'єкта бази даних (таблиці чи подання) obj.

Поверне 4 SELECT COL_LENGTH ("Employee", "Id")
CONVERT CONVERT (type [(length)], w)

Еквівалент функції CAST, але аргументи вказуються інакше. Може застосовуватись з будь-яким типом даних.
CURRENT_TIMESTAMP CURRENT_TIMESTAMP

Повертає поточну дату та час.
CURRENT_USER CURRENT_USER

Повертає ім'я користувача.
DATALENGTH DATALENGTH (z)

Повертає число байтів, що займає вираз z.

Цей запит повертає довжину кожного поля SELECT DATALENGTH(FirstName) FROM Employee
GETANSINULL GETANSINULL ("dbname")

Повертає 1, якщо використання значень null у базі даних dbname відповідає вимогам стандарту ANSI SQL.
ISNULL ISNULL (expr, value)

Повертає значення виразу expr, якщо він не дорівнює NULL; в іншому випадку повертається значення значення.
ISNUMERIC ISNUMERIC (expr)

Визначає, чи вираз expr має дійсний числовий тип.
NEWID NEWID()

Створює однозначний ідентифікаційний номер ID, що складається з 16-байтового двійкового рядка, призначеного для зберігання значень типу даних UNIQUEIDENTIFIER.
NEWSEQUENTIALID NEWSEQUENTIALID()

Створює ідентифікатор GUID, більший за будь-який інший ідентифікатор GUID, створений раніше цією функцією на вказаному комп'ютері. (Цю функцію можна використовувати лише як значення за промовчанням для стовпця.)
NULLIF NULLIF (expr1, expr2)

Повертає значення null, якщо значення виразів expr1 та expr2 однакові.

Запит повертає NULL для проекту - у якого NUMBER = "p1" SELECT NULLIF(Number, "p1") FROM Project
SERVERPROPERTY SERVERPROPERTY (propertyname)

Повертає інформацію про властивості сервера бази даних.
SYSTEM_USER SYSTEM_USER

Повертає ID користувача.
USER_ID USER_ID()

Повертає ідентифікатор користувача username. Якщо користувач не вказано, повертається ідентифікатор поточного користувача.
USER_NAME USER_NAME()

Повертає ім'я користувача із зазначеним ідентифікатором id. Якщо ідентифікатор не вказано, повертається ім'я поточного користувача.

Функції метаданих

За великим рахунком, функції метаданих повертають інформацію про вказану базу даних та об'єкти бази даних. У таблиці нижче наводяться деякі з найважливіших функцій метаданих разом з їх коротким описом:

Функції метаданих Transact-SQL
Функція Синтаксис Опис Приклад використання
COL_NAME COL_NAME (tab_id, col_id)

Повертає ім'я стовпця із зазначеним ідентифікатором col_id таблиці з ідентифікатором tab_id.

Поверне ім'я стовпця "LastName" SELECT COL_NAME (OBJECT_ID("Employee"), 3)
COLUMNPROPERTY COLUMNPROPERTY (id, col, property)

Повертає інформацію про вказаний стовпчик.

Поверне значення властивості PRECISION -- для стовпця Id таблиці Employee SELECT COLUMNPROPERTY (OBJECT_ID("Employee"), "Id", "precision")
DATABASEPROPERTY DATABASEPROPERTY (database, property)

Повертає значення властивості власності бази даних database.

Поверне значення властивості IsNullConcat - для бази даних SampleDb SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat")
DB_ID DB_ID ()

Повертає ідентифікатор бази даних db_name. Якщо ім'я бази даних не вказано, ідентифікатор поточної бази даних повертається.
DB_NAME DB_NAME()

Повертає назву бази даних, що має ідентифікатор db_id. Якщо ідентифікатор не вказано, повертається ім'я поточної бази даних.
INDEX_COL INDEX_COL (table, i, no)

Повертає ім'я індексованого стовпця таблиці таблиці. Стовпець вказується ідентифікатором індексу i і позицією стовпця no в цьому індексі.
INDEXPROPERTY INDEXPROPERTY (obj_id, index_name, property)

Повертає властивості іменованого індексу чи статистики для зазначеного ідентифікаційного номера таблиці, ім'я індексу чи статистики, і навіть ім'я властивості.
OBJECT_NAME OBJECT_NAME (obj_id)

Повертає ім'я об'єкта бази даних з ідентифікатором obj_id.

SELECT OBJECT_NAME(245575913);
OBJECT_ID OBJECT_ID (obj_name)

Повертає ідентифікатор об'єкта бази даних obj_name.

Поверне 245575913 - ID таблиці Employee SELECT OBJECT_ID("Employee")
OBJECTPROPERTY OBJECTPROPERTY (obj_id, property)

Повертає інформацію про об'єкти із поточної бази даних.

Від автора:Сьогодні ми поговоримо про те, як працюють у SQL функції дати. У наступній таблиці наведено список усіх важливих функцій, пов'язаних з датою та часом, які доступні. Існують і інші, які підтримуються різними СУБД. Цей список представляє функції, доступні в СУБД MySQL.

ADDDATE(). Додає дати

ADDTIME(). Додає час

CONVERT_TZ(). Перетворює з одного часового поясу на інший

CURDATE(). Повертає поточну дату

CURRENT_DATE(), CURRENT_DATE. Синоніми для CURDATE()

CURRENT_TIME(), CURRENT_TIME. Синоніми для CURTIME()

CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP. Синоніми для NOW()

CURTIME(). Повертає поточний час

DATE_ADD(). Складає дві дати

DATE_FORMAT(). Вказує вказаний формат дати

DATE_SUB(). Віднімає одну дату з іншої

DATE(). Витягує частину, що відноситься до дати, з виразу представляє дату або час і дату

DATEDIFF(). Віднімає одну дату з іншої

DAY(). Синонім для DAYOFMONTH()

DAYNAME(). Повертає день тижня

DAYOFMONTH(). Повертає день місяця (1-31)

DAYOFWEEK(). Повертає індекс дня тижня аргументу

DAYOFYEAR(). Повертає номер дня на рік (1-366)

EXTRACT. Витягує частину, що відноситься до дати

FROM_DAYS(). Перетворює номер дня на дату

FROM_UNIXTIME(). Форматує дату як тимчасову мітку UNIX

HOUR(). Витягує годину

LAST_DAY. Повертає останній день місяця для аргументу

LOCALTIME(), LOCALTIME. Синонім для NOW()

LOCALTIMESTAMP, LOCALTIMESTAMP (). Синонім для NOW()

MAKEDATE(). Створює дату з року та дня року

MAKETIME. MAKETIME(). MICROSECOND(). Повертає мікросекунди з аргументу

MINUTE(). Повертає хвилини з аргументу

MONTH(). Повернення місяця з дати

MONTHNAME(). Повертає назву місяця

NOW(). Повертає поточну дату та час

PERIOD_ADD(). Додає період до року-місяця

PERIOD_DIFF(). Повертає кількість місяців між періодами

QUARTER(). Повертає квартал із аргументу

SEC_TO_TIME(). Перетворює секунди на формат «HH: MM: SS»

SECOND(). Повертає секунди (0-59)

STR_TO_DATE(). Перетворює рядок на дату

SUBDATE(). При виклику із трьома аргументами синонім DATE_SUB()

SUBTIME(). Віднімає час

SYSDATE(). Повертає час виконання функції

TIME_FORMAT(). Задає формат часу

TIME_TO_SEC(). Повертає аргумент, перетворений на секунди

TIME(). Витягує частину, що відноситься до часу, з переданого виразу

TimeDiff(). Віднімає час

TIMESTAMP(). З одним аргументом ця функція повертає вираз дати чи дати та часу. Із двома аргументами — складає ці два аргументи

TIMESTAMPADD(). Додає інтервал до вираження дати та часу

TIMESTAMPDIFF(). Віднімає інтервал з виразу дати та часу

TO_DAYS(). Повертає аргумент дати, перетворений на дні

UNIX_TIMESTAMP(). Повертає тимчасову мітку UNIX

UTC_DATE(). Повертає поточну дату UTC

UTC_TIME(). Повертає поточний час UTC

UTC_TIMESTAMP(). Повертає поточну дату та час UTC

WEEK(). Повертає номер тижня

WEEKDAY(). Повертає індекс дня тижня

WEEKOFYEAR(). Повертає календарний номер тижня (1-53)

YEAR(). Повертає рік

YEARWEEK(). Повертає рік та тиждень

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

Під час виклику з другим аргументом, заданим у вигляді INTERVAL, функція ADDDATE() є синонімом DATE_ADD(). Пов'язана функція SUBDATE() є синонімом DATE_SUB(). Інформацію про аргумент блоку INTERVAL див. у розділі DATE_ADD().

При виклику з другим аргументом, заданим у днях, MySQL розглядає це як ціле число днів додавання у вираз.

ADDTIME(expr1,expr2)

ADDTIME() додає expr2 до expr1 і повертає результат. Expr1 є виразом часу або дати та часу, в той час як expr2 є виразом часу.

CONVERT_TZ(dt,from_tz,to_tz)

Перетворює значення дати і часу dt з часового поясу, заданого from_tz, часовий пояс, заданий to_tz, і повертає отримане значення. Ця функція повертає NULL, якщо аргументи є недійсними.

CURDATE()

Повертає поточну дату як значення у форматі «YYYY-MM-DD» або YYYYMMDD, залежно від того, чи використовується ця функція в рядковому чи числовому контексті.

CURRENT_DATE та CURRENT_DATE()

CURRENT_DATE та CURRENT_DATE() є синонімами для CURDATE()

CURTIME()

Повертає поточний час як значення у форматі «HH: MM: SS» або HHMMSS, залежно від того, чи функція використовується в рядковому або в числовому контексті. Значення відображається для поточного часового поясу.

CURRENT_TIME та CURRENT_TIME()

CURRENT_TIME та CURRENT_TIME() є синонімами для CURTIME().

CURRENT_TIMESTAMP та CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP та CURRENT_TIMESTAMP() є синонімами для NOW().

DATE(expr)

Витягує частину, що відноситься до дати, з виразу дати або дати та часу expr.

DATEDIFF(expr1,expr2)

DATEDIFF() повертає expr1.expr2, виражений як кількість днів між двома датами. І expr1, і expr2 є виразами дати чи дати та часу. У розрахунках використовуються лише частини, що відносяться до дати.

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

Ці функції виконують арифметичні операції із датами. date представлено як значення DATETIME або DATE, що вказує на початкову дату. expr є виразом, що визначає значення інтервалу, який потрібно додати або відняти з вихідної дати. expr - це рядок; вона може починатися з "-" для негативних інтервалів.

unit є ключовим словом, що вказує одиниці виміру для виразу. Ключове слово INTERVAL та позначення одиниць не чутливі до регістру. У наступній таблиці показано очікувану форму аргументу expr для кожного значення одиниці виміру.

Значення QUARTER і WEEK доступні MySQL починаючи з версії 5.0.0.

DATE_FORMAT (date, format)

Ця команда задає формат значення date відповідно до зазначеного рядка format. У рядку format можуть використовуватись такі покажчики. Перед вказівниками формату необхідно додавати символ %.

%a. Скорочена назва дня тижня (Sun..Sat)

%b. Скорочена назва місяця (Jan..Dec)

%с. Числове позначення місяця (0…12)

%D. День місяця з англійським суфіксом (0, 1, 2, 3,.)

%d. Числове позначення дня місяця (00.31)

%е. Числове позначення дня місяця (00.31)

%f. Мікросекунди (000000..999999)

%H. Година (00..23)

%h. Година (01..12)

%I. Година (01..12)

%i. Числове позначення хвилин (00..59)

%J. День року (001..366)

%k. Година (0..23)

%l. Година (1..12)

%M. Назва місяця (January..December)

%м. Числове позначення місяця (00..12)

%р. AM чи PM

%r. Час, 12-годинний формат (чч: мм: сс, за яким слідують AM або PM)

%S. Секунди (00..59)

%s. Секунди (00..59)

%Т. Час, 24-годинний формат (чч: мм: сс)

%U. Тиждень (00..53), де неділя - перший день тижня

%u. Тиждень (00..53), де понеділок - перший день тижня

%V. Тиждень (01..53), де неділя - перший день тижня; використовується разом із %X

%v. Тиждень (01..53), де понеділок – перший день тижня; використовується разом із %x

%W. Назва дня тижня (Sunday..Saturday)

%w. День тижня (0=Sunday..6=Saturday)

%X. Рік для тижня, де перший день тижня - неділя, число чотирьох цифр; використовується разом із %V

%x. Рік на тиждень, де перший день тижня - понеділок, число з чотирьох цифр; використовується разом із %V

%Y. Рік, число, чотири цифри

%y. Числове позначення року (дві цифри)

%%. Буквально символ %

%x. x, для всіх.x., не перерахованих вище

DATE_SUB(date,INTERVAL expr unit)

Аналогічно функції DATE_ADD().

DAY(date)

DAY() є синонімом функції DAYOFMONTH().

DAYNAME(date)

Повертає день тижня для цієї дати.

DAYOFMONTH(date)

Повертає день місяця для зазначеної дати від 0 до 31.

DAYOFWEEK(date)

Повертає індекс дня тижня (1 = Sunday, 2 = Monday, ., 7 = Saturday). Ці значення індексу відповідають стандарту ODBC.

DAYOFYEAR(date)

Повертає день року для цієї дати в діапазоні від 1 до 366.

EXTRACT(unit FROM date)

Функція EXTRACT() використовує ті ж типи покажчиків одиниць вимірювання, що і DATE_ADD() або DATE_SUB(), але не виконує арифметичні операції з датами, а витягує з дати частину, що відноситься до покажчика одиниць вимірювання.

FROM_DAYS(N)

Повертається значення DATE з урахуванням числа N днів.

Примітка. Обережно використовуйте FROM_DAYS() для старих дат. Функція не призначена для роботи зі значеннями дат до запровадження григоріанського календаря (1582).

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

Повертає представлення аргументу unix_timestamp як значення у форматі «YYYY-MM-DD HH: MM: SS або YYYYMMDDHHMMSS» залежно від того, чи використовується ця функція в рядковому чи числовому контексті. Значення відображається в поточному часовому поясі. Параметр unix_timestamp є внутрішнім значенням мітки часу, що створюється функцією UNIX_TIMESTAMP().

Якщо format вказано, результат форматується відповідно до рядка format, який використовується так само, як описано в розділі DATE_FORMAT().

HOUR(time)

Повертає годинник із зазначеного часу. Діапазон значення, що повертається, становить від 0 до 23. Однак діапазон значень TIME насправді набагато більше, тому HOUR може повертати значення, що перевищують 23.

LAST_DAY(date)

Приймає значення дати або дати та часу та повертає значення, що відповідає останньому дню місяця. Повертає NULL, якщо аргумент недійсний.

LOCALTIME та LOCALTIME()

LOCALTIME та LOCALTIME() є синонімами для NOW().

LOCALTIMESTAMP та LOCALTIMESTAMP()

LOCALTIMESTAMP та LOCALTIMESTAMP() є синонімами для NOW().

MAKEDATE(year,dayofyear)

Повертає значення дати, заданого року та дня року. Значення dayofyear має бути більше 0 або результат буде NULL.

MAKETIME(hour,minute,second)

Повертає значення часу, розраховане з аргументів hour, minute та second.

MICROSECOND(expr)

Повертає мікросекунди з виразу часу або datetime(expr) у вигляді числа в діапазоні від 0 до 999999.

MINUTE(time)

Повертає хвилини для вказаного часу в діапазоні від 0 до 59.

MONTH(date)

Повертає місяць для цієї дати в діапазоні від 0 до 12.

MONTHNAME(date)

Повертає повну назву місяця для зазначеної дати.

NOW()

Повертає поточну дату та час як значення у форматі «YYYY-MM-DD HH: MM: SS» або YYYYMMDDHHMMSS, залежно від того, чи використовується функція у рядковому чи числовому контексті. Це значення відображається в поточному часовому поясі.

PERIOD_ADD(P,N)

Додає N місяців до періоду P (у форматі YYMM або YYYYMM). Повертає значення у форматі YYYYMM. Зауважте, що аргумент періоду P не є значенням дати.

PERIOD_DIFF(P1,P2)

Повертає кількість місяців між періодами P1 та P2. Періоди P1 та P2 повинні вказуватися у форматі YYMM або YYYYMM. Зверніть увагу, що аргументи періодів P1 та P2 не є значеннями дати.

QUARTER(date)

Повертає квартал року для зазначеної дати у діапазоні від 1 до 4.

SECOND(time)

Повертає значення секунд для часу від 0 до 59.

SEC_TO_TIME(seconds)

Повертає аргумент seconds, перетворений у години, хвилини та секунди у форматі «HH: MM: SS» або HHMMSS, залежно від того, чи функція використовується в рядковому чи числовому контексті.

STR_TO_DATE(str,format)

Це інверсивна функція функції DATE_FORMAT(). Вона приймає рядок str і формат формату. Функція STR_TO_DATE() повертає значення DATETIME, якщо рядок формату містить як дату, і час. В іншому випадку вона повертає значення DATE або TIME, якщо рядок містить лише дату або час.

SUBDATE(date,INTERVAL expr unit) та SUBDATE(expr,days)

Якщо SUBDATE() викликається з другим аргументом, заданим як INTERVAL, функція є синонімом DATE_SUB(). Інформацію про аргумент INTERVAL дивіться у розділі DATE_ADD().

SUBTIME(expr1,expr2)

Функція SUBTIME() повертає expr1. expr2 виражається як значення у тому форматі, як і expr1. Значення expr1 є виразом часу або дати та часу, а значення expr2 є виразом часу.

SYSDATE()

Повертає поточну дату та час як значення у форматі «YYYY-MM-DD HH: MM: SS» або YYYYMMDDHHMMSS, залежно від того, чи використовується функція у рядковому чи числовому контексті.

TIME(expr)

Витягує частину, що відноситься до часу, вирази expr і повертає його у вигляді рядка.

TIMEDIFF(expr1,expr2)

Функція TIMEDIFF() повертає expr1. expr2 виражається як значення часу. Значення expr1 і expr2 є виразом часу або дати і часу, але обидва вони повинні бути одного типу.

TIMESTAMP (expr), TIMESTAMP (expr1, expr2)

З одним зазначеним аргументом ця функція повертає вираз дати чи дати та часу expr, як значення дати та часу. З двома аргументами вона додає вираз часу expr2 до виразу дати або дати та часу expr1 і повертає результат як значення дати та часу.

TIMESTAMPADD(unit,interval,datetime_expr)

Ця функція додає цілий вираз interval до виразу дати або часу datetime_expr. Одиниці вимірювання для інтервалу задаються аргументом unit, який може приймати одне з наступних значень:

Значення unit може бути вказано за допомогою одного з ключових слів, як було показано вище, або з префіксом SQL_TSI_. Наприклад, DAY та SQL_TSI_DAY є дійсними значеннями.

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Повертає цілу різницю між виразами дати або дати та часу datetime_expr1 та datetime_expr2. Одиниці виміру для результату задаються аргументом unit. Дійсними для аргументу unit є ті ж значення, що були перераховані в описі функції TIMESTAMPADD().

TIME_FORMAT(time,format)

Ця функція використовується так само, як і функція DATE_FORMAT(), але рядок format може містити вказівники формату лише для годин, хвилин та секунд.

Якщо значення часу містить частину, що відноситься до годинника, яка більше 23, покажчики формату годинника %H і %k дають значення, більше, ніж звичайний діапазон від 0 до 23. Інші покажчики формату годинника дають значення години 12 по модулю.

TIME_TO_SEC(time)

Повертає аргумент time, перетворений на секунди.

TO_DAYS(date)

Повертає номер дня (кількість днів із 0-го року) для заданої дати date.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

Якщо ця функція викликається без аргументу, вона повертає тимчасову мітку Unix (секунди з 1970-01-01 00:00:00 UTC), як ціле позитивне число. Якщо UNIX_TIMESTAMP() викликається з аргументом date, вона повертає значення аргументу, виражене в секундах із «1970-01-01 00:00:00» UTC. date може бути рядком DATE, рядком DATETIME, TIMESTAMP або числом у форматі YYMMDD або YYYYMMDD.

UTC_DATE, UTC_DATE()

Повертає поточну дату UTC як значення у форматі «YYYY-MM-DD» або YYYYMMDD, залежно від того, чи використовується ця функція у рядковому чи числовому контексті.

UTC_TIME, UTC_TIME()

Повертає поточний час UTC як значення у форматі «HH:MM:SS» або HHMMSS, залежно від того, чи використовується функція у рядковому чи числовому контексті.

UTC_TIMESTAMP, UTC_TIMESTAMP()

Повертає поточну дату та час UTC як значення «YYYY-MM-DD HH: MM: SS» або у форматі YYYYMMDDHHMMSS, залежно від того, чи використовується ця функція в рядковому чи числовому контексті.

WEEK(date[,mode])

Ця функція повертає номер тижня для заданої дати. Форма WEEK() з двома аргументами дозволяє вказати, чи буде тиждень починатися в неділю або в понеділок, і чи має значення, що повертається, знаходитися в діапазоні від 0 до 53 або від 1 до 53. Якщо аргумент mode опущений, використовується значення системної змінної default_week_format

WEEKDAY(date)

Повертає індекс дня тижня для заданої дати date (0 = понеділок, 1 = вівторок, 6 = неділя).

WEEKOFYEAR(date)

Повертає календарний тиждень для заданої дати date як число від 1 до 53. WEEKOFYEAR() — це функція сумісності, еквівалентна WEEK(date,3).

YEAR(date)

Повертає рік для дати заданої дати в діапазоні від 1000 до 9999 або 0 для дати.zero.

YEARWEEK(date), YEARWEEK(date,mode)

Повертає рік і тиждень для заданої дати. Параметр mode працює так само, як аргумент mode для функції WEEK(). Рік у результаті може відрізнятись від року в аргументі дати для першого та останнього тижня року.

Примітка. Номер тижня відрізняється від того, що поверне функція WEEK() (0) для необов'язкових аргументів 0 або 1, оскільки WEEK() поверне тиждень у контексті цього року.

SQL - Урок 13. Функції дати та часу

Ці функції призначені для роботи з типами календарних даних. Розглянемо найбільш застосовні.
  • CURDATE(), CURTIME() і NOW() Перша функція повертає поточну дату, друга – поточний час, а третя – поточну дату та час. Порівняйте:

    SELECT CURDATE(), CURTIME(), NOW();


    Функції CURDATE() і NOW() зручно використовуватиме додавання до бази даних записів, що використовують поточний час. У нашому магазині всі постачання та продаж використовують поточний час. Тому для додавання записів про поставки і продаж зручно використовувати функцію CURDATE(). Наприклад, нехай до нашого магазину прийшов товар, давайте додамо інформацію про це в таблицю Поставка (incoming):

    INSERT INTO incoming (id_vendor, date_incoming) VALUES ("2", curdate());


    Якби ми зберігали дату поставки з типом datatime, то більше підійшла б функція NOW().

  • ADDDATE(date, INTERVAL value) Функція повертає дату date, до якої додано значення value. Значення значення може бути негативним, тоді підсумкова дата зменшиться. Давайте подивимося, коли наші постачальники робили постачання товару:

    SELECT id_vendor, date_incoming FROM incoming;


    Припустимо, ми помилилися при введенні дати для першого постачальника, давайте зменшимо його дату на одну добу:
    Як значення 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 хвилин:

    SELECT ADDDATE("2011-04-15 00:02:00", INTERVAL "02:45" HOUR_MINUTE);



  • SUBDATE(date, INTERVAL value) функція ідентична попередньої, але робить операцію віднімання, а чи не складання.

    SELECT SUBDATE("2011-04-15 00:02:00", INTERVAL "23:53" HOUR_MINUTE);



  • PERIOD_ADD(period, n) функція додає n місяців значення дати period. Нюанс: значення дати має бути представлене у форматі YYYYMM. Давайте до лютого 2011 (201102) додамо 2 місяці:

    SELECT PERIOD_ADD(201102, 2);



  • TIMESTAMPADD(interval, n, date)функція додає до дати date часовий інтервал n, значення якого задаються параметром interval. Можливі значення параметра interval:

    FRAC_SECOND - мікросекунди
    SECOND - секунди
    MINUTE - хвилини
    HOUR - годинник
    DAY - дні
    WEEK - тижні
    MONTH – місяці
    QUARTER - квартали
    YEAR - роки

    SELECT TIMESTAMPADD(DAY, 2, "2011-04-02");



  • TIMEDIFF(date1, date2) обчислює різницю в годинах, хвилинах та секундах між двома датами.

    SELECT TIMEDIFF("2011-04-17 23:50:00", "2011_04-16 14:50:00");



  • DATEDIFF(date1, date2) обчислює різницю днями між двома датами. Наприклад, ми хочемо дізнатися, як давно постачальник "Вільямс" (id=1) постачав нам товар:

  • функція PERIOD_DIFF(period1, period2) обчислює різницю в місяцях між двома датами, представленими у форматі YYYYMM. Давайте дізнаємося різницю між січнем 2010 та серпнем 2011:

    SELECT PERIOD_DIFF(201108, 201001);



  • TIMESTAMPDIFF(interval, date1, date2)функція обчислює різницю між датами date2 та date1 в одиницях, зазначених у параметрі interval. Можливі значення параметра interval:

    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;



  • SUBTIME(date, time) функція віднімає від часу date час time:

    SELECT SUBTIME("2011-04-18 23:17:00", "02:15:30");



  • DATE(datetime) повертає дату, відтинаючи час. Наприклад:

    SELECT DATE("2011-04-15 00:03:20");



  • TIME(datetime) повертає час, відтинаючи дату. Наприклад:

    SELECT TIME("2011-04-15 00:03:20");



  • TIMESTAMP(date) функція приймає дату date та повертає повний варіант з часом. Наприклад:

    SELECT TIMESTAMP("2011-04-17");



  • DAY(date) та DAYOFMONTH(date) функції-синоніми повертають з дати порядковий номер дня місяця:

    SELECT DAY("2011-04-17"), DAYOFMONTH("2011-04-17");



  • DAYNAME(date), DAYOFWEEK(date) та WEEKDAY(date)функції повертають день тижня, перша – його назва, друга – номер дня тижня (відлік від 1 – неділя до 7 – субота), третя – номер дня тижня (відлік від 0 – понеділок, до 6 – неділя:

    SELECT DAYNAME("2011-04-17"), DAYOFWEEK("2011-04-17"), WEEKDAY("2011-04-17");



  • WEEK(date), WEEKOFYEAR(datetime)обидві функції повертають номер тижня на рік, перша для типу date, а друга - для типу datetime, у першому тижні починається з неділі, у другій - з понеділка:

    SELECT WEEK("2011-04-17"), WEEKOFYEAR("2011-04-17 23:40:00");



  • MONTH(date) та MONTHNAME(date)обидві функції повертають значення місяця. Перша – його числове значення (від 1 до 12), друга – назва місяця:

    SELECT MONTH("2011-04-17"), MONTHNAME("2011-04-17");



  • QUARTER(date) функція повертає значення кварталу року (від 1 до 4):

    SELECT QUARTER("2011-04-17");



  • YEAR(date) функція повертає значення року (від 1000 до 9999):

    SELECT YEAR("2011-04-17");



  • DAYOFYEAR(date) повертає порядковий номер дня в році (від 1 до 366):

    SELECT DAYOFYEAR("2011-04-17");



  • HOUR(datetime) повертає значення години для часу (від 0 до 23):

    SELECT HOUR("2011-04-17 18:20:03");



  • MINUTE(datetime) повертає значення хвилин для часу (від 0 до 59): SELECT UNIX_TIMESTAMP("2011-04-17"), FROM_UNIXTIME(1302524000);

  • TIME_TO_SEC(time) та SEC_TO_TIME(n)взаємозворотні функції. Перша перетворює час на кількість секунд, що минули від початку доби. Друга, навпаки, приймає число секунд з початку доби та перетворює їх під час:

    SELECT TIME_TO_SEC("22:10:30"), SEC_TO_TIME(45368);



  • MAKEDATE(year, n) функція приймає рік і номер дня на рік і перетворює їх на дату:

    SELECT MAKEDATE(2011, 120);



Ну от, сьогодні все. Наступного разу розглянемо функції, які допомагають переводити дати з одного формату до іншого.

Вітаю Вас, шановні читачі блогу сайт. У базі даних часто потрібно зберігати різні дані пов'язані з датою та часом. Це може бути дата додавання інформації, дата реєстрації користувача, час останньої автозйомки та інші дані. У мові 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 можуть виступати такі значення:

  • SECOND - секунди
  • MINUTE - хвилини
  • HOUR - годинник
  • DAY - дні
  • WEEK - тижні
  • MONTH - місяці
  • QUARTER - квартали
  • YEAR - роки

а також їх комбінації:

  • MINUTE_SECOND — хвилини та секунди
  • HOUR_SECONDгодинник — хвилини та секунди
  • HOUR_MINUTE - години та хвилини
  • DAY_SECOND — дні, години, хвилини та секунди
  • DAY_MINUTE — дні, години та хвилини
  • DAY_HOUR — дні та години
  • YEAR_MONTH - роки та місяці.

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

Функція SUBDATE (date, INTERVAL value)виробляє відніманнязначення value з дати date. Приклад:

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

Функція TIMESTAMPADD (interval, n, date) додає до дати date часовий інтервал n, значення якого задаються параметром interval. Можливі значення параметра interval:

  • FRAC_SECOND - мікросекунди
  • SECOND - секунди
  • MINUTE - хвилини
  • HOUR - годинник
  • DAY - дні
  • WEEK - тижні
  • MONTH - місяці
  • QUARTER - квартали
  • YEAR - роки

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

Обчислення інтервалу між датами

Функція TIMEDIFF (date1, date2) обчислює різницю в годинах, хвилинах та секундахміж двома датами date1 та date2 . Приклад:

SELECT TIMEDIFF ("2015-09-28 10:30:20", "2015-09-29 10:30:20")
Результат: -24:10:00

Функція 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

Функція PERIOD_DIFF (period1, period2)обчислює різницю в місяцяхміж двома датами. Дати мають бути представлені у форматі YYYYMM. Наприклад, дізнаємося скільки місяців пройшло з січня 2015 по вересень 2015:

SELECT PERIOD_DIFF (201509, 201501)
Результат: 9

Функція TIMESTAMPDIFF (interval, date1, date2)обчислює різницю між датами date2 та date1 в одиницях зазначених у параметрі interval. При цьому interval може приймати такі значення:

  • FRAC_SECOND - мікросекунди
  • SECOND - секунди
  • MINUTE - хвилини
  • HOUR - годинник
  • DAY - дні
  • WEEK - тижні
  • MONTH - місяці
  • QUARTER - квартали
  • YEAR - роки

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 (date)повертає повний формат з часомдати date . Приклад:

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

Функція 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")
Результат: 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)приймає рік року та номер дня на рік n і перетворює їх на дату. приклад.

Стандарт SQL-92 специфікує лише функції, що повертають системну дату/час. Наприклад, функція CURRENT_TIMESTAMP повертає одразу і дату, і час. Плюс є функції що повертають щось одне.
Природно, через таку обмеженість реалізації мови розширюють стандарт за рахунок додавання функцій, що полегшує роботу користувачів з даними цього типу. Тут ми розглянемо функції обробки дати/часу T-SQL.

Функція DATEADD

Синтаксис

DATEADD ( datepart , number, date)

Ця функція повертає значення типу datetime, що виходить додаванням до дати dateкількості інтервалів типу datepart, рівного number. Наприклад, ми можемо до заданої дати додати будь-яке число років, днів, годин, хвилин тощо. Допустимі значення аргументу datepartнаведені нижче та взяті з BOL.


Нехай сьогодні 23/01/2004, і ми хочемо дізнатися, який день буде за тиждень. Ми можемо написати тому, що дрібна частина значення аргументу datepartвідкидається, і ми отримаємо 0 замість однієї четвертої та, як наслідок, поточний день.
Крім того, ми можемо використовувати замість CURRENT_TIMESTAMPфункцію T-SQL GETDATE()з тим самим ефектом. Наявність двох ідентичних функцій підтримується, мабуть, в очікуванні подальшого розвитку стандарту.
приклад (Схема 4). Визначити, який буде день за тиждень після останнього польоту.
SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip))
Використання подзапроса як аргумент допустимо, т.к. це підзапит повертає ЄДИНЕ значення типу datetime.

Функція DATEDIFF

Синтаксис

DATEDIFF ( datepart , startdate , enddate)

Функція повертає інтервал часу, що пройшов між двома часовими відмітками - startdate(початкова позначка) та enddate(Кінець). Цей інтервал може бути виміряний у різних одиницях. Можливі варіанти визначаються аргументом datepartі перераховані вище стосовно функції DATEADD.
приклад (Схема 4). Визначити кількість днів, що пройшли між першим та останнім скоєними рейсами.

(яке дає -760) буде невірним із двох причин.
По-перше, для рейсів, що вилітають в один день, а прилітають наступного, обчислене таким способом значення буде неправильним. По-друге, ненадійно робити будь-які припущення щодо дня, який є тільки через необхідність відповідати типу datetime.
Але як визначити, що літак приземлився наступного дня? Тут допомагає опис предметної області, де йдеться, що політ неспроможна продовжуватися понад добу. Отже, якщо час прильоту не більше, ніж час вильоту, цей факт має місце. Тепер друге питання: як порахувати тільки час, з яким би днем ​​він не стояв?
Тут може допомогти функція T-SQL DATEPART.

Функція DATEPART

Синтаксис

DATEPART ( datepart , date)

Ця функція повертає ціле число, що є зазначеною аргументом datepartчастина заданої другим аргументом дати ( date).
Список допустимих значень аргументу datepart, описаний вище в цьому розділі, доповнюється ще одним значенням


Зауважимо, що повертається функцією DATEPARTзначення в цьому випадку (номер дня тижня) залежить від установок, які можна змінити за допомогою оператора SET DATEFIRST, що встановлює перший день тижня Для когось понеділок – день важкий, а для когось – неділя. До речі, останнє значення приймається за умовчанням.
Однак повернемось до нашого прикладу. У припущенні, що час вильоту/прильоту є кратним хвилині, ми можемо визначити його як суму годин і хвилин. Оскільки функції дати/часу працюють з цілими значеннями, наведемо результат до найменшого інтервалу - хвилин. Отже, час вильоту рейсу 1123 у хвилинах

Тепер ми повинні порівняти, чи час прильоту перевищує час вильоту. Якщо це так, відняти з першого друге, щоб отримати тривалість рейсу. В іншому випадку до різниці потрібно додати одну добу (24 * 60 = 1440 хвилин).

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
Тут, щоб не повторювати довгі конструкції в операторі CASE, використано підзапит. Звичайно, результат вийшов досить громіздким, зате абсолютно коректним у світлі зроблених до цього зауважень.
приклад (4 Схема). Визначити дату та час вильоту рейсу 1123.
У таблиці скоєних рейсів Pass_in_trip міститься лише дата рейсу, але з час, т.к. відповідно до предметної області кожен рейс може виконуватися лише один раз на день. Для вирішення цього завдання потрібно додати, що зберігається в таблиці Pass_in_trip, додати час з таблиці Trip
DISTINCTнеобхідний тут, щоб унеможливити дублікати, оскільки номер і дата рейсу дублюються в цій таблиці для кожного пасажира даного рейсу.

Функція DATENAME

Синтаксис

DATENAME ( datepart , date)

Ця функція повертає символьне уявлення складової ( datepart) зазначеної дати ( date). Аргумент, що визначає складову дати, може приймати одне із значень, перерахованих у наведеній вище таблиці.
Це дає нам найпростішу можливість конкатенувати компоненти дати, отримуючи будь-який потрібний формат. Наприклад, конструкція

Слід зазначити, що ця функція виявляє відмінність значень dayі dayofyearаргументу datepart. Перший дає символьну виставу дня зазначеної дати, тоді як другий дає символьну виставу цього дня з початку року. Тобто.
SELECT DATENAME (day, "2003-12-31")
дасть нам 31, а
SELECT DATENAME (dayofyear, "2003-12-31")
- 365.
У ряді випадків функцію DATEPARTможна замінити більш простими функціями. Ось вони:
DAY (date) - цілісне подання дня зазначеної дати. Ця функція еквівалентна функції DATEPART(dd, date).
MONTH (date) - цілісне подання місяця зазначеної дати. Ця функція еквівалентна функції DATEPART(mm, date).
YEAR (date) - цілісне подання року зазначеної дати. Ця функція еквівалентна функції DATEPART(yy, date).

Функція @@DATEFIRST

@@DATEFIRST повертає число, яке визначає перший день тижня, встановлений для поточної сесії. При цьому 1 відповідає понеділку, а 7 відповідно неділі. Тобто. якщо

SELECT @@DATEFIRST;
повертає 7, то першим днем ​​тижня вважається неділя (відповідає поточним налаштуванням на сайті).

 

 
Це цікаво: