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

❮ Предыдущая Следующая Глава ❯

SQL Даты

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

Функции даты MySQL

В следующей таблице перечислены наиболее важные встроенные функции даты в MySQL:

функция Описание
NOW() Возвращает текущую дату и время
CURDATE() Возвращает текущую дату
CURTIME() Возвращает текущее время
DATE() Извлечение даты часть даты или выражения даты / времени
EXTRACT() Возвращает одну часть даты / времени
DATE_ADD() Добавляет заданный интервал времени до даты
DATE_SUB() Вычитает заданный интервал времени от даты
DATEDIFF() Возвращает количество дней между двумя датами
DATE_FORMAT() дата Отображение / времени данные в различных форматах

SQL Дата сервера Функции

В следующей таблице перечислены наиболее важные встроенные функции даты в SQL Server:

функция Описание
GETDATE() Возвращает текущую дату и время
DATEPART() Возвращает одну часть даты / времени
DATEADD() Добавляет или вычитает определенный интервал времени от даты
DATEDIFF() Возвращает время между двумя датами
CONVERT() дата Отображение / времени данные в различных форматах

SQL Дата типы данных

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

  • ДАТА — формат YYYY-MM-DD
  • DATETIME — формат: YYYY-MM-DD HH: MI: SS
  • TIMESTAMP — формат: YYYY-MM-DD HH: MI: SS
  • ГОД — формат YYYY или YY

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

  • ДАТА — формат YYYY-MM-DD
  • DATETIME — формат: YYYY-MM-DD HH: MI: SS
  • SMALLDATETIME — формат: YYYY-MM-DD HH: MI: SS
  • TIMESTAMP — формат: уникальный номер

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

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

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

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

Мы используем следующую ЗЕЬЕСТ:

SELECT * FROM Orders WHERE OrderDate=’2008-11-11′

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

OrderId ProductName OrderDate
1 Geitost
2008-11-11
3 Mozzarella di Giovanni 2008-11-11

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

OrderId ProductName OrderDate
1 Geitost 2008-11-11 13:23:44
2 Камамбер Пьерро 2008-11-09 15:45:21
3 Моцарелла ди Джованни 2008-11-11 11:12:01
4 Маскарпоне Fabioli 2008-10-29 14:56:59

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

SELECT * FROM Orders WHERE OrderDate=’2008-11-11′

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

Tip: Если вы хотите сохранить ваши запросы простой и легкий в обслуживании, не позволяют компоненты времени ваши даты!

❮ Предыдущая Следующая Глава ❯

Фильтрация по полю DateTime в MySQL

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

Давайте перечислим основные поля, используемые для хранения значений времени:

DATE – тип поля который хранит дату в формате ГГГГ-ММ-ДД;
DATETIME – поле этого типа хранит дату и время, в формате ГГГГ-ММ-ДД ЧЧ:ММ:CC;
TIMESTAMP – временная метка, формат хранения зависит от версии MySQL, и режима работы сервера баз данных.

Как правило, TIMESTAMP представляет некоторое числовое значение, которое удобно использовать при фильтрации полей, традиционными операторами сравнения. Что касается формата DATETIME, то здесь уже операция фильтрации выполняется немного иначе. Давайте рассмотрим простые примеры, как можно фильтровать записи, если у вас таблица хранит данные времени в формате DATETIME.

Фильтрация по дате в формате DateTime

В качестве задачи, мы рассмотрим пример выборки записей, до определённой даты, пусть это будет 24.05.2020. Для этого чтобы применить фильтрацию к полю DateTime мы воспользуемся функцией DATE():

 

SELECT * FROM m_table WHERE DATE(add_date) < '2020-05-24'

Данный запрос сделает выборку данных из таблицы m_table, выбирая записи, у которых значение даты в поле add_date не превышает указанное в условии. Либо аналогичным образом можно отфильтровать записи, у которых значение поле add_date больше указанной даты:


SELECT * FROM m_table WHERE DATE(add_date) > '2020-05-24'

Как вы можете заметить, поменялся только лишь оператор сравнения.

Фильтрация по диапазону значений DateTime

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


SELECT * FROM m_table
WHERE DATE(add_date) BETWEEN '2020-04-01' AND '2020-05-01'

Команда BETWEEN в запросе как раз указывает на то, что в условии будет использоваться диапазон значений.

Фильтрация по диапазону значений DateTime с временными интервалами

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


SELECT * FROM m_table
WHERE DATE(add_date) BETWEEN DATE(CURRENT_DATE() - INTERVAL 2 WEEK) AND 
DATE(CURRENT_DATE() + INTERVAL 4 DAY)

Опять же, здесь задействованы такие функции как DATE(), CURRENT_DATE(), а как же команды временного интервала INTERVAL. MySQL позволяет выполнять математические операции с датами, оперируя вместе с этими функциями. Так можно увеличивать или уменьшать некоторые временные значения в запросе, динамически, в зависимости от текущего значения времени.

Функции для работы с датой и временем в 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

❮ Предыдущий Далее ❯

Даты MySQL

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

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

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

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

  • ДАТА — формат ГГГГ-ММ-ДД
  • ДАТАВРЕМЯ — формат: ГГГГ-ММ-ДД ЧЧ:МИ:СС
  • TIMESTAMP — формат: ГГГГ-ММ-ДД ЧЧ:МИ:СС
  • ГОД — формат ГГГГ или ГГ

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

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

Посмотрите на следующую таблицу:

Таблица заказов

Идентификатор заказа Название продукта Дата заказа
1 Гейтост 11.11.2008
2 Камамбер Пьеро 09. 11.2008
3 Моцарелла ди Джованни 11.11.2008
4 Маскарпоне Фабиоли 29.10.2008

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

Мы используем следующий оператор SELECT :

SELECT * FROM Orders WHERE OrderDate=’2008-11-11′

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

Order49 Название продукта Дата заказа
1 Гейтост 11.11.2008
3 Моцарелла ди Джованни 11.11.2008

Примечание: Две даты можно легко сравнить, если нет временной составляющей!

Теперь предположим, что таблица «Заказы» выглядит так (обратите внимание на добавлен компонент времени в столбце «Дата заказа»):

OrderId Название продукта Дата заказа
1 Гейтост 11-11-2008 13:23:44
2 Камамбер Пьеро 09. 11.2008 15:45:21
3 Моцарелла ди Джованни 11-11-2008 11:12:01
4 Маскарпоне Фабиоли 2008-10-29 14:56:59

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

SELECT * FROM Orders WHERE OrderDate=’2008-11-11′

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

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

❮ Предыдущий Следующий ❯


ВЫБОР ЦВЕТА

Лучшие учебники
Учебник HTML
Учебник CSS
Учебник JavaScript
Как Учебник
Учебник SQL
Учебник Python
Учебник W3.CSS
Учебник Bootstrap
Учебник PHP
Учебник Java
Учебник C++
Учебник jQuery
Top3 Reference

900 Справочник по HTML
Справочник по CSS
Справочник по JavaScript
Справочник по SQL
Справочник по Python
Справочник по W3. CSS
Справочник по Bootstrap
Справочник по PHP
Цвета HTML
Справочник по Java
Справочник по Angular
Справочник по jQuery

Основные примеры
Примеры HTML
Примеры CSS
Примеры JavaScript
Примеры инструкций
Примеры SQL
Примеры Python
Примеры W3.CSS
Примеры Bootstrap
Примеры PHP
Примеры Java
Примеры XML
Примеры jQuery

| О

W3Schools оптимизирован для обучения и обучения. Примеры могут быть упрощены для улучшения чтения и обучения. Учебники, ссылки и примеры постоянно пересматриваются, чтобы избежать ошибок, но мы не можем гарантировать полную правильность всего содержания. Используя W3Schools, вы соглашаетесь прочитать и принять наши условия использования, куки-файлы и политика конфиденциальности.

