Timestamp для чего нужен
Календарные типы данных в MySQL: особенности использования
В MySQL 5 есть несколько типов данных для хранения даты и времени. Это TIMESTAMP, DATE, DATETIME, TIME и YEAR. Все они обладают своими особенностями, и выбор в пользу того или иного календарного типа должен производиться отдельно в каждой конкретной ситуации. Я хотел бы поделиться с вами результатом моего сегодняшнего миниисследования этих типов, в том числе в аспекте работы с временными зонами.
Итак, все календарные типы данных подробно описаны в разделе «10.3. Date and Time Types» руководства по MySQL. А важная информация, касающаяся поддержки СУБД временных зон, расписана в разделе «9.7. MySQL Server Time Zone Support». Все следующее далее базируется на изучении руководства. В то же время, в здесь указаны лишь нюансы выбора в пользу того или иного типа, поэтому этот материал никак не заменяет мануал, но дополняет его.
Проанализировав описание типов, представленное выше, можно сделать практически все выводы о достоинствах и недостатках тех или иных типов. Все довольно просто и очевидно.
Но прежде, чем рассказать об использовании этих типов, хочу заметить, что на практике часто используется другой тип для хранения даты и времени: целочисленное значение (для хранения даты — 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 или timestamp
На днях я столкнулся с тем, что многие разработчики не знают в чём отличие типов данных DATETIME и TIMESTAMP в MySQLе, а так же как хранить дату и время, если необходимо учитывать разные часовые пояса для разных пользователей веб-приложения. Поэтому хочу дать ниже разъяснения с пояснениями.
DATETIME
Хранит время в виде целого числа вида YYYYMMDDHHMMSS, используя для этого 8 байтов. Это время не зависит от временной зоны. Оно всегда отображается при выборке точно так же, как было сохранено, независимо от того какой часовой пояс установлен в MySQL. Даю пример:
mysql> create table `dt1` ( col datetime NOT NULL );
mysql> SET @@session.time_zone=’+00:00′;
mysql> select now();
+———————+
| now() |
+———————+
| 2009-06-04 18:13:56 |
+———————+
mysql> insert into dt1 values(now());
mysql> insert into dt1 values(now());
TIMESTAMP
Хранит 4-байтное целое число, равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича (т.е. нулевой часовой пояс, точка отсчёта часовых поясов). При получении из базы отображается с учётом часового пояса. Часовой пояс может быть задан в операционной системе, глобальных настройках MySQL или в конкретной сессии. Запомните, что сохраняется всегда количество секунд по UTC (универсальное координированное время, солнечное время на меридиане Гринвича), а не по локальному часовому поясу. Пример:
Ещё одно отличие! TIMESTAMP по умолчанию NOT NULL, а его значение по умолчанию равно NOW().
mysql> insert into dt1 values(null);
ERROR 1048 (23000): Column ‘col’ cannot be null
mysql> insert into tm1 values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tm1;
+———————+
| col |
+———————+
| 2009-06-04 18:25:08 |
| 2009-06-04 18:25:26 |
| 2009-06-04 18:32:50 |
+———————+
Дополнение. Для тех, кого смущает использование функции NOW().
Как работать с метками времени (timestamp) в PostgreSQL?
Тема работы с временными метками в PostgreSQL плохо раскрыта в русскоязычных профильных публикациях в Интернете и служит частым источником проблем в работе программистов. Предлагаю вашему вниманию перевод материала от Hubert Lubaczewski, автора популярного зарубежного блога depesz.com. Надеюсь, статья будет для вас полезна!
Время от времени в IRC или в почтовых рассылках кто-нибудь задает вопросы, которые показывают глубокое непонимание (или недостаток понимания) меток времени, особенно тех, которые учитывают часовые пояса. Так как я уже сталкивался с этим ранее, позвольте мне рассказать, что такое timestamps, как с ними работать и с какими наиболее распространенными загвоздками вы можете столкнуться.
У нас есть два типа данных, которые мы можем использовать:
Давайте представим, что у вас есть временная метка “2014-04-04 20:00:00″. О чем она вам говорит? К сожалению, не о многом. Всё зависит от того, о какой точке планеты идет речь. Восемь вечера 4-го апреля – это разный момент времени в Лос Анджелесе, Чикаго, Лондоне, Варшаве или Москве. В этом проблема часовых поясов.
Конечно, вы можете подумать: «Я всегда буду в одном часовом поясе, мне не нужно заморочек с поддержкой разных временных зон. В моем часовом поясе даты и времени будет вполне достаточно, чтобы отметить какой-либо момент времени, ведь именно так мы делаем в «реальной жизни».
Но так ли это на самом деле?
Представим, что у вас есть метка ‘2013-10-27 02:00:00′, и вы знаете, что ваше приложение привязано к польскому времени. В этом случае, вам уже не повезло, потому что это может быть 2 часа ночи по центрально-европейскому летнему времени (CEST) или на час больше, по обычному центрально-европейскому времени. Всё из-за сезонного перевода часов.
Я считаю, что использование временных меток без часового пояса почти всегда является багом, и его нужно исправлять. Проблем становится еще больше, если, записи в вашем приложении поступают из разных часовых поясов (например, приложение-планировщик).
Так что самое очевидное решение – использовать метки времени с часовыми поясами (timestamptz).
Во-первых, это не займет больше места на диске:
Как же это работает? Метка должна знать часовой пояс, так почему же для этого не требуется больше места?
Дело в том, что она не знает часовой пояс. Внутри, все значения в колонках timestamptz указаны в формате UTC (всемирное координированное время).
У UTC есть приятные особенности: у него нет смещения (он сам является отправной точкой, от которой считаются смещения других часовых поясов), и у него нет разницы между летним и зимним временем. Так что любая временная метка в формате UTC всегда гарантированно указывает только на одну точку во времени.
Но если всё время указывать по UTC, то как я узнаю время в нужном мне часовом поясе?
Каждый раз, когда речь идет о значениях timestamptz, если часовой пояс не указан, то PostgreSQL использует заранее сконфигурированное время. И вы можете конфигурировать его разными способами:
Следующие два способа меняют значение по-умолчанию для выбранной базы данных и пользователя.
Последний способ можно использовать, если вы хотите, чтобы ваше соединение с базой данных работало с иными настройками.
Примите во внимание, как при этом меняется вывод now():
Так что каждый раз, когда вы просматриваете или меняете значения timestamptz, PostgreSQL конвертирует их в/из UTC.
Это значит, что значения можно легко сравнивать (все они в одном часовом поясе, нет сдвигов на летнее или зимнее время, так что сравнение всегда возможно).
Что произошло? Почему не показывается 8 вечера?
Причина проста – в запрос я вставил timestamp в каком-то часовом поясе. Внутри, метка была сконвертирована в UTC, а затем, снова сконвертирована (возможно, даже без UTC, я не уверен) в мой обычный часовой пояс, которым является:
Если бы у меня был установлен часовой пояс Лос Анджелеса, то результат запроса был бы таким:
Важно понимать, что выводимое значение всегда принимает во внимание настройку часового пояса.
Есть еще один способ получить 20:00 в Лос Анджелесе:
Очень важно добавлять “::timestamp» после значения, иначе мы получим что-то странное:
Что здесь произошло? Откуда взялось 11:00?
Значение в кавычках (2014-04-04 20:00:00) воспринимается как timestamptz, что значит 8 вечера в моём часовом поясе:
И только после перевода значения в мой часовой пояс PG считывает “at time zone …», которая используется для отображения времени в выбранном часовом поясе.
Таким образом, timestamp at time zone выдаёт значение timestamptz, которое показывает момент, когда местное время в выбранном часовом поясе было таким, как указано в команде.
А timestamptz at time zone выдаёт значение timestamp, которое показывает, каким было время в выбранном часовом поясе в указанный момент времени.
Это звучит немного путанно, поэтому давайте я приведу примеры:
Интересно то, что мы можем использовать это для перевода времени из одного часового пояса в другой, даже если Pg не находится ни в одном из них.
Допустим, мы хотим узнать, который час в Лос Анджелесе, когда в Москве — 8 утра. Моё местное время следующее:
Пользы от него мало.
Для начала нам нужно определить точку во времени (в формате timestamptz), которая показывает 8 утра в Москве:
Это говорит мне о том, что она соответствует 6 утра в моём часовом поясе. Но мы хотим узнать время в Лос Анджелесе. Я мог бы написать ‘2014-04-04 06:00:00+02′ в часовом поясе ‘LA’, но можно сделать по-другому:
Надеюсь, теперь вам всё ясно. Я сам довольно долго пытался разобраться в этом вопросе, и наконец-то всё понял 🙂
У всего этого есть один интересный побочный эффект: не так-то просто добавить индексы к функциям, работающим с timestamptz. Например, вы не можете создать индекс, который будет использоваться для получения дня недели:
Как показано в примере выше, причина очень проста – одна и та же точка во времени может относиться к разным дням недели в зависимости от часового пояса. А поскольку to_char() использует текущий часовой пояс, он может выдавать разные значения для одних и тех же исходных данных в зависимости от настроек часового пояса в системе:
Одна и та же точка во времени, но разные дни. Это могут быть разные месяцы или даже разные года, в зависимости от того, где это было.
Временная метка (без часового пояса) здесь “проявляет” сильную сторону – так как в ней не указан часовой пояс, её можно спокойно использовать для извлечения информации.
Но мы же знаем, как переводить timestamptz в timestamp. Нужно просто указать ей часовой пояс. Поэтому мы можем попробовать сделать так:
Но, к сожалению, ничего не выходит. Дело в том, что to_char слишком разносторонний. Вы можете использовать to_char вот так:
На этот раз мы получаем другие результаты не из-за часового пояса, а из-за локали.
Правильным решением проблемы индексирования будет написать свою собственную функцию, которая будет вызывать to_char в абсолютно постоянной «среде», а затем ее уже индексировать. Вот так:
А теперь мы можем использовать ее для индексирования:
Это безопасно, потому что сама функция заставляет часовой пояс принимать значение «Poland», и она вызывает to_char таким образом, чтобы игнорировать значение локали (другими словами, в формате to_char нет префикса TM).
Естественно, чтобы воспользоваться этим индексом, все запросы должны также использовать эту функцию:
Еще одна важная вещь при работе с часовыми поясами – получение времени Unix, или так называемой эпохи. В целом, это просто:
Интересно то, что оно не зависит от часового пояса:
Причина, известная не всем, кроется в том, что время Unix всегда принимается в часовом поясе UTC. Это значит, что, когда вы извлекаете эпоху из временной метки timestamp, PG предполагает, что она находится в UTC. Из чего вытекают следующие потенциальные проблемы:
В первом случае Pg получает «точку во времени», которая внутренне конвертируется в UTC (а когда отображается – преобразовывается в мой часовой пояс, +2).
Во втором случае временная метка находится в моём часовом поясе, но предполагается, что это UTC (без конвертации!), и эпоха берется от значения ‘2014-04-04 21:19:01.456205 UTC’, а не ‘2014-04-04 21:19:01.456205+02′.
Короче говоря, старайтесь избегать timestamp и используйте timestamptz.
Последнее, о чём я хотел бы сказать – это не баг или потенциальная проблема, а скорее функциональность, о которой многие не знают.
Как вы видели, PostgreSQL использует timestamp (и timestamptz) с точностью до микросекунд. Многие люди настаивают на том, чтобы точность была только до секунды, хотя лично мне это не нравится.
И timestamp, и timestamptz (и другие виды данных, относящиеся ко времени) могут иметь дополнительную точность (“precision”).
Давайте я приведу простой пример:
Конечно, вы можете использовать это и в таблицах:
Отлично! Вам не нужно менять “now()» или что-либо еще, просто добавьте точность к типу данных, и она всё скорректирует.
Я упомянул, что мне это не нравится. Причина проста – в любой достаточно нагруженной системе секунда – слишком низкий уровень точности. Тем более, что хранение данных с точностью до микросекунды ничего мне не стоит, но может быть полезным. С другой стороны, если данные до микросекунд, то как мне сделать, чтобы значения отображались без долей секунды?
Все просто: я использую (в запросах SELECT) фунуции to_char(), или date_trunc, или даже приведение к типу timestamptz(0):
Более подробно о том, как работать с timestamps, мы собираемся рассказать на конференции PG Day’16 Russia в июле 2016 года! Готовьте свои вопросы, мы постараемся на них ответить.
Unix время и Unix Timestamp (MySQL, PHP, JavaScript)
Почему Unix время начинается с 1 января 1970 года
Все дело в том, что Unix время начинает отсчет эпохи Unix, с выпуска первой UNIX системы. Первая система подобного рода была создана в 1969 году, поэтому точкой отсчета времени разработчики приняли дату с 1 января 1970 года в полночь по UTC (Всемирное координированное время).
Давайте разберемсяс тем, для чего нужны Unix время и Unix Timestamp, а также дадим им четкие понятия.
Unix время – это текущее количество секунд прошедших с 1 января 1970 года.
Unix Timestamp – это метка времени, которая представляет собой последовательность символов, отражающих количество секунд, прошедших с 1 января 1970 года.
Попробую привести пример, для разъяснения разницы этих двух понятий.
На время написания мной данного поста, Unix время было равно 1346765877.
Откровенно говоря, особого смысла разделять два понятия, на мой взгляд, нет, но все же полезно иметь представление о том, что из-себя представляет Unix Time, а также полезно понимать, что количество максимально возможных секунд прошедших с 1970 года, имеет предел!
Конец эпохи Unix придёт в 2038 году
Факт: максимальным двоичным числом в 32 битных системах является число 01111111 11111111 11111111 11111111, переведя его в десятичную систему, мы получим число 2147483647.
19 января 2038 года в 03:14:08 настанет момент, когда количество секунд прошедших с начала эры Unix, превысит максимальное, доступное в 32 битной системе, число = 2147483647. При переполнении разряда, произойдет сброс даты.
Проверить эту теорию на наглядном примере очень просто:
Если продолжить добавлять единицу, то мы получим циклическое замыкание.
Именно такое кольцевание дат произойдет с 19 января 2038 года на всех системах использующих 32 битную архитектуру.
На самом деле не стоит печалиться, ведь разработчики вычислительных систем все больше внедряют 64 битные архитектуры в повсеместное использование. Будем верить в то, что они успеют к 2038 году.
Теперь поговорим об использовании unix timestamp в php, mysql и даже в javascript.
Работа с unix timestamp
Очень важным моментом, при работе с unix timestamp в php или mysql, является необходимость четкого понимать плюсы и минусы такого формата даты.
Например, TIMESTAMP не получится использовать для задания исторических событий или событий далекого будущего. Весь набор дат ограничен периодом с 1970 по начало 2038 года. Если задать дату, выходящую за рамки 2038, она будет не правильно интерпретирована 32 битной системой.
Осознав это ограничение, напрашивается логический вопрос: «Зачем нужно заморачиваться с представлением даты в секундах?«
Когда следует использовать Unix Timestamp
Для представления времени в обычной для нас системе его измерения, требуется 8 байт, а для unix timestamp вдвое меньше – 4 байта.
Экономия объема данных, на мой взгляд, основной и неоспоримый плюс в использовании Unix Time.
Кроме того есть ряд полезных нюансов доступных при работе с UNIX timestamp в mysql. А поскольку вся информация должна храниться на сервере баз данных, и он в свою очередь имеет ряд преимуществ, при работе с метками Unix времени, то выбор в сторону unix timestamp можно корректно обосновать следующими положениями.
В MySQL предусмотрен соответствующий тип данных Timestamp для работы с форматом unix-времени, установив который мы сразу получаем полезное преимущество, перед стандартными форматами DATE и DATETIME. Преимущество заключается в том, что выполняя операцию добавления новой записи в таблицу, столбец с этим типом данных заполняется автоматически. А это значит, что мы можем сэкономить не только на объеме данных, но и на процессорном времени веб сервера.
Для подкрепления слова делом поставим такую задачу: при регистрации нового пользователя в системе, нужно заносить дату его добавления в базу данных.
Если тип поля хранящего дату в таблице – DATETIME, то запрос из PHP скрипта будет выглядеть примерно так:
В случае, когда поле date имеет тип TIMESTAMP запрос будет таким: