типы данных даты и времени : DATETIME , DATE , TIMESTAMP , TIME и YEAR . Каждый из них имеет интервал допустимых значений, а также значение "ноль", которое используется, когда пользователь вводит действительно недопустимое значение. Отметим, что MySQL позволяет хранить некоторые не вполне достоверные значения даты, например 1999-11-31. Причина в том, что управление проверкой даты входит в обязанности конкретного приложения, а не SQL-серверов. Для ускорения проверки правильности даты MySQL только проверяет, находится ли месяц в интервале 0-12 и день в интервале 0-31. Данные интервалы начинаются с 0, это сделано для того, чтобы обеспечить для MySQL возможность хранить в столбцах DATE или DATETIME даты, в которых день или месяц равен нулю. Эта возможность особенно полезна для приложений, которые предполагают хранение даты рождения - здесь не всегда известен день или месяц рождения. В таких случаях дата хранится просто в виде 1999-00-00 или 1999-01-00 (при этом не следует рассчитывать на то, что для подобных дат функции DATE_SUB() или DATE_ADD дадут правильные значения).

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

• Хотя MySQL пытается интерпретировать значения в нескольких форматах, во всех случаях ожидается, что крайним слева будет раздел значения даты, содержащий год. Даты должны задаваться в порядке год-месяц-день (например, "98-09-04"), а не в порядке месяц-день-год или день-месяц-год, т.е. не так, как мы их обычно записываем (например "09-04-98", "04-09-98").
• MySQL автоматически преобразует значение, имеющее тип даты или времени, в число, если данная величина используется в числовом контексте, и наоборот.
• Значение, имеющее тип даты или времени, которое выходит за границы установленного интервала или является недопустимым для этого типа данных (см. начало раздела), преобразуется в значение "ноль" для данного типа. (Исключение составляют выходящие за границы установленного интервала величины типа TIME , которые усекаются до соответствующей граничной точки заданного интервала TIME ). В таблице 4.3 . представлены форматы значения "ноль" для каждого из типов столбцов:

• Значения "ноль" - особые. Для их хранения или ссылок на них можно явно применять представленные в таблице значения, а можно использовать "0", что легче в написании.

Типы данных DATETIME, DATE и TIMESTAMP

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

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

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

Для остальных (кроме первого) столбцов типа TIMESTAMP также можно задать установку в значение текущих даты и времени. Для этого необходимо просто установить столбец в NULL или в NOW() .

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

Величины типа TIMESTAMP могут принимать значения от начала 1970 года до некоторого значения в 2037 году с разрешением в одну секунду. Эти величины выводятся в виде числовых значений.

Формат данных, в котором MySQL извлекает и показывает величины TIMESTAMP , зависит от количества показываемых символов. Это проиллюстрировано в таблице 4.4 . Полный формат TIMESTAMP составляет 14 десятичных разрядов, но можно создавать столбцы типа TIMESTAMP и с более короткой строкой вывода:

Таблица 4.4. Формат данных TIMESTAMP в зависимости от количества извлекаемых разрядов

Тип столбца	Формат вывода
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

Величины DATETIME , DATE и TIMESTAMP могут быть заданы любым стандартным набором форматов:

• Как строка в формате "YYYY-MM-DD HH:MM:SS" или в формате "YY-MM-DD HH:MM:SS". Допускается "облегченный" синтаксис - можно использовать любой знак пунктуации в качестве разделительного между частями разделов даты или времени. Например, величины "98-12-31 11:30:45", "98.12.31 11+30+45", "98/12/31 11*30*45" и "98@12@31 11^30^45" являются эквивалентными.
• Как строка в формате "YYYY-MM-DD" или в формате "YY-MM-DD". Здесь также допустим "облегченный" синтаксис. Например, величины "98-12-31", "98.12.31", "98/12/31" и "98@12@31" являются эквивалентными.
• Как строка без разделительных знаков в формате "YYYYMMDDHHMMSS" или в формате "YYMMDDHHMMSS", при условии, что строка понимается как дата. Например, величины "19970523091528" и "970523091528" можно интерпретировать как "1997-05-23 09:15:28", но величина "971122129015" является недопустимой (значение раздела минут является абсурдным) и преобразуется в "0000-00-00 00:00:00".
• Как строка без разделительных знаков в формате "YYYYMMDD" или в формате "YYMMDD", при условии, что строка интерпретируется как дата. Например, величины "19970523" и "970523" можно интерпретировать как "1997-05-23", но величина "971332" является недопустимой (значения разделов месяца и дня не имеют смысла) и преобразуется в "0000-00-00".
• Как число в формате YYYYMMDDHHMMSS или в формате YYMMDDHHMMSS, при условии, что число интерпретируется как дата. Например, величины 19830905132800 и 830905132800 интерпретируются как "1983-09-05 13:28:00".
• Как число в формате YYYYMMDD или в формате YYMMDD, при условии, что число интерпретируется как дата. Например, величины 19830905 и 830905 интерпретируются как "1983-09-05".
• Как результат выполнения функции, возвращающей величину, приемлемую в контекстах типов данных DATETIME , DATE или TIMESTAMP (например, функции NOW() или CURRENT_DATE() ).

Тип данных TIME

MySQL извлекает и выводит величины типа TIME в формате "HH:MM:SS" (или в формате "HHH:MM:SS" для больших значений часов). Величины TIME могут изменяться в пределах от "-838:59:59" до "838:59:59". Причина того, что "часовая" часть величины может быть настолько большой, заключается в том, что тип TIME может использоваться не только для представления времени дня (которое должно быть меньше 24 часов), но также для представления общего истекшего времени или временного интервала между двумя событиями (который может быть значительно больше 24 часов или даже отрицательным).

Величины TIME могут быть заданы в различных форматах:

Как строка в формате "D HH:MM:SS.дробная часть" (следует учитывать, что MySQL пока не обеспечивает хранения дробной части величины в столбце рассматриваемого типа). Можно также использовать одно из следующих "облегченных" представлений: HH:MM:SS.дробная часть , HH:MM:SS , HH:MM , D HH:MM:SS , D HH:MM , D HH или SS . Здесь D - это дни из интервала значений 0-33.

• Как строка без разделителей в формате "HHMMSS", при условии, что строка интерпретируется как дата. Например, величина "101112" понимается как "10:11:12", но величина "109712" будет недопустимой (значение раздела минут является абсурдным) и преобразуется в "00:00:00".
• Как число в формате HHMMSS , при условии, что строка интерпретируется как дата. Например, величина 101112 понимается как "10:11:12". MySQL понимает и следующие альтернативные форматы: SS
• Как четырехзначное число в интервале значений от 1901 до 2155.
• Как двухзначная строка в интервале значений от "00" до "99". Величины в интервалах от "00" до "69" и от "70" до "99" при этом преобразуются в величины YEAR в интервалах от 2000 до 2069 и от 1970 до 1999 соответственно.
• Как двухзначное число в интервале значений от 1 до 99. Величины в интервалах от 1 до 69 и от 70 до 99 при этом преобразуются в величины YEAR в интервалах от 2001 до 2069 и от 1970 до 1999 соответственно. Необходимо принять во внимание, что интервалы для двухзначных чисел и двухзначных строк несколько различаются, так как нельзя указать "ноль" непосредственно как число и интерпретировать его как 2000. Необходимо задать его как строку "0" или "00", или же оно будет интерпретировано как 0000.
• Как результат выполнения функции, возвращающей величину, приемлемую в контексте типа данных YEAR (такой как NOW() ).

Недопустимые величины YEAR преобразуются в 0000.

Типы 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;

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

• 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, с целью максимизации переносимости и независимости от СУБД. Других преимуществ я не вижу, если они есть, предлагаю указать в комментах.

Использование календарных типов данный в MySQL

Начнем с самого простого - тип 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 января по локальному времени, ничего переводить тут не понадобится.

