Тип полей DATE and TIME в MySQL

Кто знает, поведайте, пожалуйста. Чем отличаются и что дают типы полей: DATE, TIME, DATETIME, TIMESTAMP и YEAR?

Подробно обо всех типах данный mysql можно почитать тут: типы данных MySQL. По поводу перечисленных вами типов:

DATE Дата. Поддерживается интервал от ‘1000-01-01’ до ‘9999-12-31’. MySQL выводит значения DATE в формате ‘YYYY-MM-DD’, но можно установить значения в столбец DATE, используя как строки, так и числа.

TIME Время. Интервал от ‘-838:59:59’ до ‘838:59:59’. MySQL выводит значения TIME в формате ‘HH:MM:SS’, но можно устанавливать значения в столбце TIME, используя как строки, так и числа.

DATETIME Комбинация даты и времени. Поддерживается интервал от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’. MySQL выводит значения DATETIME в формате ‘YYYY-MM-DD HH:MM:SS’, но можно устанавливать значения в столбце DATETIME, используя как строки, так и числа

TIMESTAMP

Временная метка. Интервал от ‘1970-01-01 00:00:00’ до некоторого значения времени в 2037 году. MySQL выводит значения TIMESTAMP в форматах YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD или YYMMDD в зависимости от значений M: 14 (или отсутствующее), 12, 8, или 6; но можно также устанавливать значения в столбце TIMESTAMP, используя как строки, так и числа. Столбец TIMESTAMP полезен для записи даты и времени при выполнении операций INSERT или UPDATE, так как при этом автоматически вносятся значения даты и времени самой последней операции, если эти величины не введены программой. Можно также устанавливать текущее значение даты и времени, задавая значение NULL

YEAR Год в двухзначном или четырехзначном форматах (по умолчанию формат четырехзначный). Допустимы следующие значения: с 1901 по 2155, 0000 для четырехзначного формата года и 1970-2069 при использовании двухзначного формата (70-69). MySQL выводит значения YEAR в формате YYYY, но можно задавать значения в столбце YEAR, используя как строки, так и числа (тип данных YEAR недоступен в версиях, предшествующих MySQL 3.

22)

Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации

Почта

Необходима, но никому не показывается

Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки

Календарные типы данных в MySQL: особенности использования / Хабр

В MySQL 5 есть несколько типов данных для хранения даты и времени. Это TIMESTAMP, DATE, DATETIME, TIME и YEAR. Все они обладают своими особенностями, и выбор в пользу того или иного календарного типа должен производиться отдельно в каждой конкретной ситуации. Я хотел бы поделиться с вами результатом моего сегодняшнего миниисследования этих типов, в том числе в аспекте работы с временными зонами.

Итак, все календарные типы данных подробно описаны в разделе «10.3. Date and Time Types» руководства по MySQL. А важная информация, касающаяся поддержки СУБД временных зон, расписана в разделе «9.7. MySQL Server Time Zone Support». Все следующее далее базируется на изучении руководства. В то же время, в здесь указаны лишь нюансы выбора в пользу того или иного типа, поэтому этот материал никак не заменяет мануал, но дополняет его.

Вначале краткая характеристика каждого из типов:

  • TIMESTAMP — тип данных для хранения даты и времени. Данные хранятся в виде количества секунд, прошедших с начала «эпохи Юникса». Диапазон значений: 1970-01-01 00:00:00 — 2038-12-31 00:00:00.
    Занимает 4 байта.
  • YEAR — тип данных для хранения года. Диапазон значений: 1901 — 2155. Занимает 1 байт.
  • DATE — тип данных для хранения даты. Диапазон значений: 1000-01-01 — 9999-12-31. Занимает 3 байта.
  • TIME — тип данных для хранения времени. Диапазон значений: −828:59:59 — 828:59:59. Занимает 3 байта.
  • DATETIME — тип данных для хранения даты и времени. Диапазон значений: 1000-01-01 00:00:00 — 9999-12-31 00:00:00. Занимает 8 байт.

