Типы 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 происходит при выполнении любого из следующих условий:
Для остальных (кроме первого) столбцов типа TIMESTAMP также можно задать установку в значение текущих даты и времени. Для этого необходимо просто установить столбец в NULL или в NOW() .
Любой столбец типа 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 могут быть заданы любым стандартным набором форматов:
Недопустимые значения величин DATETIME , DATE или T IMESTAMP преобразуются в значение ""ноль"" соответствующего типа величин ("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» - это и есть 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 января по локальному времени, ничего переводить тут не понадобится.
типы данных даты и времени : 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 извлекает значения для данного типа даты или времени только в стандартном формате, но в то же время пытается интерпретировать разнообразные форматы, которые могут поступать от пользователей (например, когда задается величина, которой следует присвоить тип даты или времени или сравнить со значением, имеющим один из этих типов). Тем не менее, поддерживаются только форматы, описанные в следующих разделах. Предполагается, что пользователь будет вводить допустимые значения величин, так как использование величин в других форматах может дать непредсказуемые результаты.
Тип данных 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 и с более короткой строкой вывода:
Тип столбца | Формат вывода |
---|---|
TIMESTAMP(14) | YYYYMMDDHHMMSS |
TIMESTAMP(12) | YYMMDDHHMMSS |
TIMESTAMP(10) | YYMMDDHHMM |
TIMESTAMP(8) | YYYYMMDD |
TIMESTAMP(6) | YYMMDD |
TIMESTAMP(4) | YYMM |
TIMESTAMP(2) | YY |
Величины DATETIME , DATE и TIMESTAMP могут быть заданы любым стандартным набором форматов:
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.
Недопустимые величины YEAR преобразуются в 0000.
Я не рекомендую использовать ни поле DATETIME, ни TIMESTAMP. Если вы хотите представить определенный день в целом (например, день рождения), используйте тип DATE, но если вы более конкретны, вы, вероятно, заинтересованы в записи фактического момента, а не единицы время (день, неделя, месяц, год). Вместо использования DATETIME или TIMESTAMP используйте BIGINT и просто сохраните количество миллисекунд с эпохи (System.currentTimeMillis (), если вы используете Java). Это имеет ряд преимуществ:
Эта проблема тесно связана с тем, как вы должны хранить денежную стоимость (т.е. 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 для чего-либо другого, кроме метаданных строк (дата создана или изменена).
Типы данных date, time и datetime используются для хранения соответственно даты, времени и даты и времени одновременно. Гораздо удобнее хранить дату и время в формате одного из предназначенных для этого типов данных, а не в виде строки символов. Если вы храните дату и время таким образом, то их проще выводить на экран, поскольку SQL Server автоматически придает им привычный формат. Для этих типов данных можно также использовать специальные функции обработки значений типа дата и время.
Если же хранить дату и время как значения типа char, varchar или одного из числовых типов данных, то, естественно, при выводе на экран их формат окажется далеко не тем, к которому мы привыкли.
Следует заметить, что все настройки («по умолчанию») зависят от настроек сервера , поэтому перед внесением каких-либо данных в свою базу следует проверить настройки сервера.
Тип datetime . Позволяет определить для хранения в столбце таблицы дату и время 15.04.00 13:05.
Для отображения значений, хранящихся в виде данных типа datetime, чаще всего (по умолчанию) используется формат: Д ММММ ГГГГ ’г’ (или кратко Д. ММ. ГГ) Ч:мм:сс, например, 12 июня 2000 (или 12.06.00) 22:33:50 . При употреблении значений типа datetime в инструкции INSERT или любой другой их надо ставить в одинарные кавычки. Допускается ввести сначала дату, а потом время, или наоборот, поскольку SQL Server может отличить одно значение от другого и сохранить все так, как нужно.
При вводе значения типа datetime для даты можно использовать как верхний, так и нижний регистры, а также вставлять один или более пробелов между месяцем, числом и годом.
Дату можно ввести разными способами. SQL Server распознает каждый из форматов даты и сохраняет значение правильно. Можно ввести дату в алфавитном формате с помощью либо аббревиатуры месяца, либо полного имени месяца. Между числом, месяцем и годом ставятся точка или косая черта.
Если пропустить часть года, обозначающую век, то декада, меньшая пятидесяти, будет представлена с двадцать первым веком, а декада, большая пятидесяти – с двадцатым. Год необходимо вводить полностью в том случае, если в дате пропущено число. Когда вы вводите дату без числа, это значение будет равно первому числу (по умолчанию).
Время должно вводиться в следующем порядке: часы, минуты, секунды и миллисекунды (если они предусмотрены настройками). Между блоками времени в обязательном порядке должен ставиться разделитель (двоеточие). Он необходим, чтобы распознавать вводимые цифры как время, а не как дату.
В следующей таблице показаны различные интерпретации даты и времени в значении типа datetime.