Цель этой статьи заключается в разъяснении особенностей работы с типами DATETIME в SQL Server, в том числе часто встречающихся заблуждений и общих рекомендаций по их преодолению. Благодаря Frank Kalis эта статья переведена на немецкий язык.

Благодарности:

Я хочу поблагодарить следующих людей за их ценные предложения и материалы для этой статьи: Steve Kass, Aaron Bertrand, Jacco Schalkwijk, Klaus Oberdalhoff, Hugo Kornelis, Dan Guzman и Erland Sommarskog.

Версии SQL Server

Данная статья применима к SQL Server 7.0, 2000, 2005 и 2008, если не указано иначе.

Типы даты и времени в SQL Server

До SQL Server 2008 в SQL Server имелось два типа данных для обработки даты и времени. Поскольку в этой статье мы очень часто будем ссылаться на эти типы, введем сокращенную аббревиатуру для каждого типа в двух представленных ниже таблицах (колонка СК):

Название		Мин. значение	Макс. значение	Точность	Используемая память
smalldatetime	sdt	1900-01-01 00:00:00	2079-06-06 23:59:00	минута	4 байта
datetime	dt	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997	3.33 мс	8 байт

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

Если задавать только ту часть что касается даты, то SQL Server сохранит время в виде 00:00:00.000.
А если устанавливать значение только времени, SQL Server будет хранить дату как 01.01.1900.
Это очень важно. Прочтите снова.

SELECT CAST(‘20041223’ AS datetime)

———————–
2004-12-23 00:00:00.000

SELECT CAST(’14:23:58′ AS datetime)

———————–
1900-01-01 14:23:58.000

С появлением SQL Server 2008 было получено несколько новых типов данных связанных со значениями даты и времени:

Название		Мин. значение	Макс. значение	Точность	Используемая память
datetime2	dt2	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	100 нс	6-8 байт
date	d	0001-01-01	9999-12-31	день	3 байта
time	t	00:00:00.0000000	23:59:59.9999999	100 нс	3-5 байта
datetimeoffset	dto	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	100 нс	8-10 байт

• Как вы можете видеть, мы наконец-то получили типы данных только для даты (date ) и только для времени (time ).
• Datetime2 это “лучшее DATETIME” по ряду причин, и занимает не многим больше памяти, чем datetime и потенциально даже меньше!
• Для нового типа включающего величину времени, вы можете задать “точность до дробных секунд” определяя используемый порядок цифр в секундах после запятой. Так, time(3) может хранить значения наподобие 14:23:12.567, которые при вводе значения 14:23:12:5677 округляются до 14:23:12:568.
• Новый тип datetimeoffset содержит в себе часть смещения местного часового пояса.

Форматы даты и времени

Распространенное заблуждение – то, что SQL Server хранит эти типы данных в некотором специфическом удобном для чтения формате. Это не так. SQL Server хранит такие значения в своем внутреннем формате (например, два целых числа для datetime и smalldatetime ). При этом, когда используется T-SQL для установки значения (например, в выражении INSERT) вы выражаете его как текстовую строку. Существуют также правила для интерпретации SQL Server-ом различных форматов даты-строки. Но заметим, что в любом случае SQL Server не запомнит этот формат.

Входные форматы для даты и времени

Есть много доступных форматов для приведения значений к виду date/time/datetime. Некоторые из них “лучше”, чем другие, и далее вы поймете почему “лучше”. Примечательно, что все эти форматы применимы для всех типов. Так даже формат “только время” применимо для типа “только дата” и т.д. (В статье игнорируется часть смещения местного часового пояса, которая используется только в типе данных datetimeoffset – более подробно о котором в Books Online.)