Хозяйке на заметку. Интересно то, что большинство программистов полагают, что понятие «timestamp» — это и есть Unix-время. На самом же деле, timestamp — это метка, которая представляет собой последовательность символов, обозначающих дату и / или время, когда определенное событие произошло. А «время Юникса» (Unix time) или POSIX time — это количество секунд, прошедших с полуночи 1 января 1970 года по UTC. Понятие timestamp шире, чем Unix time.

Проанализировав описание типов, представленное выше, можно сделать практически все выводы о достоинствах и недостатках тех или иных типов. Все довольно просто и очевидно.

Но прежде, чем рассказать об использовании этих типов, хочу заметить, что на практике часто используется другой тип для хранения даты и времени: целочисленное значение (для хранения даты — INT (4 байта), даты и времени — BIGINT (8 байт)). Отличие использования целочисленных типов от DATE и DATETIME лишь в том, что при выводе данные не форматируются, а в вычислениях с датами и временем целые числа требуется преобразовывать в соответствующий календарный тип. Кроме того, не производится проверка на валидность представленного значения перед сохранением. Возможности сортировки сохраняются. Поэтому INT и BIGINT имеет смысл использовать в тех же случаях, как DATE и DATETIME, с целью максимизации переносимости и независимости от СУБД. Других преимуществ я не вижу, если они есть, предлагаю указать в комментах.

Начнем с самого простого — тип YEAR. Единственное его достоинство — малый размер — всего-то 1 байт. Но из-за этого действует строгое ограничение по диапазону допустимых значений (тип может хранить только 255 разных значений). Мне сложно представить практическую ситуацию, когда может потребоваться хранить года строго в диапазоне от 1901 до 2155. Кроме того, тип SMALLINT (2 байта) дает диапазон, достаточный в большинстве ситуаций для хранения года. А экономить 1 байт на строке в таблице БД в наше время смысла нет.

Типы DATE и DATETIME можно объединить в одну группу. Они хранят дату или дату и время с довольно широким диапазоном допустимых значений, независимую от установленной на сервере временной зоны. Их использование определенно имеет практический смысл. Но если требуется хранить даты исторических событий, уходящие в прошлое за Нашу эру, придется выбрать другие типы данных. Для хранения дат неких событий, потенциально выходящих за рамки диапазона типа TIMESTAMP (дни рождений, даты выпуска продуктов, избрания президентов, запуски космических ракет и т.д.), отлично подойдут эти типы.

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

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

Остался самый интересный тип — TIMESTAMP. Рассматривать его надо в сравнении с DATE и DATETIME: TIMESTAMP тоже предназначен для хранения даты и/или времени происхождения неких событий. Важное отличие между ними в диапазонах значений: очевидно, что TIMESTAMP не годится для хранения исторических событий (даже таких, как дни рождений), но отлично подходит для хранения текущих (логирование, даты размещения статей, добавления товаров, оформления заказов) и предстоящих в обозримом будущем событий (выходы новых версий, календари и планировщики и т.д).

Основное удобство использования типа TIMESTAMP состоит в том, что для столбцов этого типа в таблицах можно задавать значение по умолчанию в виде подстановки текущего времени, а так же установки текущего времени при обновлении записи. Если вам требуется эти возможности, то с вероятностью 99% TIMESTAMP — именно то, что вам нужно. (Как этоделать, смотрите в мануале.)

Не стоит бояться того, что с приближением к 2038 году ваш софт перестанет работать. Во-первых, до этого времени вашим софтом, скорее всего, просто перестанут пользоваться (особенно версиями, которые пишутся сейчас). Во-вторых, с приближением к этой дате разработчики MySQL обязательно что-нибудь придумают для сохранения работоспособности вашего софта. Все решится так же хорошо, как проблема Y2K.

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

