DATETIME, DATE и TIMESTAMP
TIMEYEARBuy this Reference Manual in softcover from Barnes & Noble!
DATETIME, DATE и TIMESTAMP
Типы 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 происходит при выполнении любого из следующих условий:
INSERT или LOAD DATA INFILE.UPDATE, и при этом изменяется величина в некотором другом столбце (следует отметить, что команда UPDATE, устанавливающая столбец в то же самое значение, которое было до выполнения команды, не вызовет обновления столбца TIMESTAMP, поскольку в целях повышения производительности MySQL игнорирует подобные обновления при установке столбца в его текущее значение).TIMESTAMP явно установлена в NULL.Для остальных (кроме первого) столбцов типа TIMESTAMP также можно задать установку в значение текущих даты и времени. Для этого необходимо просто установить столбец в NULL или в NOW().
Любой столбец типа TIMESTAMP (даже первый столбец данного типа) можно установить в значение, отличное от текущих даты и времени. Это делается путем явной установки его в желаемое значение. Данное свойство можно использовать, например, если необходимо установить столбец TIMESTAMP в значение текущих даты и времени при создании строки, а при последующем обновлении этой строки значение столбца не должно изменяться:
TIMESTAMP при создании данной строки. Столбец будет установлен в исходное состояние со значением текущих даты и времени.TIMESTAMP в его текущее значение.Однако, с другой стороны, для этих целей, возможно, будет проще использовать столбец DATETIME. При создании строки его следует инициализировать функцией NOW() и оставить в покое при последующих обновлениях.
Величины типа TIMESTAMP могут принимать значения от начала 1970 года до некоторого значения в 2037 году с разрешением в одну секунду. Эти величины выводятся в виде числовых значений.
Формат данных, в котором MySQL извлекает и показывает величины TIMESTAMP, зависит от количества показываемых символов. Это проиллюстрировано в приведенной ниже таблице. Полный формат TIMESTAMP составляет 14 десятичных разрядов, но можно создавать столбцы типа TIMESTAMP и с более короткой строкой вывода:
| Тип столбца | Формат вывода |
TIMESTAMP(14) |
YYYYMMDDHHMMSS |
TIMESTAMP(12) |
YYMMDDHHMMSS |
TIMESTAMP(10) |
YYMMDDHHMM |
TIMESTAMP(8) |
YYYYMMDD |
TIMESTAMP(6) |
YYMMDD |
TIMESTAMP(4) |
YYMM |
TIMESTAMP(2) |
YY |
Независимо от размера выводимого значения размер данных, хранящихся в столбцах типа TIMESTAMP, всегда один и тот же. Чаще всего используется формат вывода с 6, 8, 12 или 14 десятичными знаками. При создании таблицы можно указать произвольный размер выводимых значений, однако если этот размер задать равным 0 или превышающим 14, то будет использоваться значение 14. Нечетные значения размеров в интервале от 1 до 13 будут приведены к ближайшему большему четному числу.
Величины 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).Недопустимые значения величин DATETIME, DATE или TIMESTAMP преобразуются в значение ``ноль'' соответствующего типа величин ('0000-00-00 00:00:00', '0000-00-00', или 00000000000000).
Для величин, представленных как строки, содержащие разделительные знаки между частями даты, нет необходимости указывать два разряда для значений месяца или дня, меньших, чем 10. Так, величина '1979-6-9' эквивалентна величине '1979-06-09'. Аналогично, для величин, представленных как строки, содержащие разделительные знаки внутри обозначения времени, нет необходимости указывать два разряда для значений часов, минут или секунд, меньших, чем 10. Так,
Величины, определенные как числа, должны иметь 6, 8, 12, или 14 десятичных разрядов. Предполагается, что число, имеющее 8 или 14 разрядов, представлено в форматах YYYYMMDD или YYYYMMDDHHMMSS соответственно, причем год указан в первых четырех разрядах. Если же длина числа 6 или 12 разрядов, то предполагаются соответственно форматы YYMMDD или YYMMDDHHMMSS, где год указан в первых двух разрядах. Числа, длина которых не соответствует ни одному из описанных вариантов, интерпретируются как дополненные спереди нулями до ближайшей вышеуказанной длины.
Величины, представленные строками без разделительных знаков, интерпретируются с учетом их длины согласно приведенным далее правилам. Если длина строки равна 8 или 14 символам, то предполагается, что год задан первыми четырьмя символами. В противном случае предполагается, что год задан двумя первыми символами. Строка интерпретируется слева направо, при этом определяются значения для года, месяца, дня, часов, минут и секунд для всех представленных в строке разделов. Это означает, что строка с длиной меньше, чем 6 символов, не может быть использована. Например, если задать строку вида '9903', полагая, что это будет означать март 1999 года, то MySQL внесет в таблицу ``нулевую'' дату. Год и месяц в данной записи равны 99 и 03 соответственно, но раздел, представляющий день, пропущен (значение равно нулю), поэтому в целом данная величина не является достоверным значением даты.
При хранении допустимых величин в столбцах типа TIMESTAMP используется полная точность, указанная при их задании, независимо от количества выводимых символов. Это свойство имеет несколько следствий:
TIMESTAMP(4) или TIMESTAMP(2). В противном случае задаваемая величина не будет допустимым значением даты и будет храниться как 0.TIMESTAMP путем использования команды ALTER TABLE будет выводиться ранее ``скрытая'' информация.TIMESTAMP хранимая информация не будет потеряна, если не принимать во внимание, что при выводе информации будет выдаваться меньше.TIMESTAMP хранятся с полной точностью, непосредственно может работать с этим исходным хранимым значением величины только функция UNIX_TIMESTAMP(). Остальные функции оперируют форматированными значениями извлеченной величины. Это означает, что нельзя использовать такие функции, как HOUR() или SECOND(), пока соответствующая часть величины TIMESTAMP не будет включена в ее форматированное значение. Например, раздел HH столбца TIMESTAMP не будет выводиться, пока количество выводимых символов не станет по меньшей мере равным 10, так что попытки использовать HOUR() для более коротких величин TIMESTAMP приведут к бессмысленным результатам.Величины одного типа даты можно в ряде случаев присвоить объекту другого типа даты. Однако при этом возможны некоторое изменение величины или потеря информации:
DATE объекту DATETIME или TIMESTAMP, то в результирующей величине ``временная'' часть будет установлена в '00:00:00', так как величина DATE не содержит информации о времени.DATE, DATETIME или TIMESTAMP объекту DATE, то ``временная'' часть в результирующей величине будет удалена, так как тип DATE не включает информацию о времени.DATETIME, DATE и TIMESTAMP могут быть указаны с использованием одного и того же набора форматов, следует помнить, что указанные типы имеют разные интервалы допустимых значений. Например, величины типа TIMESTAMP не могут иметь значения даты более ранние, чем относящиеся к 1970 году или более поздние, чем относящиеся к 2037 году. Это означает, что такая дата, как '1968-01-01', будучи разрешенной для величины типа DATETIME или DATE, недопустима для величины типа TIMESTAMP и будет преобразована в 0 при присвоении этому объекту.Задавая величины даты, следует иметь в виду некоторые ``подводные камни'':
'10:11:12', благодаря разделителю `:' могла бы оказаться величиной времени, но, используемая в контексте даты, она будет интерпретирована как год '2010-11-12'. В то же время величина '10:45:15' будет преобразована в '0000-00-00', так как для месяца значение '45' недопустимо.00-31, месяцы 00-12, года 1000-9999. Любая дата вне этого диапазона преобразуется в 0000-00-00. Следует отметить, что, тем не менее, при этом не запрещается хранить неверные даты, такие как 2002-04-31. Это позволяет веб-приложениям сохранять данные форм без дополнительной проверки. Чтобы убедиться в достоверности даты, выполняется проверка в самом приложении.00-69 преобразуются в 2000-2069.70-99 преобразуются в 1970-1999.| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
To get a MySQL compatible DATETIME string with
php, use the following:
$datetime = date("Y-m-d H:i:s");
| Posted by Fredrik Johansson on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Usually I don't want to make the timestamp string
in php and then query it to the db as many people
seem to do, then its better to do a "insert into
db values(now(),'otherstuff');" for a perfect fitting
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Documentation fix: note that the character ':' is
a COLON, and the character ';' is a SEMICOLON.
The time delimiter is ':' or COLON.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
A way to display a Timestamp as a normal date-
time, assuming that datetime is the Timestamp
field:
select DATE_FORMAT(datetime, \'%Y-%m-%d %T\')
from some_table
| Posted by on Wednesday December 18 2002, @5:28pm | [Delete] [Edit] |
A first reading of this page makes me seriously
worried about the implied lack of design.
Knowning just how dangerous bad date parsing can
be the ability to
| Posted by Andrius Juozapaitis on Monday November 18 2002, @4:22pm | [Delete] [Edit] |
2 pitfalls I've encountered with mysql timestamps:
1) timestamp in mysql differs from the unix-
timestamp in php.
2) this automatic-update-one-but-not-the-other
column sounds, and actually is, scary. and it's not
intuitive too - I was figthing with all types of weird
behaviour until I found this manual here ;-). my
suggestion - use datetime with now() instead.
| Posted by PJ Kix on Thursday December 5 2002, @4:09am | [Delete] [Edit] |
if you need to use an UPDATE query but don't want
your timestamp columns to be automagically
updated as well just be sure to set them to
themselves in the SQL statement like so ...
UPDATE `table` SET `views` = `views`+1 ,
`timestamp` = `timestamp` WHERE `row_id`
= "242"
that way the the updates will take place but the
timestamps will not update themselves.
Hope it helps someone.
-=PJK=-
| Posted by on Tuesday February 18 2003, @1:12am | [Delete] [Edit] |
I would prefer if the value for this types of fields (date, datetime,timestamp,etc) is invalid, do not insert it at all, prompt the ERROR instead, like any other type. Because it at least will prompt user that there is an error occured. If not, user will assuming the entry is valid. I've a occasion (since I'm new in this MySQL) related to this where I'm assuming the entry is exactly valid. Friends of mine also who start to working with MySQL facing the same problem.
| Posted by [name withheld] on Wednesday February 19 2003, @4:34am | [Delete] [Edit] |
I find it weird that if i have a table including two colums: start_time and end_time of the type DateTime than if i use update to add a value for start_time .. it sets it without changing end_time .. but if i set end_time at now() for example than it will change start_time to the value of now().
| Posted by [name withheld] on Sunday February 23 2003, @4:21pm | [Delete] [Edit] |
I realy wish timezone would be stored with the time values!
| Posted by Jayaram Bhogi on Wednesday March 5 2003, @3:48am | [Delete] [Edit] |
This automatic insertion of the current date value to the first Timestamp column does not look meaningful. Other databases, if the timestamp column is not provided any value means, the value will be null there. If one want to migrate his application from such a database to MySQL, the entire functionality related to Timestamp filed has to be rewritten.
I feel this automatic updation of the first Timestamp column should be optional to the user while he creates the table with Timestamp column.
Add your own comment.