Изучаем MySQL: работа с датами и временем
В этой статье мы рассмотрим основы работы с датой и временем в MySQL.
Формат даты и времени
MySQL date format поддерживает несколько форматов даты и времени. Их можно определить следующим образом:
DATE — хранит значение даты в виде ГГГГ-ММ-ДД. Например, 2008-10-23.
DATETIME — хранит значение даты и времени в виде ГГГГ-MM-ДД ЧЧ:ММ:СС. Например, 2008-10-23 10:37:22. Поддерживаемый диапазон дат и времени: 1000-01-01 00:00:00 до 9999-12-31 23:59:59
TIMESTAMP — похож на DATETIME с некоторыми различиями в зависимости от версии MySQL и режима, в котором работает сервер.
Создание полей даты и времени
Форматы даты и времени
Наиболее часто используемым разделителем для дат является тире ( — ), а для времени — двоеточие ( : ). Но мы можем использовать любой символ, или вообще не добавлять никакого символа.
Например, все следующие форматы являются правильными:
Функции даты и времени
MySQL содержит множество функций, которые используются для обработки даты и времени. В приведенной ниже таблице представлен список наиболее часто используемых функций:
| Функция | Описание |
| ADDDATE() | Добавляет дату. |
| ADDTIME() | Добавляет время. |
| CONVERT_TZ() | Конвертирует из одного часового пояса в другой. |
| CURDATE() | Возвращает текущую дату. |
| CURTIME() | Возвращает текущее системное время. |
| DATE_ADD() | Добавляет одну дату к другой. |
| MySQL DATE FORMAT () | Задает указанный формат даты. |
| DATE() | Извлекает часть даты из даты или выражения дата-время. |
| DATEDIFF() | Вычитает одну дату из другой. |
| DAYNAME() | Возвращает день недели. |
| DAYOFMONTH() | Возвращает день месяца (1-31). |
| DAYOFWEEK() | Возвращает индекс дня недели из аргумента. |
| DAYOFYEAR() | Возвращает день года (1-366). |
| EXTRACT | Извлекает часть даты. |
| FROM_DAYS() | Преобразует номер дня в дату. |
| FROM_UNIXTIME() | Задает формат даты в формате UNIX. |
| MySQL DATE_SUB () | Вычитает одну дату из другой. |
| HOUR() | Извлекает час. |
| LAST_DAY | Возвращает последний день месяца для аргумента. |
| MAKEDATE() | Создает дату из года и дня года. |
| MAKETIME () | Возвращает значение времени. |
| MICROSECOND() | Возвращает миллисекунды из аргумента. |
| MINUTE() | Возвращает минуты из аргумента. |
| MONTH() | Возвращает месяц из переданной даты. |
| MONTHNAME() | Возвращает название месяца. |
| NOW() | Возвращает текущую дату и время. |
| PERIOD_ADD() | Добавляет интервал к месяцу-году. |
| PERIOD_DIFF() | Возвращает количество месяцев между двумя периодами. |
| QUARTER() | Возвращает четверть часа из переданной даты в качестве аргумента. |
| SEC_TO_TIME() | Конвертирует секунды в формат ‘ЧЧ:MM:СС’. |
| SECOND() | Возвращает секунду (0-59). |
| MySQL STR TO DATE () | Преобразует строку в дату. |
| SUBTIME() | Вычитает время. |
| SYSDATE() | Возвращает время, в которое была выполнена функция. |
| TIME_FORMAT() | Задает формат времени. |
| TIME_TO_SEC() | Возвращает аргумент, преобразованный в секунды. |
| TIME() | Выбирает часть времени из выражения, передаваемого в качестве аргумента. |
| TIMEDIFF() | Вычитает время. |
| TIMESTAMP() | С одним аргументом эта функция возвращает дату или выражение дата-время. С двумя аргументами возвращается сумма аргументов. |
| TIMESTAMPADD() | Добавляет интервал к дате-времени. |
| TIMESTAMPDIFF() | Вычитает интервал из даты — времени. |
| TO_DAYS() | Возвращает аргумент даты, преобразованный в дни. |
| UNIX_TIMESTAMP() | Извлекает дату-время в формате UNIX в формат, принимаемый MySQL. |
| UTC_DATE() | Возвращает текущую дату по универсальному времени (UTC). |
| UTC_TIME() | Возвращает текущее время по универсальному времени (UTC). |
| UTC_TIMESTAMP() | Возвращает текущую дату-время по универсальному времени (UTC). |
| WEEK() | Возвращает номер недели. |
| WEEKDAY() | Возвращает индекс дня недели. |
| WEEKOFYEAR() | Возвращает календарную неделю даты (1-53). |
| YEAR() | Возвращает год. |
| YEARWEEK() | Возвращает год и неделю. |
Вы можете попробовать сочетание нескольких функций в одном запросе (например, чтобы найти день недели):
Внесение значений даты и времени в столбцы таблицы
Данный запрос создает заказ для указанного элемента с датой, временем выполнения заказа, и интервалом через две недели после этого в качестве даты доставки:
Точно так же можно заказать товар с датой доставки через два месяца:
Извлечение данных по дате и времени
В MySQL мы можем отфильтровать извлеченные данные в зависимости от даты и времени. Например, мы можем извлечь только те заказы, доставка которых запланирована на ноябрь:
Заключение
SQL Работа с датами
SQL DATE
Пока ваши данные содержат только часть даты, ваши запросы будут работать так, как и ожидалось. Однако, если задействована временная часть, все становится еще сложнее.
SQL Типы данных даты
MySQL поставляется со следующими типами данных для хранения даты или значения даты/времени в базе данных:
SQL Server поставляется со следующими типами данных для хранения даты или значения даты/времени в базе данных:
Примечание: Типы дат выбираются для столбца при создании новой таблицы в базе данных!
SQL Работа с датами
Вы можете легко сравнить две даты, если нет никакого компонента времени!
Предположим, что у нас есть следующая таблица «Orders»:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 2 | Camembert Pierrot | 2008-11-09 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
| 4 | Mascarpone Fabioli | 2008-10-29 |
Теперь мы хотим выбрать записи с порядковым номером «2008-11-11» из приведенной выше таблицы.
Мы используем следующую инструкцию SELECT:
Результирующий набор будет выглядеть следующим образом:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
Теперь предположим, что таблица «Orders» выглядит следующим образом (обратите внимание на компонент time в столбце «OrderDate»):
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 13:23:44 |
| 2 | Camembert Pierrot | 2008-11-09 15:45:21 |
| 3 | Mozzarella di Giovanni | 2008-11-11 11:12:01 |
| 4 | Mascarpone Fabioli | 2008-10-29 14:56:59 |
Если мы используем тот же оператор SELECT, что и выше:
Мы не получим никакого результата! Это происходит потому, что запрос ищет только даты без временной части.
Совет: Чтобы ваши запросы были простыми и удобными в обслуживании, не допускайте компонентов времени в ваших датах!
SQL — урок 2: Работа с датой и временем
Базы данных › SQL — урок 2: Работа с датой и временем
Литералы
Литералы служат для непосредственного представления данных, ниже приведен список
стандартных литерал:
Двойной апостроф интерпретируется в строковой литерале как апостроф в тексте.
В MySQL для временных литерал строка должна быть заключена в скобки: DATE (‘2008-01-10’).
Формат даты по умолчанию обычно определяется настройкой БД. Продвинутые СУБД могут
автоматически определять некоторые форматы (DATE (‘2008.01.10’))
или как в Oracle имеют функцию преобразования (to_date(‘01.02.2003′,’dd.mm.yyyy’)).
Для упрощения во многих СУБД там, где подразумевается дата,
перед строкой необязательно ставить имя типа.
Интервал времени
Синтаксис и реализация интервалов отличается на разных СУБД.
Oracle
Данный тип разделен на два: интервал по годам и интервал по дням.
В первом случае можно указать точность интервала только до месяца (по умолчанию год).
Во втором случае до различных долей секунды. Кроме этого указывается точность значения
временных промежутков в количестве цифр. Если точность не совпадает, то выводится сообщение
об ошибке.
PostgreSQL
интервалы указываются в виде строки, в которой перечисляются значение и тип промежутка:
Слова можно употреблять и во множественном числе.
Если интервал начинается с дней, то можно использовать короткий формат строки как
в Oracle для дневных интервалов.
MySQL
Только сложные интервалы, состоящие из более одного типа промежутков, указываются в строке.
Для этих целей введены дополнительные по сравнению с PostgreSQL имена для промежутков:
MySQL интервалы используются в выражениях с временными типами данных,
использовать их в качестве конечного типа для столбцов запрещено.
Выражения и операции
Для построения выражений SQL включает стандартные операции, ряд дополнительных предикатов
(булевских конструкций) и функций. В MySQL для встроенных функций между именем и открывающей
скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной функции в БД.
Oracle не поддерживает логические выражения в перечислении select.
cтроковые операции
|| — соединение строк, в некоторых СУБД операнды автоматически преобразуются в
строковый тип. В MS Access используется &
алгебраические операции
Операции + и — также используются при работе со временем и интервалами.
В Oracle и PostgreSQL возможна разница между датами.
Результат возвращается в виде интервала в днях. Ниже приведен пример добавления к дате
интервала.
Ко времени можно прибавлять целое число, но результат зависит от конкретной СУБД.
операции отношения
логические операции и предикаты
выражение IN (значение1. значениеn)
В качестве множества значений может служить корректная выборка
строковое_выражение LIKE шаблон [ESCAPE еск_символ]
Знак % в шаблоне интерпретируется как строка любой длины, знак _
как любой символ. В конструкции ESCAPE еск_символ указывается символ ESCAPE
последовательности, который отменит обычную интерпретацию символов ‘_’ и ‘%’.
В последних стандартах включены предикаты SIMILAR и LIKE_REGEX расширяющие возможности
LIKE, используя в качестве шаблона регулярные выражения.
условные выражения
Ниже приведен пример использования выражения в запросе выбора данных.
прочие операции
В каждой СУБД свой набор операций, выше были приведены наиболее употребительные.
Например, в PosgreSQL можно использовать и такие операции:
Обзор функций
В арсенале каждой СУБД обязательно имеется набор встроенных функций для
обработки стандартных типов данных. В MySQL для встроенных функций между именем и
открывающей скобкой не должно быть пробелов, иначе будет сообщение об отсутствии подобной
функции в БД. В некоторых СУБД, как Oracle, если функция не имеет аргументов,
то скобки можно опустить.
математические функции
Тригонометрические функции работают с радианами:
строковые функции
работа с датами
В рассматриваемых СУБД для обработки времени мало общего. Самый минимум у Oraсle:
Ниже приведены допустимые форматы в строковом параметре s для функций trunc и date_trunc соответственно:
Такие функции как last_day в других СУБД реализуются с помощью арифметики времени и преобразования типов.
Так что при желании можно написать соответствующую функцию. Ниже приведена выборка последнего дня указанной даты.
Преобразование типов
Множество типов разрешенные для преобразования в констркуции CAST AS определяется
реализацией СУБД. Так в MySQL может преобразовать только следующие типы: binary[(n)],
char[(n)], date, datetime, decimal[(m[,d])], signed [integer], time, unsigned [integer].
А в Oracle, кроме преобразования встроенных типов, можно преобразовывать выборки со
множеством записей в массивы.
В большинстве случае необходимо преобразование в строку либо из строки. Для этого случаяСУБД предоставляют дополнительные функции.
функции Oracle
В этих функциях format описание формата даты или числа, а nlsparams — национальные
параметры. Формат строки для даты задается следующими элементами:
Формат числовой строки задается следующими элементами:
функции PostgreSQL
Основные элементы форматирования совпадают с Oracle.
функции MySQL
При хранении даты в MySQL под типом Date (), она имеет формат 2011-07-11 (год-месяц-день). В некоторых случаях даже не имея разделителя 20110711.
Поскольку в русскоязычных странах более привычным к восприятию считается формат 11.07.2011 (день.месяц.год), то при выводе даты из базы данных, возникает необходимость в её преобразовании.
Преобразовать дату можно несколькими способами.
Первый способ применяется в тех случаях, когда необходимо вывести небольшое количество записей или же когда разработчик не подозревает о существовании второго способа.
Второй способ применим во всех случаях, вне зависимости сколько записей необходимо извлечь из базы, при этом он осуществляет минимальную нагрузку на сервер в отличии от способа с php кодом.
Рассмотрим пример выполнения:
Для извлечения и преобразования даты напишем следующий код:
$message = mysql_fetch_array(mysql_query(«SELECT DATE_FORMAT(send_data, ‘%e.%m.%Y’) FROM message»));
$message = mysql_fetch_array(mysql_query(«SELECT title, text, DATE_FORMAT(send_data, ‘%e.%m.%Y’) FROM message»));
вывод даты будет осуществляться с индексом 2:
Преобразовать дату при помощи DATE_FORMAT() можно в любой вид и очередность при помощи подстановки ключей.
Ниже приведен список основных элементов форматирования для даты и времени:
Типы данных и функции даты и времени (Transact-SQL)
В разделах этой статьи представлен обзор всех типов данных и функций даты и времени Transact-SQL.
Типы данных даты и времени
Типы данных даты и времени Transact-SQL перечислены в следующей таблице:
| Тип данных | Формат | Диапазон | Точность | Объем памяти (в байтах) | Определяемая пользователем точность в долях секунды | Смещение часового пояса |
|---|---|---|---|---|---|---|
| time | чч:мм:сс[.ннннннн] | От 00:00:00.0000000 до 23:59:59.9999999 | 100 наносекунд | от 3 до 5 | Да | Нет |
| date | ГГГГ-ММ-ДД | От 0001-01-01 до 31.12.99 | 1 день | 3 | Нет | Нет |
| smalldatetime | ГГГГ-ММ-ДД чч:мм:сс | От 01.01.1900 до 06.06.2079 | 1 минута | 4 | нет | Нет |
| datetime | ГГГГ-ММ-ДД чч:мм:сс[.ннн] | От 01.01.1753 до 31.12.9999 | 0,00333 секунды | 8 | Нет | Нет |
| datetime2 | ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] | От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 | 100 наносекунд | От 6 до 8 | Да | Нет |
| datetimeoffset | ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] [+|-]чч:мм | От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 (время в формате UTC) | 100 наносекунд | От 8 до 10 | Да | Да |
Тип данных Transact-SQL rowversion не относится к типам данных даты и времени. Тип данных timestamp является устаревшим синонимом rowversion.
Функции даты и времени
В следующих таблицах приводятся функции даты и времени Transact-SQL. Дополнительные сведения о детерминизме см. в статье Детерминированные и недетерминированные функции.
Функции, возвращающие значения системной даты и времени
Transact-SQL наследует все значения системной даты и времени от операционной системы компьютера, на котором работает экземпляр SQL Server.
Высокоточные функции системной даты и времени
SQL Server 2019 (15.x) получает значения даты и времени с помощью функции GetSystemTimeAsFileTime() Windows API. Точность зависит от физического оборудования и версии Windows, в которой запущен экземпляр SQL Server. Точность возвращаемых значений этого API-интерфейса задана равной 100 нс. Точность может быть определена с помощью метода GetSystemTimeAdjustment() API-интерфейса Windows.
date (Transact-SQL)
Определяет дату в SQL Server.
Описание типа данных date
| Свойство | Значение |
|---|---|
| Синтаксис | date |
| Использование | DECLARE @MyDate date |
CREATE TABLE Таблица1 ( Столбец1 date )
(используется для клиента нижнего уровня)
Дополнительные сведения см. в подразделе «Обратная совместимость для клиентов низкого уровня» следующего раздела.
От 1 января 1 года до 31 декабря 9999 года нашей эры (от 15 октября 1582 года до 31 декабря 9999 года для Informatica).
ММ обозначает 2 цифры, которые представляют месяц и принимают значения от 01 до 12.
ДД обозначает две цифры, представляющие день указанного месяца и принимающие значения от 01 до 31 в зависимости от месяца.
Это значение используется как присоединяемая часть даты при неявном преобразовании данных типа time в значение типа datetime2 или datetimeoffset.
Поддерживаемые форматы строковых литералов для типа данных date
В приведенной ниже таблице перечислены допустимые форматы строковых литералов для типа данных date.
| Числовой | Описание |
|---|---|
| mdy [гг]гг-[м]м-дд | Сочетания [м]м, дд и [гг]гг представляют в строке месяц, день и год, разделенные символом косой черты (/), дефисом (-) или точкой (.). Поддерживаются только двух- и четырехзначные форматы записи года. Для записи года рекомендуется всегда использовать четырехзначный формат. Чтобы указать целое число в промежутке от 0001 до 9999, представляющее пороговое значение года при преобразовании двухзначной записи в четырехзначную, необходимо использовать параметр конфигурации сервера two digit year cutoff. Примечание. Для Informatica значение ГГГГ ограничено диапазоном от 1582 до 9999. Если двузначное число, обозначающее год, меньше или равно двузначному числу из последних двух цифр порогового года, год относится к тому же столетию, что и пороговый год. Если двузначное число, обозначающее год, больше двузначного числа из последних двух цифр порогового года, год относится к столетию, которое предшествует столетию порогового года. Например, если пороговое значение года для двухзначной записи равно 2049, то год, обозначенный двумя цифрами 49, интерпретируется как 2049, а год, обозначенный двумя цифрами 50, интерпретируется как 1950. Формат даты по умолчанию определяется текущими настройками языковых стандартов. Формат даты можно изменить с помощью инструкций SET LANGUAGE и SET DATEFORMAT. Формат гдм не поддерживается для типа данных date. |
| Алфавитный формат | Описание |
|---|---|
| мес [дд][,] гггг гггг [дд] мес | Значение мес означает полное или сокращенное название месяца на текущем языке. Запятые являются необязательными, регистр букв не учитывается. Во избежание неоднозначности используйте четырехзначную запись года. Если не указано число месяца, подразумевается первое число месяца. |
| ISO 8601 | Описание |
|---|---|
| ГГГГ-ММ-ДД ГГГГММДД | То же, что и стандарт SQL. Этот формат является единственным международным стандартом. |
| Без разделителей | Описание |
|---|---|
| [гг]ггммдд гггг[мм][дд] | Данные типа date могут быть определены с помощью четырех, шести или восьми цифр. Строка из шести или восьми цифр всегда интерпретируется как гмд. На месяц и день всегда должно приходиться по две цифры. Строка из четырех цифр воспринимается как значение года. |
| ODBC | Описание |
|---|---|
| Зависит от API-интерфейса ODBC. |
| Формат W3C XML | Описание | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| гггг-мм-ддTZD | Введен для использования в XML и SOAP. Соответствие стандартам ANSI и ISO 8601Значение date соответствует стандартному определению ANSI SQL для григорианского календаря: «Замечание 85 — данные типа Datetime позволяют хранить даты григорианского календаря в виде чисел диапазона от 0001-01-01 CE до 9999–12–31 CE». Формат строковых литералов по умолчанию, используемый для клиентов низкого уровня, соответствует стандарту SQL, в котором он определен как ГГГГ-ММ-ДД. Данный формат аналогичен определению даты в стандарте ISO 8601. Для Informatica диапазон ограничен интервалом от 1582-10-15 (15 октября 1582 г.) до 9999-12-31 (31 декабря 9999 г.). Обратная совместимость для клиентов нижнего уровняНекоторые клиенты нижнего уровня не поддерживают типы данных time, date, datetime2 и datetimeoffset. В следующей таблице показано сопоставление типов экземпляра более высокого уровня SQL Server и клиентов низкого уровня.
Преобразование данных типа Date и TimeПри преобразовании в типы данных даты и времени SQL Server отвергает все значения, которые он не распознает как значения даты или времени. Сведения об использовании функций CAST и CONVERT c данными типов даты и времени см. в статье Функции CAST и CONVERT (Transact-SQL). Преобразование даты в другие типы данных даты и времениВ этом разделе описывается, что происходит при преобразовании типа данных date в другие типы даты и времени. Преобразование в time(n) завершается сбоем, и появляется сообщение об ошибке 206: «Конфликт типов операндов: date несовместим с time». Преобразование строковых литералов в датуПреобразование строковых литералов в типы данных даты и времени разрешается, если все части строк записаны в допустимом формате. Иначе возникает ошибка времени выполнения. Явные или скрытые преобразования, в которых не задан стиль преобразования типов данных даты и времени в строковые литералы, будут проведены в формате по умолчанию для текущего сеанса. В таблице ниже приводятся правила преобразования строковых литералов в тип данных date.
ПримерыВ следующем примере сравниваются результаты приведения строкового типа к каждому из типов данных date и time. Теперь вы знаете какие однокоренные слова подходят к слову Как пишется дата в sql, а так же какой у него корень, приставка, суффикс и окончание. Вы можете дополнить список однокоренных слов к слову "Как пишется дата в sql", предложив свой вариант в комментариях ниже, а также выразить свое несогласие проведенным с морфемным разбором. |