SQL — Функции даты

SQL — Функции даты

От автора: сегодня мы поговорим о том, как работают в SQL функции даты. В следующей таблице приведен список всех важных функций, связанных с датой и временем, которые доступны. Существуют и другие, поддерживаемые различными СУБД. Данный список представляет функции, доступные в СУБД MySQL.

ADDDATE(). Добавляет даты

ADDTIME(). Добавляет время

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

CURDATE(). Возвращает текущую дату

SQL — Функции даты

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля

Получить курс сейчас!

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().

SQL — Функции даты

При вызове со вторым аргументом, заданным в днях, MySQL рассматривает это как целое число дней для добавления в выражение.

SQL — Функции даты

ADDTIME(expr1,expr2)

ADDTIME () добавляет expr2 к expr1 и возвращает результат. Expr1 является выражением времени или даты и времени, в то время как expr2 является выражением времени.

SQL — Функции даты

CONVERT_TZ(dt,from_tz,to_tz)

Преобразует значение даты и времени dt из часового пояса, заданного в from_tz, в часовой пояс, заданный в to_tz, и возвращает полученное значение. Эта функция возвращает NULL, если аргументы недействительны.

SQL — Функции даты

CURDATE()

Возвращает текущую дату как значение в формате «YYYY-MM-DD» или YYYYMMDD, в зависимости от того, используется ли эта функция в строковом или в числовом контексте.

SQL — Функции даты

CURRENT_DATE и CURRENT_DATE()

CURRENT_DATE и CURRENT_DATE() являются синонимами для CURDATE()

CURTIME()

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

SQL — Функции даты

CURRENT_TIME и CURRENT_TIME()

CURRENT_TIME и CURRENT_TIME() являются синонимами для CURTIME().

CURRENT_TIMESTAMP и CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP и CURRENT_TIMESTAMP() являются синонимами для NOW().

DATE(expr)

Извлекает часть, относящуюся к дате, из выражения даты или даты и времени expr.

SQL — Функции даты

DATEDIFF(expr1,expr2)

DATEDIFF() возвращает expr1. expr2, выраженное как количество дней между двумя датами. И expr1, и expr2 являются выражениями даты или даты и времени. В расчетах используются только части относящиеся к дате.

SQL — Функции даты

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

Эти функции выполняют арифметические операции с датами. date представлено как значение DATETIME или DATE, указывающее начальную дату. expr представляет собой выражение, определяющее значение интервала, который нужно добавить или вычесть из исходной даты. expr — это строка; она может начинаться с «-» для отрицательных интервалов.

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

SQL — Функции даты

SQL — Функции даты

DATE_FORMAT (date,format)

Эта команда задает формат значения date в соответствии с указанной строкой format. В строке format могут использоваться следующие указатели. Перед указателями формата необходимо добавлять символ ‘%’.

Сокращенное название дня недели (Sun. Sat)

Сокращенное название месяца (Jan. Dec)

