Форматы времени в MySQL: TIMESTAMP vs DATE[TIME]

В MySQL 5 есть несколько типов данных для хранения даты и времени. Это TIMESTAMP, DATE, DATETIME, TIME и YEAR. Все они обладают своими особенностями, и выбор в пользу того или иного календарного типа должен производиться отдельно в каждой конкретной ситуации. Я хотел бы поделиться с вами результатом моего сегодняшнего миниисследования этих типов, в том числе в аспекте работы с временными зонами.Итак, все календарные типы данных подробно описаны в разделе «10.3. Date and Time Types» руководства по MySQL. А важная информация, касающаяся поддержки СУБД временных зон, расписана в разделе «9.7. MySQL Server Time Zone Support». Все следующее далее базируется на изучении руководства. В то же время, в здесь указаны лишь нюансы выбора в пользу того или иного типа, поэтому этот материал никак не заменяет мануал, но дополняет его.

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

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

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

Преобразование временной метки Unix в удобочитаемую дату с использованием MySQL

спросил

Изменено 10 месяцев назад

Просмотрено 431 тысяч раз

Есть ли функция MySQL, которую можно использовать для преобразования временной метки Unix в удобочитаемую дату? У меня есть одно поле, в котором я сохраняю время Unix, и теперь я хочу добавить еще одно поле для удобочитаемых дат.

  • mysql
  • unix-timestamp

2

Использовать FROM_UNIXTIME() :

 ВЫБРАТЬ
  FROM_UNIXTIME (отметка времени)
ОТ
  ваша_таблица;

См. также: Документация MySQL по FROM_UNIXTIME() .

4

Что отсутствует в других ответах (на момент написания этой статьи) и не очевидно, так это то, что from_unixtime может принимать второй параметр для указания формата, например:

 SELECT
  from_unixtime (отметка времени, '%Y %D %M %H:%i:%s')
ОТ
  your_table

1

Я думаю, что вы ищете FROM_UNIXTIME()

Вот один вкладыш, если у вас есть быстрый доступ к MySQL cli:

 mysql> выберите convert_tz(from_unixtime(1467095851), 'UTC', 'MST ') как "местное время";
+---------------------+
| местное время |
+---------------------+
| 27. 06.2016 23:37:31 |
+---------------------+

Замените 'MST' на желаемый часовой пояс. Я живу в Аризоне 🌵 поэтому конвертация из UTC в MST.

Зачем сохранять поле как читаемое? Только мы AS 

 ВЫБРАТЬ theTimeStamp, FROM_UNIXTIME (theTimeStamp) AS readDate
               Из таблицы
               ГДЕТаблица.поле = значение;

РЕДАКТИРОВАТЬ: Извините, мы храним все в миллисекундах, а не в секундах. Починил это.

Вы можете использовать функцию DATE_FORMAT. Вот страница с примерами и шаблонами, которые вы можете использовать для выбора различных компонентов даты.

Легкий и простой способ:

выберите from_unixtime (column_name, '%Y-%m-%d') из table_name

Поскольку я обнаружил этот вопрос, не зная, что mysql всегда хранит время в полях меток времени в формате UTC но будет отображаться (например, phpmyadmin) в местном часовом поясе. Я хотел бы добавить свои выводы.

У меня есть автоматически обновляемое поле last_modified, определенное как:

 `last_modified` отметка времени NOT NULL ПО УМОЛЧАНИЮ CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Глядя на это с помощью phpmyadmin, похоже, что это местное время, а внутри UTC

 SET time_zone = '+04:00'; // или '+00:00' для отображения дат в формате UTC или 'UTC', если установлены часовые пояса. 
SELECT last_modified, UNIX_TIMESTAMP(last_modified), from_unixtime(UNIX_TIMESTAMP(last_modified), '%Y-%c-%d %H:%i:%s'), CONVERT_TZ(last_modified,@@session.time_zone,'+00:00 ') как UTC FROM `table_name`

В любом созвездии UNIX_TIMESTAMP и ‘as UTC’ всегда отображаются во времени UTC.

Запустите это дважды, сначала без установки часового пояса.

Если вы хотите преобразовать время И отобразить данные в определенном формате, вы можете использовать эту строку.

 date_format (convert_tz (from_unixtime (TIMESTAMP), 'UTC', 'ЖЕЛАЕМЫЙ TZ'), '%m/%d/%y')

, где вы добавляете convert_tz к строке date_format. %m/%d/%y равно месяцу/дню/году . вы можете найти все конкретные форматы здесь https://www.w3schools.com/sql/func_mysql_date_format.asp

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

datetime — MySQL преобразует строку даты в отметку времени Unix

спросил

Изменено 3 месяца назад

Просмотрено 387 тысяч раз

Как преобразовать следующий формат в метку времени unix?

 15 апреля 2012 г. , 00:00

Формат, который я получаю из БД, кажется, имеет в конце AM . Я пробовал использовать следующее, но это не сработало:

 CONVERT(DATETIME, Sales.SalesDate, 103) AS DTSALESDATE,
CONVERT(TIMESTAMP, Sales.SalesDate, 103) КАК TSSALESDATE
где значение Sales.SalesDate — 15 апреля 2012 г., 00:00.
  • mysql
  • datetime
  • метка времени
  • метка времени unix

3

Вот пример того, как преобразовать DATETIME в отметку времени UNIX :
SELECT UNIX_TIMESTAMP(STR_TO_DATE('15 апреля 2012, 00:00', '%M %d %Y %h:%i%p') )

Вот пример того, как изменить формат даты :
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('15 апреля 2012 00:00', '%M %d %Y %h:%i%p') ),'%m-%d-%Y %h:%i:%p')

Документация: UNIX_TIMESTAMP , FROM_UNIXTIME

2

Вам, безусловно, придется использовать как STR_TO_DATE , чтобы преобразовать дату в стандартный формат даты MySQL, так и UNIX_TIMESTAMP , чтобы получить от нее метку времени.

No related posts.