Диапазоны значений — это важное отличие между типами TIMESTAMP, DATETIME и DATE, но не главное. Главное то, что TIMESTAMP хранит значение в UTC. При сохранении значения оно переводится из текущего временной зоны в UTC, а при его чтении — во время текущей временной зоны из UTC. DATETIME и DATE хранят и выводят всегда одно и то же время, независимо от временных зон.

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

Функция NOW() и ее синонимы возвращают значение времени в текущей временной зоне пользователя.

Учитывая все эти обстоятельства, необходимо быть крайне внимательными при изменении временной зоны в пределах подключения к серверу и использовании типов DATE и DATETIME. Если надо хранить дату (например, дату рождения), то никаких проблем не будет. Дата рождения в любой зоне одинаковая. Т.е. если вы родились 1 января в 0:00 UTC/GMT+0, то это не значит, что в Америке будут праздновать ваш день рождения 31 декабря. Но если вы решите хранить время события в столбце DATETIME, то тут уже построить работу с пользовательскими временными зонами на уровне СУБД просто не выйдет. Поясню на примере:

Пользователь X работает в зоне UTC/GMT+2, Y — в зоне UTC/GMT+3. Для соединений пользователей с MySQL установлена соответствующая (у каждого своя) временная зона. Пользователь размещает сообщение на форуме, нас интересует дата написания сообщения.

Вариант 1: DATETIME. Пользователь X пишет сообщение в 14:00 UTC/GMT+2. Значение в поле «дата» сообщения подставляется как результат выполнения функции NOW() — 14:00. Пользователь Y считывает время написания сообщения и видит те же 14:00. Но у него в настройках стоитзона UTC/GMT+3, и он думает, что сообщение было написано не только что, а час назад.

Вариант 2: TIMESTAMP. Пользователь X пишет сообщение в 14:00 UTC/GMT+2. В поле «дата» попадает результат выполнения функции NOW() — в данном случае — 12:00 UTC/GMT+0. ПользовательY считывает время написания сообщения и получает (UTC/GMT+3)(12:00 UTC/GMT+0) = 15:00 UTC/GMT+3. Все получается ровно так, как мы хотим. И главное — пользоваться этим крайне удобно: для поддержки пользовательских временных зон не нужно писать никакой код приведения времени.

Возможности подстановки текущего времени и работы с временными зонами в типе TIMESTAMP настолько весомы, что если вам в неком логе надо хранить дату без времени, все равно стоит использовать TIMESTAMP, вместо DATE, не экономя 1 байт разницы между ними. При этом на «00:00:00» просто не обращать внимания.

Если же вы не можете использовать TIMESTAMP из-за относительно малого диапазона его значений (а обычно это 1—2 случая против 10—15 в базе сайта), придется использовать DATETIME и аккуратно его корректировать значения в нужных местах (т.е. при записи в это поле переводить дату в UTC, а при чтении — во время в зоне считывающего пользователя). Если вы храните только дату, то скорее всего не важно, какая у вас временная зона: новый год все празднуют 1 января по локальному времени, ничего переводить тут не понадобится.

MySQL | endoflife.дата

дб

MySQL — это база данных с открытым исходным кодом, разработанная Oracle. С этими проверенная производительность, надежность и простота использования, MySQL стала ведущей базой данных для веб-приложения, используемые высококлассными веб-ресурсами, включая Facebook, Twitter, YouTube, Яху! и многое другое.

Выпуск Выпущено Первоклассная поддержка Расширенная поддержка Последний
8,0 5 лет назад

(08.04.2018)

 Заканчивается через 1 год и 11 месяцев

(30.04.2025)

 Заканчивается через 2 года и 11 месяцев

(30. 04.2026)

 8.0.33

(16 марта 2023 г.)

5.7 7 лет назад

(09 окт. 2015 г.)

 Завершено 2 года и 6 месяцев назад

(31 октября 2020 г.)

 Заканчивается через 5 месяцев и 2 недели

(31 октября 2023 г.)

 5.7.42

(16 марта 2023 г.)

5.6 10 лет назад

(01 февраля 2013 г.)

 Завершено 5 лет назад

(28 февраля 2018 г.)

 Завершено 2 года назад

(28 февраля 2021 г.)

 5. 6.51
5,5 12 лет назад

(03 декабря 2010 г.)

 Завершено 7 лет назад

(31 декабря 2015 г.)

 Завершено 4 года назад

(31 декабря 2018 г.)

 5.5.63

Политика Oracle Lifetime Support Policy включает три варианта.

Первоклассная поддержка

5 лет, начиная с GA , первоклассной поддержки, которая включает выпуски обслуживания MySQL, обновления и исправления. (исправление ошибок) и предупреждения системы безопасности.

Расширенная поддержка

3 года дополнительной поддержки, включая исправление ошибок, после периода поддержки уровня Premier для определенных Релизы MySQL. Включает выпуски обслуживания MySQL, обновления, исправления ошибок (исправление ошибок) и оповещения безопасности.

Постоянная поддержка

В любое время, кроме периода поддержки Premier. Только поддержка. Нет новых выпусков, нет новых исправлений (нет ошибок исправление новых проблем), новых обновлений нет. Только ранее существовавшие обновления, исправления и предупреждения доступный.

MySQL Community Edition находится в свободном доступе под лицензией GPLv2 лицензирование. Исторически патчи выпускались одновременно с коммерческими. предложения, но никаких официальных обязательств о том, что такая политика сохранится, не делается.

Дополнительная информация доступна на веб-сайте MySQL.

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

Вы можете проверить используемую версию, запустив:

 mysqld --version

Вы можете отправить улучшение на эту страницу на GitHub . На этой странице есть соответствующий Страница обсуждения.

Версия этой страницы в формате JSON доступна по адресу /api/mysql.json. Дополнительную информацию см. в документации по API. Вы можете подписаться на канал iCalendar по адресу /calendar/mysql.ics.

mysql — Как выбрать дату из столбца datetime?

спросил

Изменено 8 месяцев назад

Просмотрено 682k раз

У меня есть столбец типа «datetime» со значениями типа 2009-10-20 10:00:00

Я хотел бы извлечь дату из datetime и написать запрос типа:

 ВЫБЕРИТЕ * ИЗ
данные
ГДЕ дата и время = '2009-10-20'
ЗАКАЗАТЬ ПО дате и времени DESC

Является ли следующий лучший способ сделать это?

 ВЫБЕРИТЕ * ИЗ
данные
ГДЕ дата и время МЕЖДУ('2009-10-20 00:00:00' И '2009-10-20 23:59:59')
ЗАКАЗАТЬ ПО дате и времени DESC

Однако это возвращает пустой набор результатов. Какие-либо предложения?

  • mysql
  • дата
  • дата-время

0

Вы можете использовать MySQL ДАТА() функция:

 ГДЕ ДАТА(дата/время) = '2009-10-20'

Вы также можете попробовать это:

WHERE datetime LIKE '2009-10-20%'

См. этот ответ для получения информации о влиянии на производительность использования LIKE .

7

Использование WHERE DATE(datetime) = '2009-10-20' имеет проблемы с производительностью . Как указано здесь:

  • он рассчитает DATE() для всех строк, включая несовпадающие.
  • сделает невозможным использование индекса для запроса.

Используйте операторы BETWEEN или > , < , = , которые позволяют использовать индекс:

 SELECT * FROM data
ГДЕ datetime МЕЖДУ "2009-10-20 00:00:00" И "2009-10-20 23:59:59"

Обновление: влияние на использование LIKE вместо операторов в индексированном столбце высокий . Вот некоторые результаты тестирования таблицы с 1 176 000 строк:

  • с использованием datetime LIKE '2009-10-20%' => 2931 мс
  • с использованием datetime >= '2009-10-20 00:00:00' AND datetime <= '2009-10-20 23:59:59' => 168 мс