Название		Формат	SET DATEFORMAT зависимость	SET LANGUAGE зависимость	Нейтральность к языку
Unseparated	u	‘19980223 14:23:05’	нет	нет	для всех
Separated	s	’02/23/1998 14:23:05′	для всех	для всех	нет
ANSI SQL	ansisql	‘1998-12-23 14:23:05’	sdt , dt	sdt , dt	не для sdt и dt
Alphabetic	a	’23 February 1998 14:23:05′	нет	для всех (название месяца)	нет
ODBC datetime	odt	{ts ‘1998-02-23 14:23:05’}	нет	нет	для всех
ODBC date	od	{d ‘1998-02-23’}	нет	нет	для всех
ODBC time	ot	{t ’14:23:05′}	нет	нет	для всех
ISO 8601	iso	‘1998-02-23T14:23:05’	нет	нет	для всех
Time	t	’14:23:05′ ‘2:23:05 PM’	нет	нет	для всех

• Отметим, ANSI SQL действительно только частный случай формата с разделительными символами Separated (так называемый “цифровой”), использующий в качестве разделителей тире (-), косую черту (/) и точку (.). Но, поскольку это единственный формат, определенный в стандарте ANSI SQL, по мнению автора это стоит упомянуть как частный случай.
• Большинство форматов позволяют удалять часть содержащую дату и/или время, и в некоторых случаях это может выглядеть немного … странно. Казалось бы, неразумно указывать, например ‘2008-08-25’ как тип время (time ), но в конечном результате это аналогично не установке значений в строке datetime . Рассмотрим ниже:
  SELECT CAST(” AS time)
  SELECT CAST(‘2008-08-25’ AS time)
  
  Оба запроса выдают один и тот же результат (time 00:00:00).
• ODBC форматы (ODBC datetime, ODBC date, ODBC time ) отличаются в том смысле, что у них есть маркер (literal_type, t, d или ts), который необходимо правильно задать в зависимости от того, получать ли дату и время, только дату или только время.
• Для применения формата ISO 8601 необходим сегмент даты и времени.
• SET DATEFORMAT наследует свои настройки от SET LANGUAGE (но явно заданный SET DATEFORMAT аннулирует более поздний SET LANGUAGE). Языковые настройки по умолчанию задаются для каждого языка используемого при вводе логина. Язык по умолчанию для логина задается при помощи sp_configure.
• Правила, относительно форматирования части даты и новых типов могут привести к путанице. Microsoft стремится, чтобы дата новых соответствующих типов данных (date, datetime2 и datetimeoffset ) была менее зависима от настроек и еще более соответствовала требованиям ANSI SQL. И в результате – новые типы нейтрально-языковые для выделения составляющих даты-времени до тех пор пока год на первом месте. SQL Server-у необходимо определить, что эта часть является годом, и поэтому требуется 4 позиции составляющих год (yyyy, а не yy). Если это так, то строка будет интерпретироваться как сначала год, затем месяц и, наконец, день – независимо от DATEFORMAT или языковых установок. Если же вначале указывается месяц, тогда DATEFORMAT и языковые настройки будут “соблюдаться”:
  SET LANGUAGE British –uses dmy
  GO
  SELECT CAST(’02-23-1998 14:23:05′ AS date) –Error
  GO
  SELECT CAST(‘2/23/1998 14:23:05’ AS date) –Error
  GO
  SELECT CAST(‘1998-02-23 14:23:05’ AS date) –Ok
  GO
  SELECT CAST(‘1998.02.23 14:23:05’ AS date) –Ok
  GO
  SELECT CAST(‘1998/02/23 14:23:05’ AS date) –Ok
  GO
  Первые два запроса ошибочны поскольку год не на первой позиции (и нет 23 месяца в 1998 году). В последующих трех запросах ошибок нет, поскольку требования учтены, и год указан первым (и мы используем один из новых стилей типов связанных с датой). Предельно прозрачно, правда? 🙂

Описание доступных форматов имеется в Books Online, поэтому вдаваться в подробности для каждого формата нет смысла.