Copyright 1999-2023 Refsnes Data. Все права защищены.
W3Schools работает на основе W3.CSS.

Работа с датами и временем в MySQL

Работа с датами и временем в MySQL. Часть 1

25 февраля 2022 г. by Robert Gravelle

Подавляющее большинство баз данных хранят большое количество «временных» данных. Временные данные — это просто данные, представляющие состояние во времени. Организация может собирать временные данные по разным причинам, например, для анализа погодных условий и других переменных окружающей среды, мониторинга условий движения, изучения демографических тенденций и т. д. Предприятиям также постоянно необходимо хранить временные данные о том, когда были размещены заказы, пополнены запасы, нанятый персонал и целый ряд другой информации об их повседневных делах.

Возможно, вы удивитесь, узнав, что реляционные базы данных не хранят даты и время одинаковым образом. MySQL особенно директивен. Например, он хранит значения даты, используя универсальный формат гггг-мм-дд. Этот формат является фиксированным и не может быть изменен. Вы можете предпочесть использовать формат мм-дд-гггг, но это невозможно. Однако вы можете использовать функцию DATE_FORMAT для форматирования даты так, как вы хотите, на уровне представления, обычно в приложении. В первых двух частях о работе с датами и временем в MySQL мы рассмотрим временные типы данных MySQL, начиная с DATE, TIME и DATETIME.

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

Имя типа Описание
ДАТА Значение даты в формате ГГГГ-ММ-ДД 9формат 0019
ВРЕМЯ Значение времени в формате чч:мм:сс
ДАТАВРЕМЯ Значение даты и времени в формате ГГГГ-ММ-ДД. формат чч:мм:сс
TIMESTAMP Значение метки времени в формате ГГГГ-ММ-ДД чч:мм:сс формат
ГОД Значение года в формате ГГГГ или ГГ формат

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

MySQL использует 3 байта для хранения значения DATE. Значения DATE находятся в диапазоне от 1000-01-01 до 9999-12-31. Более того, когда строгий режим отключен, MySQL преобразует любую недопустимую дату, например, 2015-02-30, в значение нулевой даты 0000-00-00. В Navicat 16 вы можете выбрать тип ДАТЫ в дизайнере таблиц из раскрывающегося списка Типы:

Чтобы установить значение ДАТЫ, вы можете просто выбрать его с помощью элемента управления календарем:

Конечно, вы также можете вставить ДАТУ с помощью оператора INSERT:

MySQL использует формат «ЧЧ:ММ:СС» для запроса и отображения значения времени, представляющего время суток, которое находится в пределах 24 часов. Для представления временного интервала между двумя событиями MySQL использует формат «ЧЧЧ:ММ:СС», который превышает 24 часа.

Вот тип TIME в раскрывающемся списке Navicat 16 Types:

Чтобы установить значение TIME, Navicat предоставляет элемент управления TIME INPUT:

Вот оператор INSERT, который устанавливает время начала и окончания:

Довольно часто, вам нужно будет хранить дату и время. Для этого вы можете использовать тип MySQL DATETIME. По умолчанию значения DATETIME находятся в диапазоне от 1000-01-01 00:00:00 до 9999-12-31 23:59:59. Когда вы запрашиваете данные из столбца DATETIME, MySQL отображает значение DATETIME в том же формате ГГГГ-ММ-ДД ЧЧ:ММ:СС.

Значение DATETIME использует 5 байтов для хранения. Кроме того, значение DATETIME может включать завершающую дробную часть секунды до микросекунд в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС[.fraction], например, 2015-12-20 10:01:00.999999.

Для ввода значений DATETIME Navicat предоставляет элемент управления DATETIME INPUT, который объединяет элементы управления DATE и TIME.

No related posts.