При повторном вызове того же запроса разница еще выше: 2984 мс против 7 мс (да, всего 7 мс!). Я обнаружил это, переписывая старый код в проекте с использованием Hibernate.

2

Вы можете отформатировать дату и время в части Y-M-D:

 DATE_FORMAT(datetime, '%Y-%m-%d')

2

Хотя все ответы на странице вернут желаемый результат, все они имеют проблемы с производительностью. Никогда не выполняйте преобразования полей в предложении WHERE (включая вычисление DATE() ), поскольку это преобразование должно выполняться для всех строк в таблице.

Конструкция BETWEEN ... AND включает оба граничных условия, требуя указать синтаксис 23:59:59 в конечной дате, которая сама по себе имеет другие проблемы (микросекундные транзакции, которые, как я полагаю, MySQL не поддерживает в 2009 году, когда был задан вопрос).

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

 ГДЕ datetime>='2009-10-20' AND datetime<'2009-10-21'

Это самый быстрый метод с наименьшим объемом памяти, наименее ресурсоемким и дополнительно поддерживает все функции и крайние случаи MySQL, такие как точность временной метки менее секунды. Кроме того, это гарантия будущего.

3

Скажем, это столбец, содержащий значение datetime , таблица data .

 +--------------------+
| дата_создания |
+--------------------+
| 2018-06-02 15:50:30|
+--------------------+
 
 mysql> выберите ДАТУ (дата_создания) из данных;
+--------------------+
| ДАТА(дата_создания) |
+--------------------+
| 2018-06-02 |
+--------------------+
 
 mysql> выберите ГОД (дата_создания) из данных;
+--------------------+
| ГОД(дата_создания) |
+--------------------+
| 2018 |
+--------------------+
 
 mysql> выберите МЕСЯЦ (дата_создания) из данных;
+---------------------+
| МЕСЯЦ (дата_создания) |
+---------------------+
| 6 |
+---------------------+
 
 mysql> выберите ДЕНЬ (дата_создания) из данных;
+-------------------+
| ДЕНЬ(дата_создания) |
+-------------------+
| 2 |
+-------------------+
 
 mysql> выберите ЧАС (дата_создания) из данных;
+--------------------+
| ЧАС(дата_создания) |
+--------------------+
| 15 |
+--------------------+
 
 mysql> выберите МИНУТА (дата_создания) из данных;
+----------------------+
| МИНУТА(дата_создания) |
+----------------------+
| 50 |
+----------------------+
 
 mysql> выберите SECOND (date_created) из данных;
+----------------------+
| ВТОРОЙ (дата_создания) |
+----------------------+
| 31 |
+----------------------+

Вы можете использовать:

 DATEDIFF (день, дата начала, дата окончания) = 0

Или:

 ЧАСТЬ ДАТЫ (день, дата начала) = ЧАСТЬ ДАТЫ (день, дата окончания)
И
ЧАСТЬ ДАТЫ( месяц, дата начала) = ЧАСТЬ ДАТЫ(месяц, дата окончания)
И
ЧАСТЬ ДАТЫ(год,дата начала) = ЧАСТЬ ДАТЫ(год,дата окончания)

Или:

 CONVERT(DATETIME,CONVERT(VARCHAR(12), startdate, 105)) = CONVERT(DATETIME,CONVERT(VARCHAR(12), enddate, 105))

простой и лучший способ использования функции даты

пример

 SELECT * FROM
данные
ГДЕ дата (дата и время) = '2009-10-20'

ИЛИ

 ВЫБЕРИТЕ * ИЗ
данные
ГДЕ дата (дата-время) >= '2009-10-20' && дата (дата-время) <= '2009-10-20'

Я попробовал date(tscreated) = '2022-06-04' на большом наборе записей.

No related posts.