Книга Richard T. Snodgrass “Разработка временно-ориентированных приложений баз данных в SQL”: содержит много сведений о представлении время-ориентированной информации в модели данных. И, конечно же, возможно использование этой дополнительной (исторической) информации в своих запросах SQL. Эта книга не издается, а на официальном сайте Ричарда (www.cs.arizona.edu/people/rts), вы бесплатно можете скачать ее в PDF формате.

Перевод: Винчик Евгений

Я не рекомендую использовать ни поле DATETIME, ни TIMESTAMP. Если вы хотите представить определенный день в целом (например, день рождения), используйте тип DATE, но если вы более конкретны, вы, вероятно, заинтересованы в записи фактического момента, а не единицы время (день, неделя, месяц, год). Вместо использования DATETIME или TIMESTAMP используйте BIGINT и просто сохраните количество миллисекунд с эпохи (System.currentTimeMillis (), если вы используете Java). Это имеет ряд преимуществ:

1. Вы избегаете блокировки поставщика. Практически каждая база данных поддерживает целые числа относительно похожим образом. Предположим, вы хотите перейти в другую базу данных. Хотите ли вы беспокоиться о различиях между значениями DATETIME MySQL и том, как Oracle их определяет? Даже среди разных версий MySQL, TIMESTAMPS имеют разный уровень точности. Совсем недавно MySQL поддерживал миллисекунды в метках времени.
2. Нет проблем с часовым поясом. Здесь были некоторые проницательные комментарии о том, что происходит с часовыми поясами с разными типами данных. Но разве это общее знание, и ваши коллеги все время будут учиться этому? С другой стороны, довольно сложно перепутать BigINT в java.util.Date. Использование BIGINT приводит к множеству проблем с часовыми поясами, которые падают на обочину.
3. Не беспокойтесь о диапазонах или точности. Вам не нужно беспокоиться о том, что будет сокращено будущими диапазонами дат (TIMESTAMP - только 2038).
4. Интеграция сторонних инструментов. Используя целое число, тривиально для сторонних инструментов (например, EclipseLink) для взаимодействия с базой данных. Не каждый сторонний инструмент будет иметь такое же представление о «datetime», как это делает MySQL. Хотите попытаться выяснить в Hibernate, следует ли использовать объект java.sql.TimeStamp или java.util.Date, если вы используете эти настраиваемые типы данных? Использование базовых типов данных делает использование с сторонними инструментами тривиальным.

Эта проблема тесно связана с тем, как вы должны хранить денежную стоимость (т.е. 1,99 доллара США) в базе данных. Должны ли вы использовать Decimal или тип денег в базе данных, или, что хуже всего, Double? Все 3 из этих вариантов ужасны, по многим причинам, перечисленным выше. Решение заключается в том, чтобы хранить стоимость денег в центах с помощью BIGINT, а затем конвертировать центы в доллары, когда вы показываете значение для пользователя. Задача базы данных состоит в том, чтобы хранить данные и НЕ собирать данные. Все эти причудливые типы данных, которые вы видите в базах данных (особенно Oracle), мало добавляют и запускают вас по пути к блокировке поставщика.

2018-12-04T00:00Z

TIMESTAMP - 4 байта с 8 байтами для DATETIME.

Но, подобно тому, как скронид сказал, что он имеет нижний предел 1970 года. Это здорово для всего, что может произойти в будущем, хотя;)

2018-12-11T00:00Z

Основное различие заключается в том, что DATETIME является постоянным, а TIMESTAMP зависит от установки time_zone .

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

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

Отличным примером DATETIME, используемым там, где TIMESTAMP должен был использоваться, является Facebook, где их серверы никогда не могут быть уверены в том, что произошло во время часовых поясов. Однажды у меня был разговор, в котором время говорило, что я отвечаю на сообщения, прежде чем сообщение действительно отправлено. (Это, конечно, также могло быть вызвано неправильным переводом в часовом поясе в программном обеспечении для обмена сообщениями, если время было отправлено, а не синхронизировано).

2018-12-18T00:00Z

Я всегда использую поля DATETIME для чего-либо другого, кроме метаданных строк (дата создана или изменена).