Календарные типы данных в MySQL: особенности использования / Хабр
В MySQL 5 есть несколько типов данных для хранения даты и времени. Это TIMESTAMP, DATE, DATETIME, TIME и YEAR. Все они обладают своими особенностями, и выбор в пользу того или иного календарного типа должен производиться отдельно в каждой конкретной ситуации. Я хотел бы поделиться с вами результатом моего сегодняшнего миниисследования этих типов, в том числе в аспекте работы с временными зонами.
Итак, все календарные типы данных подробно описаны в разделе «10.3. Date and Time Types» руководства по MySQL. А важная информация, касающаяся поддержки СУБД временных зон, расписана в разделе «9.7. MySQL Server Time Zone Support». Все следующее далее базируется на изучении руководства. В то же время, в здесь указаны лишь нюансы выбора в пользу того или иного типа, поэтому этот материал никак не заменяет мануал, но дополняет его.
Вначале краткая характеристика каждого из типов:
- TIMESTAMP — тип данных для хранения даты и времени.
- 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.
Проанализировав описание типов, представленное выше, можно сделать практически все выводы о достоинствах и недостатках тех или иных типов. Все довольно просто и очевидно.
Но прежде, чем рассказать об использовании этих типов, хочу заметить, что на практике часто используется другой тип для хранения даты и времени: целочисленное значение (для хранения даты — 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, но не главное. Главное
DATETIME и DATE хранят и выводят всегда одно и то же время, независимо от временных зон.Временные зоны устанавливаются в СУБД MySQL глобально или для текущего подключения.Последнее можно использовать для обеспечения работы разных пользователей в разных временных зонах на уровне СУБД. Все значения времени физически будут храниться в UTC, а приниматься от клиента и отдаваться клинту — в значениях его временной зоны. Но только при использовании типа данных TIMESTAMP. DATE и DATETIME всегда принимают, хранят и отдают одно и то же значение.
Функция NOW() и ее синонимы возвращают значение времени в текущей временной зоне пользователя.
Учитывая все эти обстоятельства, необходимо быть крайне внимательными при изменении временной зоны в пределах подключения к серверу и использовании типов DATE и DATETIME. Если надо хранить дату (например, дату рождения), то никаких проблем не будет. Дата рождения в любой зоне одинаковая. Т.е. если вы родились 1 января в 0:00 UTC/GMT+0, то это не значит, что в Америке будут праздновать ваш день рождения 31 декабря.
Пользователь 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 января по локальному времени, ничего переводить тут не понадобится.
Функции даты и времени Функции для работы с датой-временем,напр.
Функции для обработки даты и времени,например ВРЕМЯ,ДАТА,ДЕНЬЯ и т.д.
| Title | Description |
|---|---|
| Микросекунды в MariaDB | Микросекунды поддерживаются начиная с MariaDB 5.3. |
| Единицы измерения даты и времени | Единицы времени или даты |
| ADD_MONTHS | Добавляет количество месяцев к дате. |
| ADDDATE | Добавьте дни или другой интервал к дате. |
| ADDTIME | Добавляет время к времени или дате. |
| CONVERT_TZ | Преобразование времени даты из одного часового пояса в другой. |
| CURDATE | Возвращает текущую дату. |
| CURRENT_DATE | Синоним CURDATE(). |
| CURRENT_TIME | Синоним CURTIME(). |
| CURRENT_TIMESTAMP | Синоним NOW(). |
| CURTIME | Возвращает текущее время. |
| DATE FUNCTION | Извлекает часть даты. |
| DATEDIFF | Разница в днях между двумя значениями даты/времени. |
| DATE_ADD | Дата арифметики-добавление. |
| DATE_FORMAT | Форматирует значение даты в соответствии со строкой форматирования. |
| DATE_SUB | Арифметика даты-вычитание. |
| DAY | Синоним для DAYOFMONTH(). |
| DAYNAME | Верните название дня недели. |
| DAYOFMONTH | Возвращает день месяца. |
| DAYOFWEEK | Возвращает индекс дня недели. |
| DAYOFYEAR | Возвращает день года. |
| EXTRACT | Извлекает часть даты. |
| FROM_DAYS | Возвращает дату,указанную в день. |
| FROM_UNIXTIME | Возвращает время с Unix метки времени. |
| GET_FORMAT | Возвращает строку форматирования. |
| HOUR | Возвращает час. |
| LAST_DAY | Возвращает последний день месяца. |
| LOCALTIME | Синоним NOW(). |
| LOCALTIMESTAMP | Синоним NOW(). |
| MAKEDATE | Возвращает дату,заданную годом и днем. |
| MAKETIME | Возвращает время. |
| MICROSECOND | Возвращает микросекунды с даты или времени. |
| MINUTE | Возвращается через минуту с 0 до 59. |
| MONTH | Возвращается в месяц с 1 до 12. |
| MONTHNAME | Возвращает полное название месяца. |
| NOW | Возвращает текущую дату и время. |
| PERIOD_ADD | Добавь месяцы к периоду. |
| PERIOD_DIFF | Количество месяцев между двумя периодами. |
| QUARTER | Возвращает квартал года с 1 по 4. |
| SECOND | Возвращается через секунду. |
| SEC_TO_TIME | Преобразует секунду во время. |
| STR_TO_DATE | Преобразует строку в дату. |
| SUBDATE | Вычитайте единицу даты или количество дней. |
| SUBTIME | Вычитает время из даты/времени. |
| SYSDATE | Возвращает текущую дату и время. |
| TIME Function | Извлекает время. |
| TIMEDIFF | Возвращает разницу между двумя датами/времени. |
| TIMESTAMP FUNCTION | Возврат даты или добавление времени к дате/времени. |
| TIMESTAMPADD | Добавить интервал к дате или времени. |
| TIMESTAMPDIFF | Разница между двумя свиданиями. |
| TIME_FORMAT | Форматирует значение времени в соответствии со строкой форматирования. |
| TIME_TO_SEC | Возвращает аргумент времени,преобразованный в секунды. |
| TO_DAYS | Количество дней с года 0. |
| TO_SECONDS | Количество секунд с года 0. |
| UNIX_TIMESTAMP | Возвращает временную метку Unix. |
| UTC_DATE | Возвращает текущую UTC дату. |
| UTC_TIME | Возвращает текущее время UTC. |
| UTC_TIMESTAMP | Возвращает текущую UTC дату и время. |
| WEEK | Возвращает номер недели. |
| WEEKDAY | Возвращает индекс дня недели. |
| WEEKOFYEAR | Возвращает календарную неделю в виде числа в диапазоне от 1 до 53. |
| YEAR | Возвращает год для данной даты. |
| YEARWEEK | Возвращает год и неделю для даты. |
Содержание,воспроизводимое на этом сайте,является собственностью соответствующих владельцев,и это содержание не просматривается заранее компанией MariaDB.Взгляды,информация и мнения,выраженные в этом содержании,не обязательно представляют собой взгляды,информацию и мнения,выраженные MariaDB или любой другой стороной.
Столбцы Mysql DATE и DATETIME в запросах Retool — запросы и ресурсы
церковь
#1
Привет, мы столкнулись с проблемой запросов Retool, когда столбцы mysql DATE и DATETIME (которые хранятся без часового пояса в mysql) преобразуются в метку времени ISO с добавленным часовым поясом. Например, запрос в mysql для столбца типа «дата» вернет:
выберите my_date из my_table;
+------------+ | моя_дата | +------------+ | 2021-04-19 | +------------+
Где результат ответа на запрос перенастройки для того же поля с той же датой:
Последнее неверно, его следует интерпретировать без часового пояса. Обычная мода для столбца даты или даты и времени состоит в том, чтобы хранить данные часового пояса где-то еще и использовать их для преобразования (в этом случае часовой пояс зависит от приложения). Это требует от нас повторного преобразования 100% наших полей даты.
Это ожидаемое поведение или ошибка?
Алекс-В
#2
Эй, Церковь! Это относительно ожидаемо. Все типы данных преобразуются в свои аналоги Javascript внутри Retool, у которого нет типа для даты без времени. Наиболее близким было бы преобразование его в строку, а затем в любом месте, где вы хотите использовать эти данные в некоторой логике JS, вам нужно будет проанализировать их обратно в Date.
Библиотека moment.js включена в состав Retool и может помочь в этом. Если время/часовой пояс неясны, {{ moment(INPUT) }} создаст новый объект даты момента, интерпретируемый в локальном часовом поясе пользователя, а {{ moment.utc(INPUT) }} будет его интерпретировать. время UTC в качестве источника
церковь
#3
Привет Алекс, 9 лет0005
Да, мы использовали moment() довольно интенсивно, но это довольно сложно, так как мы должны делать это для каждого столбца даты, везде, где он используется (а это много).
Я понимаю проблему с датами Javascript. Можем ли мы каким-либо образом, например, указать в ресурсах TZ сеанса для использования во всех запросах, чтобы нам не приходилось вручную преобразовывать каждое поле даты?
Спасибо
alex-w
#4
Не совсем глобальная настройка, но используете ли вы преобразователи запросов? Это позволит вам приводить результаты каждого запроса один раз, а затем ссылаться на преобразованные результаты внутри пользовательского интерфейса/других запросов
церковь
#5
Да, но мне нужно вернуться к большому количеству запросов =)
alex-w
#6
В настройках ресурсов также есть параметр, определяющий, следует ли автоматически преобразовывать типы дат MySQL в объекты JS Date.
Отключение этого может приблизиться к тому, что вы ищете, но имейте в виду, что когда вы измените его, это повлияет на существующую логику, и нижестоящий, вероятно, потребует различных работ, чтобы эти недаты работали должным образом
церковь
#7
О, на самом деле, это работает очень хорошо.
FWIW, момент (отметка времени) не работает, потому что он уже получает код даты «Z», прежде чем мы доберемся до этой точки, исправление состояло в том, чтобы заменить «Z» на «-0500» перед подачей на момент (быстрее чем с использованием преобразования часового пояса с накоплением моментов).
Отключение даты -> временные метки делает именно то, что мы хотим, и, похоже, не влияет на нашу логику, поскольку момент прекрасно обрабатывает даты без временных меток.
церковь
#8
Итак, это похоже на непредвиденные последствия. Кажется, я больше не могу конвертировать временные метки даже после их загрузки. Все временные метки теперь отображаются в формате UTC в таблицах, даже при выборе между датой времени (исходный часовой пояс) и датой времени (местный часовой пояс), кроме того, moment() также не может преобразовать в любой другой часовой пояс.
Есть мысли, как решить эту проблему?
См., например (я в центральном часовом поясе):
Кроме того, переключение между (местный часовой пояс) и (исходный часовой пояс) для даты и времени показывает те же дату и время, с той лишь разницей в конце даты добавляется «UTC».
церковь
#9
Не обращайте внимания на мой предыдущий пост, теперь я понимаю настройку.
Не отключает преобразование строк даты в даты javascript, отключает преобразование всех столбцов даты и времени, включая метки времени , что приводит к потере информации о часовом поясе из БД и предотвращает дальнейшее преобразование раз с этого момента.
Было бы неплохо, если бы эта функция просто отключала родные столбцы без TZ, такие как DATE и DATETIME, оставляя только TIMESTAMP. Но, похоже, нам просто нужно рассмотреть наши варианты использования в отношении сроков выполнения и тому подобного и искать другой способ решить эту проблему.
Работа с датами в MySQL и PHP
Работа с датами в MySQL и PHP
Обычно MySQL и PHP идут рука об руку, как арахисовое масло и шоколад. Как самая популярная база данных с открытым исходным кодом и самый популярный в мире серверный язык сценариев с открытым исходным кодом, MySQL разработан для совместной работы в большинстве случаев.
Однако когда дело доходит до работы с датами и отметками времени, существует некоторый риск потери данных при переводе. К счастью, в PHP и MySQL есть функции и команды, которые могут помочь вам устранить несоответствия между форматами дат MySQL и форматами дат PHP.
Даты MySQL и PHP
PHP хранит даты и метки времени в виде простого целого числа, равного количеству секунд, прошедших с 1 января 1970 года. Это известно как время Unix. Для отображения дат PHP на веб-страницах вы можете использовать функцию date(), чтобы отформатировать ее во что-то читабельное и значимое для пользователей. Но когда дата сохраняется или упоминается, она обрабатывается в формате времени Unix.
В MySQL метки времени хранятся в формате ГГГГ-ММ-ДД ЧЧ:ММ:СС. Только для дат часть ЧЧ:ММ:СС опускается. Например, 26 сентября, 1984 будет представлен как 1984-09-26. Но в PHP эта же самая дата будет представлена как 465004800. Как вы понимаете, это создаст проблемы при сохранении и извлечении дат из баз данных MySQL.
Преобразование дат PHP в даты MySQL
К счастью, есть простой способ конвертировать даты PHP в даты MySQL. На самом деле есть два пути. Первый способ — предварительно обработать дату PHP с помощью функции PHP. Упомянутая выше функция date() специально разработана для преобразования дат PHP в читаемые форматы. Все, что вам нужно сделать, это использовать функцию date() для соответствия формату MySQL. Итак, ваш код будет выглядеть так:
$date_for_mysql = date(‘Y-m-d H:i:s’, $date_from_php)
Затем вы можете вставить $date_for_mysql в свой запрос SQL.
В качестве альтернативы вы можете использовать функцию SQL для обработки даты. Эту функцию вы бы использовали непосредственно в своем запросе. Правильная функция называется FROM_UNIXTIME(). Таким образом, при использовании этого запроса ваш запрос может выглядеть примерно так:
$query = «UPDATE table SET date = FROM_UNIXTIME($date_from_php) WHERE row = column» ;
Помните: Вы должны использовать одну из этих функций или другую, но не обе.