Поиск

Типы DATETIME, DATE и TIMESTAMP

Типы DATETIME, DATE и TIMESTAMP связаны друг с другом. Этот раздел описывает их характеристики, в чем они сходны и в чем отличаются.
Тип DATETIME применяется, когда необходимо иметь значения, включающие и дату и время. MySQL извлекает и отображает значения типа DATETIME в формате ТГГТ-ММ-ДД ЧЧ: ММ :СС'. Поддерживаемый диапазон значений для них - от '1000-01-01 00:00:00' до ' 9999-12-31 23:59:59' (Поддерживаемый означает, что более ранние значения могут работать, но это не гарантируется.)
Тип DATE применяется, когда необходимо иметь значения, включающие только дату, без времени. MySQL извлекает и отображает значения типа DATETIME в формате 'ГГГГ-ММ-ДД'. Поддерживаемый диапазон-от Ч000-01-0Г до '9999-12-31'.
Тип столбца TIMESTAMP имеет ряд свойств, зависящих от версии MySQL и SQL-режима, в котором работает сервер. Эти свойства описаны далее в настоящем разделе.
Вы можете специфицировать значения типов DATETIME, DATE и TIMESTAMP, используя любой из общепринятых наборов форматов:

  1. Как строку в формате 'ГГГГ-ММ-ДД ЧЧ:ММ:СС или 'ГГ-ММ-ДД ЧЧ:ММ:СС. Допускается ослабленный синтаксис: любой символ пунктуации может быть использован в качестве разделителя между датой и временем. Например, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45' и '98012031 11Л30Л45' - эквивалентны.
  2. Как строку в формате 'YYYY-MM-DD' или 'YY-MM-DD'. Ослабленный синтаксис также допускается. Например, эквивалентны следующие значения: '98-12-31','98.12.31','98/12/31' и '98012031'.
  3. Как строку без разделителей в формате 'ГГГГММДДЧЧММСС' или ' ГГММДДЧЧММСС', предполагая, что строка имеет смысл в качестве даты. Например, ' 19970523091528' и '970523091528' интерпретируются как '1997-0 5-23 09:15:28', но '971122129015' неверно (потому что имеет бессмысленное значение минут) и становится '0000-00-00 00:00:00'.
  4. Как строку без разделителей в формате 'ГГГГММДД' или ТГММДД', предполагая, что строка имеет смысл в качестве даты. Например, '19970523' и '980523' интерпретируются как '1997-05-23', но '971332' неверно (неправильное значение месяца и дня) и превращается в ' 0000—00—00'.
  5. Как число в формате ГГГГММДДЧЧММСС или ГГММДДЧЧММСС, предполагая, что число имеет смысл в качестве даты. Например, 19830905132800 и 830905132800 интерпретируются как '1983-09-05 13:28:00'.
  6. Как число в формате ГГГГММДД или ГГММДД, предполагая, что число имеет смысл в качестве даты. Например, 19830905 и 830905 интерпретируются как '1983-09-05'.
  7. Как результат функции, которая возвращает приемлемое в контексте DATETIME, DATE или TIMESTAMP значение, такое как NOW() или CURRENT_DATE.

Неверные величины DATETIME, DATE или TIMESTAMP преобразуются в нулевые значе-ниясоответствующеготипа('0000-00-00 00:00:00', '0000-00-00' или 00000000000000).
Для значений, указанных в виде строки, включающей разделитель даты, нет необходи­мости задавать два разряда для месяца или дня, которые меньше 10. '1976-6-9' - это тоже самое, что и '1976-06-09'. Аналогично, для значений, заданных в виде строки, вклю­чающей разделитель времени, не нужно указывать два разряда для часов, минут и секунд, которые меньше 10. '1979-10-30 1:2:3' - это то же самое, что '1979-10-30 01:02:03'.


Значения, заданные в виде числа, должны иметь длину 6, 8, 12 или 14 разрядов. Если число имеет длину 8 или 14 разрядов, предполагается, что оно задает значение в форма­те ГГГГММДД или ГГГГММДДЧЧММСС и что год задан четырьмя разрядами. Если число имеет длину 6 или 12, то предполагается, что оно задает значение в формате ГГММДД или ГГММДДЧЧММСС и год задан двумя разрядами. Числа, длина которых отличается от 6, 8, 12 и 14, дополняются ведущими нулями до ближайшего количества разрядов из указанного ряда.

Значения, заданные в виде строки без разделителей, интерпретируются с использова­нием их длины, как описано выше. Если длина строки 8 или 14 символов, предполагает­ся, что год задан в 4-значном формате. В противном случае предполагается, что год за­дан первыми двумя знаками. Строка интерпретируется слева направо, чтобы извлечь значения года, месяца, дня, часов, минут и секунд. Это означает, что вы не должны ис­пользовать строки длиной менее 6 символов. Например, если вы укажете '9903', имея в виду март 1999 года, то обнаружите, что MySQL вставит нулевую дату в таблицу. Так получается из-за того, что значения года и месяца равны 99 и 03, но часть, указывающая день, полностью отсутствует, то есть это значение не задает корректную дату. Однако, начиная с MySQL 3.23, вы можете явно указать нулевое значение месяца или дня. На­пример, можно указать '990300', чтобы вставить в таблицу значение '1999-03-00'.
В определенных пределах вы можете присваивать значения одного типа объектам другого типа. Однако, при этом возможно некоторое искажение с потерей информации:

  1. Если вы присваиваете значение типа DATE объекту типа DATETIME или TIMESTAMP, временная часть значения принимается равной '00:00:00', поскольку значения типа DATE не содержат информации о времени.
  2. Если вы присваиваете значение типа DATETIME или tiMesTAMP объекту типа DATE,временная часть значения теряется, поскольку DATE не может ее включить в себя.
  3. Помните, несмотря на то, что значения DATETIME, DATE и TIMESTAMP могут быть указаны с использованием одного и того же набора форматов, диапазоны их допустимых значений отличаются. Например, значения TIMESTAMP не могут быть ранее 1970 или позднее 2037 года. Это означает, что дата вроде '1968-01-10', которая вполне корректна в качестве значения типа DATETIME или DATE, неверна для типа TIMESTAMP и будет преобразована в 0 при присвоении такому объекту.

Не следует также забывать о некоторых ловушках при указании значений дат:

  1. Ослабленный формат значений, заданных в виде строк, может вводит в заблуждение. Например, значение вроде '10:11:12' может выглядеть как время, потомучто используется разделитель ':', но если оно применяется в контексте даты, то будет интерпретировано как '2010-11-12'. В то же время значение '10:45:15'
    будет преобразовано в '0000-00-00', поскольку '45' не является допустимым месяцем.
  2. Сервер MySQL выполняет только базовую проверку правильности дат: диапазоны значений года, месяца и дня составляют соответственно от 1000 до 9999, от 00 до 12 и от 00 до 31. Любые даты, содержащие части, выходящие за пределы этих диапазонов, становятся субъектами преобразования в '0000-00-00'. Помните, что это позволяет вам сохранять неверные даты, вроде '2002-04-31'. Чтобы гаранти­ровать правильность даты, выполняйте проверку внутри приложения.

  • Даты, содержащие двузначный год, неоднозначны, потому что неизвестен век. MySQL интерпретирует двузначные годы следующим образом: * Год в диапазоне 00-69 преобразуется в 2000—2069.
  • Год в диапазоне 70-99 преобразуется в 1970-1999.
Свойства TIMESTAMP в версиях MySQL, предшествующих 4.1
TIMESTAMP представляет собой тип столбца, который можно использовать для автома­тической отметки текущей даты и времени при выполнении операций UPDATE или INSERT. Если в таблице несколько столбцов типа TIMESTAMP, только первый из них об­новляется автоматически.
Автоматическое обновление первого столбца TIMESTAMP в таблице выполняется при наступлении одного из следующих условий:
  1. При явном присвоении ему значения NULL.
  2. Столбец не указан явно в операторе INSERT или LOAD DATA INFILE.
  3. Столбец не указан явно в операторе UPDATE, а значение какого-то другого столбца при этом изменяется. Оператор UPDATE, устанавливающий столбцу такое же значение, как он имел ранее, не приводит к обновлению столбца TIMESTAMP. Если вы присваиваете старое значение, MySQL игнорирует это в целях эффективности.

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

  1. Позвольте MySQL установить значение столбца при создании строки. Это инициализирует ее текущим значением даты и времени.
  2. При выполнении последующих обновлений других столбцов строки устанавливайте значение столбца TIMESTAMP равным его текущему значению:

UPDATE имя__таблицы
SET столбец_ imes tamp- столбец_ Ытеstamp, другой_столбец1 = новое_зачение1, другой_столбец2 = новое_зачение2, ...
Другой способ поддерживать столбец, который записывает время создания строки, предполагает использование столбца DATETIME, инициализируемого значением NOW () при создании строки и не изменяемого в дальнейшем.
Значения TIMESTAMP могут изменяться от начала 1970 года до части 2037 года с раз­решением в одну секунду. Значения отображаются в виде чисел.
Формат, в котором MySQL извлекает и отображает значения TIMESTAMP, зависит от ширины отображения, как иллюстрирует табл. 4.3. Полный формат TIMESTAMP состоит из 14 разрядов, однако столбцы TIMESTAMP можно определить и в более коротком формате отображения.

Таблица Зависимость формата отображения от ширины



Все столбцы TIMESTAMP имеют один и тот же размер хранения, независимо от форма­та отображения. Наиболее часто используемые форматы - в 6, 8, 12 и 14 символов. Вы можете задать произвольный размер отображения при создании таблицы, но значения 0 и больше 14 приводятся к 14. Нечетные значения от 1 до 13 приводятся к ближайшему большему четному.
Столбца TIMESTAMP хранят корректные значения, используя полную точность, с кото­рой они были указаны, независимо от ширины отображения. Однако с этим связаны и некоторые ограничения:

  1. Следует всегда указывать год, месяц и день, даже если столбец объявлен как TIMESTAMP(4) или TIMESTAMP(2). Иначе значение считается некорректным и со храняется 0.
  2. Если вы используете ALTER table, чтобы расширить столбец TIMESTAMP, то будет высвечиваться информация, которая ранее была скрытой.
  3. Аналогично, при сужении столбца TIMESTAMP информация не теряется, кроме как в том смысле, что выводиться будет меньше информации, чем ранее.
  4. Несмотря на то что столбцы TIMESTAMP хранятся с полной точностью, единственной функцией, которая работает с полным объемом хранимой в них информации, является UNIX_TIMESTAMP(). Все остальные функции работают с форматированным извлеченным значением. Это значит, что вы не можете использовать функцию типа HOUR () или SECOND (), если только соответствующая часть не включена в форматированное значение столбца. Например, часть ЧЧ столбца TIMESTAMP не будет отображаться, если только ее отображаемая ширина не равна, по меньшей мере, 10, поэтому применение HOURO для более коротких значений TIMESTAMP приведет к бессмысленному результату.
Свойства TIMESTAMP в MySQL версии 4.1 и выше
Начиная с MySQL 4.1, свойства TIMESTAMP отличаются от тех, что были в предшест­вующих выпусках: а Столбцы TIMESTAMP отображаются в том же формате, что и столбцы DATETIME.

  • Ширина отображения больше не поддерживается, как описано ранее. Другими словами, теперь нельзя использовать TIMESTAMP (4) или TIMESTAMP (2). В дополнение, если сервер MySQL запущен в режиме MAXDB, тип TIMESTAMP иденти­чен datetime. To есть, если сервер запущен в режиме MAXDB в момент создания таблицы, любые столбцы TIMESTAMP создаются как DATETIME. В результате эти столбцы использу­ют формат отображения DATETIME, имеют тот же диапазон допустимых значений и ника­кого автоматического обновления не происходит.

В режиме MAXDB сервер MySQL можно запускать, начиная с версии 4.1.1. Чтобы включить этот режим, укажите при запуске сервера опцию —sql-mode=MAXDB, либо во время выполнения установите значение глобальной переменной sqljnode:
mysql SET GLOBAL sql_mode=MAXDB;
Клиент может принудить сервер работать в режиме MAXDB для его собственного сеан­са с помощью команды:
mysql SET SESSION sql_mode=MAXDB;