День месяца с английским суффиксом (0, 1, 2, 3,

Числовое обозначение дня месяца (00. 31)

Микросекунды (000000. 999999)

Час (00. 23)

Час (01. 12)

Числовое обозначение минут (00. 59)

День года (001. 366)

Час (0. 23)

Час (1. 12)

Название месяца (January. December)

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

AM или PM

Время, 12-часовой формат (чч: мм: сс, за которым следуют AM или PM)

Секунды (00. 59)

Время, 24-часовой формат (чч: мм: сс)

Неделя (00. 53), где воскресенье — первый день недели

Неделя (00. 53), где понедельник — первый день недели

Неделя (01. 53), где воскресенье — первый день недели; используется вместе с %X

Неделя (01. 53), где понедельник — первый день недели; используется вместе с %x

Название дня недели (Sunday. Saturday)

День недели (0=Sunday. 6=Saturday)

Год для недели, где первый день недели — воскресенье, число из четырех цифр; используется вместе с %V

Год для недели, где первый день недели — понедельник, число из четырех цифр; используется вместе с %V

Год, число, четыре цифры

Числовое обозначение года (две цифры)

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

x, для всех. , не перечисленных выше

SQL — Функции даты

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

DAY(date)

DAY() является синонимом функции DAYOFMONTH().

DAYNAME(date)

Возвращает день недели для указанной даты.

SQL — Функции даты

DAYOFMONTH(date)

Возвращает день месяца для указанной даты в диапазоне от 0 до 31.

SQL — Функции даты

DAYOFWEEK(date)

Возвращает индекс дня недели (1 = Sunday, 2 = Monday,. , 7 = Saturday). Эти значения индекса соответствуют стандарту ODBC.

SQL — Функции даты

DAYOFYEAR(date)

Возвращает день года для указанной даты в диапазоне от 1 до 366.

SQL — Функции даты

EXTRACT(unit FROM date)

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

SQL — Функции даты

FROM_DAYS(N)

Возвращается значение DATE с учетом числа дней N.

SQL — Функции даты

Примечание. Используйте 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().

SQL — Функции даты

HOUR(time)

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

SQL — Функции даты

LAST_DAY(date)

Принимает значение даты или даты и времени и возвращает значение, соответствующее последнему дню месяца. Возвращает NULL, если аргумент недействителен.

SQL — Функции даты

LOCALTIME и LOCALTIME()

LOCALTIME и LOCALTIME() являются синонимами для NOW().

LOCALTIMESTAMP и LOCALTIMESTAMP()

LOCALTIMESTAMP и LOCALTIMESTAMP() являются синонимами для NOW().

MAKEDATE(year,dayofyear)

Возвращает значения даты, заданного года и дня года. Значение dayofyear должно быть больше 0 или результат будет NULL.

SQL — Функции даты

MAKETIME(hour,minute,second)

Возвращает значение времени, рассчитанное из аргументов hour, minute и second.

SQL — Функции даты

MICROSECOND(expr)

Возвращает микросекунды из выражения времени или выражения datetime(expr) в виде числа в диапазоне от 0 до 999999.

SQL — Функции даты

MINUTE(time)

Возвращает минуты для указанного времени, в диапазоне от 0 до 59.

SQL — Функции даты

Возвращает месяц для указанной даты в диапазоне от 0 до 12.

SQL — Функции даты

MONTHNAME(date)

Возвращает полное название месяца для указанной даты.

SQL — Функции даты

NOW()

Возвращает текущую дату и время как значение в формате «YYYY-MM-DD HH: MM: SS» или YYYYMMDDHHMMSS, в зависимости от того, используется ли функция в строковом или числовом контексте. Это значение выражается в текущем часовом поясе.

SQL — Функции даты

PERIOD_ADD(P,N)

Добавляет N месяцев к периоду P (в формате YYMM или YYYYMM). Возвращает значение в формате YYYYMM. Обратите внимание, что аргумент периода P не является значением даты.

SQL — Функции даты

PERIOD_DIFF(P1,P2)

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

SQL — Функции даты

QUARTER(date)

Возвращает квартал года для указанной даты в диапазоне от 1 до 4.

SQL — Функции даты

SECOND(time)

Возвращает значение секунд для времени в диапазоне от 0 до 59.

SQL — Функции даты

SEC_TO_TIME(seconds)

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

SQL — Функции даты

STR_TO_DATE(str,format)

Это инверсивная функция к функции DATE_FORMAT(). Она принимает строку str и строку формата format. Функция STR_TO_DATE() возвращает значение DATETIME, если строка формата содержит как дату, так и время. В противном случае она возвращает значение DATE или TIME, если строка содержит только дату или время.

SQL — Функции даты

SUBDATE(date,INTERVAL expr unit) и SUBDATE(expr,days)

Если SUBDATE() вызывается со вторым аргументом, заданным в виде INTERVAL, функция является синонимом DATE_SUB(). Информацию об аргументе INTERVAL смотрите в разделе DATE_ADD().

SQL — Функции даты

SUBTIME(expr1,expr2)

Функция SUBTIME() возвращает expr1. expr2 выражается как значение в том же формате, что и expr1. Значение expr1 является выражением времени или даты и времени, а значение expr2 является выражением времени.

SQL — Функции даты

SYSDATE()

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

SQL — Функции даты

TIME(expr)

Извлекает часть, относящуюся ко времени, выражения expr и возвращает его в виде строки.

SQL — Функции даты

TIMEDIFF(expr1,expr2)

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

SQL — Функции даты

TIMESTAMP (expr), TIMESTAMP (expr1, expr2)

С одним указанным аргументом эта функция возвращает выражение даты или даты и времени expr, как значение даты и времени. С двумя аргументами она добавляет выражение времени expr2 к выражению даты или даты и времени expr1 и возвращает результат как значение даты и времени.

SQL — Функции даты

TIMESTAMPADD(unit,interval,datetime_expr)

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

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

SQL — Функции даты

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

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

SQL — Функции даты

TIME_FORMAT(time,format)

Эта функция используется так же, как и функция DATE_FORMAT(), но строка format может содержать указатели формата только для часов, минут и секунд.

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

SQL — Функции даты

TIME_TO_SEC(time)

Возвращает аргумент time, преобразованный в секунды.

SQL — Функции даты

TO_DAYS(date)

Возвращает номер дня (количество дней с 0-го года) для заданной даты date.

SQL — Функции даты

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.

SQL — Функции даты

UTC_DATE, UTC_DATE()

Возвращает текущую дату UTC как значение в формате «YYYY-MM-DD» или YYYYMMDD, в зависимости от того, используется ли эта функция в строковом или числовом контексте.

SQL — Функции даты

UTC_TIME, UTC_TIME()

Возвращает текущее время UTC как значение в формате «HH: MM: SS» или HHMMSS, в зависимости от того, используется ли функция в строковом или числовом контексте.

SQL — Функции даты

UTC_TIMESTAMP, UTC_TIMESTAMP()

Возвращает текущую дату и время UTC как значение «YYYY-MM-DD HH: MM: SS» или в формате YYYYMMDDHHMMSS, в зависимости от того, используется ли эта функция в строковом или в числовом контексте.

SQL — Функции даты

WEEK(date[,mode])

Эта функция возвращает номер недели для заданной даты date. Форма WEEK() с двумя аргументами позволяет указать, будет ли неделя начинаться в воскресенье или в понедельник, и должно ли возвращаемое значение находиться в диапазоне от 0 до 53 или от 1 до 53. Если аргумент mode опущен, используется значение системной переменной default_week_format

SQL — Функции даты

SQL — Функции даты

WEEKDAY(date)

Возвращает индекс дня недели для заданной даты date (0 = понедельник, 1 = вторник, 6 = воскресенье).

SQL — Функции даты

WEEKOFYEAR(date)

Возвращает календарную неделю для заданной даты date как число в диапазоне от 1 до 53. WEEKOFYEAR() — это функция совместимости, эквивалентная WEEK(date,3).

SQL — Функции даты

Возвращает год для заданной даты date в диапазоне от 1000 до 9999 или 0 для даты. zero.

SQL — Функции даты

YEARWEEK(date), YEARWEEK(date,mode)

Возвращает год и неделю для заданной даты date. Параметр mode работает точно так же, как аргумент mode для функции WEEK(). Год в результате может отличаться от года в аргументе даты для первой и последней недели года.

SQL — Функции даты

Примечание. Номер недели отличается от того, что вернет функция WEEK() (0) для необязательных аргументов 0 или 1, так как WEEK() вернет неделю в контексте данного года.

Редакция: Команда webformyself.

SQL — Функции даты

Самое сложное при работе с датами — это убедиться, что формат даты, которую вы пытаетесь вставить, совпадает с форматом столбца даты в базе данных.

Пока ваши данные содержат только часть даты, ваши запросы будут работать так, как и ожидалось. Однако, если задействована временная часть, все становится еще сложнее.

Типы данных даты

MySQL поставляется со следующими типами данных для хранения даты или значения даты/времени в базе данных:

  • DATE — формат YYYY-MM-DD
  • DATETIME — формат: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP — формат: YYYY-MM-DD HH:MI:SS
  • YEAR — формат YYYY или YY

SQL Server поставляется со следующими типами данных для хранения даты или значения даты/времени в базе данных:

  • DATE — формат YYYY-MM-DD
  • DATETIME — формат: YYYY-MM-DD HH:MI:SS
  • SMALLDATETIME — формат: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP — формат: уникальное число

Примечание: Типы дат выбираются для столбца при создании новой таблицы в базе данных!

Работа с датами

Вы можете легко сравнить две даты, если нет никакого компонента времени!

Предположим, что у нас есть следующая таблица «Orders»:

OrderIdProductNameOrderDate
1Geitost2008-11-11
2Camembert Pierrot2008-11-09
3Mozzarella di Giovanni2008-11-11
4Mascarpone Fabioli2008-10-29

Теперь мы хотим выбрать записи с порядковым номером «2008-11-11» из приведенной выше таблицы.

Мы используем следующую инструкцию SELECT:

Результирующий набор будет выглядеть следующим образом:

OrderIdProductNameOrderDate
1Geitost2008-11-11
3Mozzarella di Giovanni2008-11-11

Теперь предположим, что таблица «Orders» выглядит следующим образом (обратите внимание на компонент time в столбце «OrderDate»):

OrderIdProductNameOrderDate
1Geitost2008-11-11 13:23:44
2Camembert Pierrot2008-11-09 15:45:21
3Mozzarella di Giovanni2008-11-11 11:12:01
4Mascarpone Fabioli2008-10-29 14:56:59

Если мы используем тот же оператор SELECT, что и выше:

Мы не получим никакого результата! Это происходит потому, что запрос ищет только даты без временной части.

Совет: Чтобы ваши запросы были простыми и удобными в обслуживании, не допускайте компонентов времени в ваших датах!

Упражнения

Литералы служат для непосредственного представления данных, ниже приведен список стандартных литерал:

  • целочисленные — 0, -34, 45;
  • вещественные — 0.0, -3.14, 3.23e-23;
  • строковые — ‘текст’, n’текст’, ‘don»t!’;
  • дата — DATE ‘2008-01-10’;
  • время — TIME ’15:12:56′;
  • временная отметка — TIMESTAMP ‘2008-02-14 13:32:02’;
  • логический тип — true, false;
  • пустое значение — null.

Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.

В MySQL для временных литерал строка должна быть заключена в скобки: DATE (‘2008-01-10’).

Формат даты по умолчанию обычно определяется настройкой БД. Продвинутые СУБД могут автоматически определять некоторые форматы (DATE (‘2008. 10’)) или как в Oracle имеют функцию преобразования (to_date(‘01. 2003′,’dd. yyyy’)). Для упрощения во многих СУБД там, где подразумевается дата, перед строкой необязательно ставить имя типа.

Интервал времени

Синтаксис и реализация интервалов отличается на разных СУБД.

Oracle

Данный тип разделен на два: интервал по годам и интервал по дням. В первом случае можно указать точность интервала только до месяца (по умолчанию год). Во втором случае до различных долей секунды. Кроме этого указывается точность значения временных промежутков в количестве цифр. Если точность не совпадает, то выводится сообщение об ошибке.

— годовые интервалы
— интервал в 99 лет
INTERVAL ’99’ YEAR

— интервал в 999 лет в скобках
— указана точность для годов
INTERVAL ‘999’ YEAR(3)

— интервал в 999 лет и 3 месяца в скобках
— после TO указывается точность самого промежутка
— месяцы указываются через —
INTERVAL ‘999-3’ YEAR(3) TO MONTH

— интервал в 99 лет и два месяца
— это отрицательный интервал
INTERVAL ‘-99-2’ YEAR TO MONTH

— дневные интервалы
— интервал в 200 дней
INTERVAL ‘200’ DAY(3)

— интервал в 200 дней и 6 часов
INTERVAL ‘200 6’ DAY(3) TO HOUR

— интервал в 200 дней, 6 часов
— и 10 минут
INTERVAL ‘200 6:10’ DAY(3) TO MINUTE

— интервал в 200 дней, 6 часов,
— 10 минут и 7 секунд
INTERVAL ‘200 6:10:7’ DAY(3) TO SECOND

— интервал в 200 дней, 6 часов,
— 10 минут, 7 секунд и 333 милисекунды
INTERVAL ‘200 6:10:7. 333’ DAY(3) TO SECOND(3)

— пример интервала в запросе
— выборка интервала в два дня
select INTERVAL ‘2’ day from dual;

PostgreSQL

интервалы указываются в виде строки, в которой перечисляются значение и тип промежутка:

  • microsecond — микросекунды;
  • millisecond — милисекунды;
  • second — секунды;
  • minute — минуты;
  • hour — часы;
  • day — дни;
  • week — недели;
  • month — месяцы;
  • year — года;
  • century — век;
  • millennium — тысячелетие.

Слова можно употреблять и во множественном числе. Если интервал начинается с дней, то можно использовать короткий формат строки как в Oracle для дневных интервалов.

— интервал в три года
INTERVAL ‘3 year’

— интервал в три года и три дня
INTERVAL ‘3 years 3 day’

— интервал в три года, три дня
— и 3 минуты
INTERVAL ‘3 year 3 day 3 minute’

— интервал в 3 дня, 7 часов,
— 7 минут и 5 секунд
INTERVAL ‘3 7:07:05’

— пример интервала в запросе
— выборка интервала в два дня
select INTERVAL ‘2 day’;

MySQL

Только сложные интервалы, состоящие из более одного типа промежутков, указываются в строке. Для этих целей введены дополнительные по сравнению с PostgreSQL имена для промежутков:

  • second_microsecond — секунды и микросекунды, формат строки ‘s.m’;
  • minute_microsecond — минуты и микросекунды, формат строки ‘m.m’;
  • minute_second — минуты и секунды, формат строки ‘m:s’;
  • hour_microsecond — часы и микросекунды, формат строки ‘h.m’;
  • hour_second — часы, минуты и секунды, формат строки ‘h:m:s’;
  • hour_minute — часы и минуты, формат строки ‘h:m’;
  • day_microsecond — день и микросекунды, формат строки ‘d.m’;
  • day_second — дни, часы, минуты и секунды, формат строки ‘d h:m:s’;
  • day_minute — дни, часы и минуты, формат строки ‘d h:m’;
  • day_hour — дни и часы, формат строки ‘d h’;
  • year_month — года и месяцы, формат строки ‘y-m’.

MySQL интервалы используются в выражениях с временными типами данных, использовать их в качестве конечного типа для столбцов запрещено.

— интервал в три года
INTERVAL 3 year

— интервал в 3 дня, 7 часов,
— 7 минут и 5 секунд
INTERVAL ‘3 7:07:05’ day_second

— пример интервала в запросе
— выборка интервала в два дня
— ошибка, столбец не может быть типа INTERVAL
select INTERVAL ‘2 day’;

— правильно, к дате прибавляем интервал
select date ‘2009-01-01’+INTERVAL ‘3 7:07:05’ day_second

Выражения и операции

Для построения выражений SQL включает стандартные операции, ряд дополнительных предикатов (булевских конструкций) и функций. В MySQL для встроенных функций между именем и открывающей скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной функции в БД. Oracle не поддерживает логические выражения в перечислении select.

cтроковые операции

  • + — сложение;
  • — — вычитание;
  • * — умножение;
  • / — деление;
  • mod — остаток от деления. Oracle: mod(6,2). MySql: 6 mod 2.

Операции + и — также используются при работе со временем и интервалами. В Oracle и PostgreSQL возможна разница между датами. Результат возвращается в виде интервала в днях. Ниже приведен пример добавления к дате интервала.

— для PostgreSQL
select date ‘2009-01-01’+INTERVAL ‘3 7:07:05’

— для Oracle
select date ‘2009-01-01’ +
INTERVAL ‘3 7:07:05’ day to second
from dual;

— для MySQL
select date ‘2009-01-01’+
INTERVAL ‘3 7:07:05’ day_second

Ко времени можно прибавлять целое число, но результат зависит от конкретной СУБД.

— для Oracle, 1 интерпретируется как день
select date ‘2009-01-01’+1 from dual

— для PostgreSQL, 1 интерпретируется как день
select date ‘2009-01-01’+1

— для MySQL, 1 интерпретируется как год
select date ‘2009-01-01’+1

операции отношения

  • <= — меньше либо равно;
  • = — равно;

логические операции и предикаты

  • and — логическое и;
  • or — логическое или;
  • nor — отрицание;
  • between — определяет, находится ли значение в указанном диапазоне: выражение BETWEEN значение_с AND значение_по
  • exists — определяет есть ли в указанной выборке хотя бы одна запись
  • in — определяет, входит ли указанное значение в указанное множество: В качестве множества значений может служить корректная выборка
  • is null — является ли указанное выражение NULL значением:выражение IS NULL
  • like — определяет, удовлетворяет ли строка указанному шаблону:

условные выражения

Ниже приведен пример использования выражения в запросе выбора данных.

— для MySQL, PostresSQL
— в скобках наше выражение
select (‘молоко’ LIKE ‘%оло%’) as result;

— эмулировать логический тип в запросах данных
— для Oracle можно с помощью CASE
select case
— в скобках наше условие
when (2 BETWEEN 0 AND 3 )
then 1
else 0
end as result from dual;

прочие операции

В каждой СУБД свой набор операций, выше были приведены наиболее употребительные. Например, в PosgreSQL можно использовать и такие операции:

  • ^ — возведение в степень;
  • ! — постфиксный факториал;
  • !! — префиксный факториал;

Обзор функций

В арсенале каждой СУБД обязательно имеется набор встроенных функций для обработки стандартных типов данных. В MySQL для встроенных функций между именем и открывающей скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной функции в БД. В некоторых СУБД, как Oracle, если функция не имеет аргументов, то скобки можно опустить.

математические функции

  • abs(x) — абсолютное значение;
  • ceil(x) — наименьшее целое, которое не меньше аргумента;
  • exp(x) — экспонента;
  • floor(x) — наибольшее целое, которое не больше аргумента;
  • ln(x) — натуральный логарифм;
  • power(x, y) — возводит x в степень y;
  • sign(x) — возвращает -1 для отрицательных значений x и 1 для положительных;
  • sqrt(x) — квадратный корень;

Тригонометрические функции работают с радианами:

  • acos(x) — арккосинус;
  • asin(x) — арксинус;
  • atan(x) — арктангенс;
  • cos(x) — косинус;
  • sin(x) — синус;
  • tan(x) — тангенс.

строковые функции

  • ascii(string) — возвращает код первого символа, эта функция обратна функции CHR;
  • chr(x) — возвращает символ с номером х, в MySQL это функция char;
  • length(string) — возвращает длину строки;
  • lower(string) — понижает регистр букв;
  • upper(string) — повышает регистр букв;
  • trim(string) — удаляет пробелы с обоих концов строки;
  • replace(string1, c1, c2) — заменяет все вхождения символа/подстроки c1 на c2. Для простого удаления всех вхождений c1, в качестве третьего аргумента надо указать пустую строку (»). В Oracle третий аргумент не обязателен, и по умолчанию равен пустой строке;
  • substr(string, pos, len) — возвращает подстрку с позиции pos и длины len.

В рассматриваемых СУБД для обработки времени мало общего. Самый минимум у Oraсle:

  • current_date — глобальная переменная содержащая текущую дату. Можно использовать и в других СУБД;
  • trunc(d,s) — приводит дату к началу указанной временной отметки, например к началу месяца. В PostgreSQL есть аналогичная функция date_trunc(s,d). В MySQL для этих целей может использоваться функция date_format(d,s), но она возвращает результат в виде строки;
  • add_months(d,n) — добавляет к дате указанное число месяцев;
  • last_day(d) — последний день месяца, содержащегося в аргументе;
  • months_between(d1,d2) — возвращает число месяцев между датами.

Ниже приведены допустимые форматы в строковом параметре s для функций trunc и date_trunc соответственно:

  • квартал — q, quarter;
  • год — yyyy, year;
  • месяц — mm, month;
  • неделя — ww, week;
  • день — dd, day;
  • час — hh, hour;
  • минута — mi, minute.

Такие функции как last_day в других СУБД реализуются с помощью арифметики времени и преобразования типов. Так что при желании можно написать соответствующую функцию. Ниже приведена выборка последнего дня указанной даты.

— для PostgreSQL
select cast( (date_trunc(‘month’, date ‘2009-01-15’)
+ interval ‘1 month’) as date) — 1 as d

— для MySQL
select date ( date_format(‘2009-01-15′,’%Y-%m-01’))
+ interval 1 month
— interval 1 day as d

Преобразование типов

— MySQL
select CAST(‘5. 3’ AS decimal)+2
select CAST( (select ‘5. 3’) AS decimal(6,2))+2. 0

— Oracle
select CAST(‘5,22’ AS double precision) +2 from dual

— PostgreSQL
select CAST(‘5. 22’ AS double precision) +2

В PostgreSQL более расширенные возможности по преобразованию. Во-первых, можно добавить собственное преобразование для встроенных и пользовательских типов. Во-вторых, есть собственный более удобный оператор преобразования типов ::.

select cast(‘tru’ as boolean);
select cast(‘fa’ as boolean);
— ошибка, строка не похожа на ‘true’, ‘false’
— и не равна строкам ‘1’ или ‘0’
select cast(‘ok’ as boolean)

— создадим функцию преобразования
— просто указываем какие строки
— понимать как true значение,
— все остальные строки будут false значением
CREATE OR REPLACE FUNCTION to_bool(varchar)
RETURNS boolean
AS $$
SELECT $1 = ‘true’ or $1 = ‘tru’ or
$1 = ‘tr’ or $1 = ‘t’
or $1 = ‘1’ or $1=’ok’$$
LANGUAGE SQL;

— создаем преобразование типа varchar в boolean
CREATE CAST (varchar AS boolean)
WITH FUNCTION to_bool(varchar)
AS ASSIGNMENT;

— теперь можно так
select cast ( ‘ok’::varchar as boolean);
select cast( varchar ‘ok’ as boolean);
select ‘ok’::varchar::boolean;

— уничтожение преобразования
DROP CAST IF EXISTS (varchar AS boolean) ;

В большинстве случае необходимо преобразование в строку либо из строки. Для этого случаяСУБД предоставляют дополнительные функции.

функции Oracle

В этих функциях format описание формата даты или числа, а nlsparams — национальные параметры. Формат строки для даты задается следующими элементами:

Формат числовой строки задается следующими элементами:

select to_char(sysdate,
‘»системное время: «DD-MON-YY hh24. mi:ss CC «век»‘)
as c
from dual;

select to_date(‘01012009′,’ddmmyyyy’) as c
from dual;

select to_char(-10000,’99G999D99L’,
‘NLS_NUMERIC_CHARACTERS = »,. »
NLS_CURRENCY = »baks» ‘) as c
from dual;

select to_char(9. 12345,’099. 99’) as c
from dual

функции PostgreSQL

  • to_char(timestamp, format) — время в строку;
  • to_char(interval, format) — интервал времени в строку;
  • to_char(number, format) — число в строку;
  • to_date(str, format) — строку в дату;
  • to_number(str, format) — строку в число;
  • to_timestamp(str, format) — строку во время.

Основные элементы форматирования совпадают с Oracle.

функции MySQL

При хранении даты в MySQL под типом Date (), она имеет формат 2011-07-11 (год-месяц-день). В некоторых случаях даже не имея разделителя 20110711.

Поскольку в русскоязычных странах более привычным к восприятию считается формат 11. 2011 (день. месяц. год), то при выводе даты из базы данных, возникает необходимость в её преобразовании.

Преобразовать дату можно несколькими способами.

  • при помощи php кода
  • воспользовавшись командой DATE_FORMAT () при выборке из базы.

Первый способ применяется в тех случаях, когда необходимо вывести небольшое количество записей или же когда разработчик не подозревает о существовании второго способа.

Второй способ применим во всех случаях, вне зависимости сколько записей необходимо извлечь из базы, при этом он осуществляет минимальную нагрузку на сервер в отличии от способа с php кодом.

Рассмотрим пример выполнения:

Для извлечения и преобразования даты напишем следующий код:

$message = mysql_fetch_array(mysql_query(«SELECT DATE_FORMAT(send_data, ‘%e. %Y’) FROM message»));

Далее в том месте где необходимо вывести преобразованную дату, выводим массив $message любой, удобной для вас командой:

к примеру если в send_data находится 2011-05-03 то мы получим 03. 2011.

Номер индекса в массиве $message указываем каким по счету начиная от 0, в команде SELECT извлекается необходимое значение с преобразованной датой. К примеру при запросе:

$message = mysql_fetch_array(mysql_query(«SELECT title, text, DATE_FORMAT(send_data, ‘%e. %Y’) FROM message»));

вывод даты будет осуществляться с индексом 2:

Преобразовать дату при помощи DATE_FORMAT() можно в любой вид и очередность при помощи подстановки ключей.

  • date_format(date,format) — дату в строку;
  • time_format(time,format) — время в строку;
  • format(number,precision) — число в cтроку типа ‘#,###,###.##’, где число знаков определяется вторым аргументом.

Ниже приведен список основных элементов форматирования для даты и времени:

  • %c — месяц числом;
  • %d — день месяца;
  • %H — часы (от 0 до 24);
  • %h — часы (1 до 12);
  • %i — минуты;
  • %s — секунды;
  • %T — время в формате «hh:mm:ss»;
  • %Y — год, четыре цифры;
  • %y — год, две цифры.

select date_format(date ‘2010-02-01’,
‘%c месяца %d дней %Y год’) as c

CREATE TABLE

Инструкция CREATE TABLE используется для создания новой таблицы в базе данных.

Синтаксис

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,

Параметры столбцов задают имена столбцов таблицы.

Параметр datatype указывает тип данных, которые может содержать столбец (например, varchar, integer, date и т.

Совет: Для получения обзора доступных типов данных перейдите Справочник Типы данных.

Пример CREATE TABLE

В следующем примере создается таблица «Persons», содержащая пять столбцов: PersonID, LastName, FirstName, Address, и City:

CREATE TABLE Persons
(
   
PersonID int,
   
LastName varchar(255),
   
FirstName varchar(255),
   
Address varchar(255),
   
City varchar(255)
);

Попробуйте сами »

Столбец PersonID имеет тип int и будет содержать целое число.

Столбцы LastName, FirstName, Address, and City имеют тип varchar и будут содержать символы,
а максимальная длина этих полей составляет 255 символов.

Пустая таблица «Persons» теперь будет выглядеть так:

Совет: Пустая таблица «Persons» теперь может быть заполнена данными с помощью инструкции SQL INSERT INTO.

Создать таблицу, используя другую таблицу

Копия существующей таблицы также может быть создана с помощью команды CREATE TABLE.

Новая таблица получает те же определения столбцов. Можно выбрать все столбцы или отдельные столбцы.

Если вы создадите новую таблицу, используя существующую таблицу, новая таблица будет заполнена существующими значениями из старой таблицы.

CREATE TABLE new_table_name AS   
SELECT column1, column2,. FROM
existing_table_name    WHERE

Следующий SQL создает новую таблицу под названием «TestTables» (которая является копией таблицы «Customers»):

CREATE TABLE TestTable ASSELECT customername, contactnameFROM customers;

Проверьте себя с помощью упражнений

Упражнение:Напишите правильный оператор SQL для создания новой таблицы с именем Persons. (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);