datetime, timestamp, etc. против unixtime в поле int

В web-приложениях, использующих СУБД MySQL, для хранения даты и/или времени можно:

  1. использовать специальные календарные типы данных(datetime, date, time, timestamp, year)
  2. завести поле с типом INT и вставлять дату/время в UNIXTIME
  3. ну и ещё для кучи — в поле текстового типа хранить дату с своём формате(но это как то айс))

Опишем плюсы и минусы первого варианта, после анализа которых и будут видны минусы использования второго варианта.

Плюсы первого варианта:

  1. В базе данные даты хранятся компактно. В виде чисел. Формат вида ГГГГ-ММ-ДД ЧЧ:ММ:СС создаётся на лету при запросе.
  2. При занесении данных MySQL пытается разными способами интепретировать полученную дату(т.е. формат даты может быть достаточно вольным, однако надо придерживаться некоторых правил), при выборке MySQL автоматически преобразовывает дату в определённую форму, что может быть удобно.
  3. Есть куча функций для работы с календарными типами данных MySQL.  Это: 1) снимается нагрузку с web-приложения; 2) Упрощает разработку web-приложения.
  4. с timestamp работать очень удобно. Если не указать его явно, то в поле будет автоматически вставляться текущая дата при вставке и обновлениии записи.
  5. возможность задать дату в более широком диапазоне(в типе DATETIME, DATE), чем если бы добавляли UNIXTIME в INT поле.
  6. при использовани поля с типом TIMESTAMP дата возращается клиенту с учётом его часового пояса, при занесении даты в поле такого типа дата так же корректируется на UTC/GMT+0 относительно пояса клиента. Достаточно доходчиво об этом написано здесь http://habrahabr.ru/blogs/mysql/69983/.

Минусы:

  1. MySQL не проверяет дату на корректность. Точнее СУБД проверяет, к примеру, чтобы месяц был от 1 до 12, день месяца от 1 до 31. Но можно занести некорректную дату, к примеру 2010-02-31. Ведь в феврале нет 31-го дня. Но это уже вопрос к web-приложению, поставляющему данные.

Рассмотрим второй вариант.

Плюсы:

  1. Переносимость между различными СУБД. Целочисленный тип есть во всех СУБД, в отличии от календарного. Или же если он(календарный тип) и есть в другой СУБД, то может быть несовместимость. Хотя если нормально экспортировать базу с календарными типами, выбрав опцию ‘совместимость дампа с такой то СУБД’, то проблем быть не должно.

Минусы(по сути отсутствие плюсов от первого варианта — это и есть большой минус второго):

  1. ручная обработка данных, средствами web-приложения.
  2. нагрузка по обработке данных переходит на web-приложение.
  3. чем больше код(код для обработки данных), тем больше ошибок, тем сложнее разрабатывать и поддерживать продукт.
  4. хранение даты в малом диапазоне.
  5. работу с часовыми поясами придётся возложить на web-приложение.

Про третий вариант я молчу. Это просто не дело. Ведь должна же быть среди разработчиков хоть какая то договорённость.

А хранить дату в своём формате это плохо. С датой в таком виде(в текстовом поле) многое придётся делать самому: преобразование, сортировка и т.д. Функции для работы с датами не будут работать с этими данными.

По логике вещей календарные типы придуманы не зря и использовать нужно именно их. Не надо забивать гвозди лопатой.

Рубрики: MySQL

Теги: date, datetime, int, MySQL, time, timestamp, типы данных

01.03.2010 13:04

Time или time_sec отображаются как sth, как unixtime, но MySQL содержит DATETIME — MySQL

mircsicz

1

Привет всем,

Я только что создал новую панель мониторинга, но не могу отобразить данные.

Я импортировал из CSV вот так:

 Время (дата), 495 #2 3P L1-L3 E[кВтч] (числовое), 495 #2 3P L1-L3 P[кВт] (числовое), 495 #2 3P L1 P[Вт] (числовое), 495 #2 3P L2 P[W] (числовое), 495 #2 3P L3 P[W] (цифровой)
2019-07-08 15:25:00,0,64,10,95,4139,20,3483,29,3324,24
2019-07-08 15:30:00,1.
 
 30,9.94,3683.09,3217.77,3037.69

Я запрашиваю БД так:

 ВЫБЕРИТЕ
   Время КАК время,
    `495 #2 3P L1-L3 P[кВт]` КАК 3OG_BT2_кВт
ОТ `WSE_KA-AIK_Wurth 3. OG_WAGO`
ЗАКАЗАТЬ ПО ВРЕМЕНИ ASC

На приборной панели я вижу только «Нет точек данных», если я смотрю в Исследовать, данные выглядят так:

1562599500000 10,95
1562599800000 9.94
1562600100000 10,37

Я не понимаю, почему метка времени меняется на это, надеюсь, у кого-то из вас есть подсказка для меня…

мое имя, а не

2

1562599500000 — это временная метка Unix, которая выражается в количестве секунд с 1 января 1970 года.

Внутри Grafana использует этот формат для времени (как и многие программы баз данных), который занимает меньше места и позволяет сравнивать время. возможно в одном тесте, в отличие от удобочитаемых форм.

Теперь перейдем к тому, что я считаю вашей настоящей проблемой, потому что я сам столкнулся с ней. Скорее всего, ваши данные не отображаются, потому что ваши даты указаны по местному времени, а Grafana хочет, чтобы они были указаны по времени UTC, а диапазон времени по умолчанию для графика — последние 6 часов. Для меня, находящегося в Калифорнии, смещение составляет 7 часов, чуть больше того, что он показывал. Переключение «Сегодня» на диапазон графика показало мои данные, сдвинутые на 7 часов.

Если это ваша проблема, у вас есть несколько вариантов. Один из них — преобразовать ваши даты в формат UTC, прежде чем помещать их в базу данных.

Другой — преобразовать их в формат UTC в запросе Grafana.
SELECT CONVERT_TZ( NOW(), @@session.time_zone, ‘+00:00’ )

NOW() выше будет изменено на ваше поле даты в вашей базе данных.

Примечание. Я только что заметил, что в метке времени есть несколько лишних нулей.
Обычная временная метка Unix для даты примерно сейчас будет состоять из 10 цифр, например:
1562599500 (кстати, это понедельник, 8 июля, 08:25:00 по тихоокеанскому времени 2019 года).
Дополнительный 000 должен использоваться, если он хочет перейти в миллисекунды.

Мирчич

3

Спасибо за ответ, но как избежать того, чтобы графана добавляла миллисекунды к отметке времени.

My Explore выглядит так:

vkUZnM.jpg922×751

Данные в MySQL выглядят так:

Pl5RmH.jpg786×93

mynameitsnot

4

Вы не хотите избавляться от миллисекунд, это формат, в котором они нужны Grafana.
Обратите внимание, что миллисекунды — это только последние 000 в числе, остальное — дата и время в формате времени Unix, как объяснил выше.

Ваш оператор SQL неверен для данного времени. Вот пример того, что я получаю, когда просто захожу в проводник. Обратите внимание на использование функции UNIX_TIMESTAMP().

SQLQuery.PNG692×736 26,1 КБ

И когда я смотрю на то, что они создали в приборной панели, это выглядит так.

Обратите внимание, что они переключились на использование «время» вместо time_sec, что является «предпочтительным в будущем» способом сделать это.

Мирчич

5

Это настолько закрыто, насколько я мог добраться до сгенерированного SQL

SELECT
UNIX_TIMESTAMP(Time) DIV 600 * 600 AS «время»,
495 #2 3P L1-L3 P[кВт] AS метрическая
FROM WSE_KA-AIK_Wurth 3. OG_WAGO
WHERE $__timeFilter(Time)
GROUP BY 1

5 DIV60 ORDER BY UNIX)_TIMESTAMP 60

Но все же я не получаю график, я точно вижу данные, но даже если данные находятся внутри $__timeFilter(), я не получаю свой график

mynameitsnot

6

Почему вы смешиваете два формата?
А почему вы делите числа?
Думаю, потому что у вас ничего не работает.

Насколько я могу судить, это:

Выберите UNIX_TIMESTAMP(Time) time_sec, …
ИЛИ
Выберите время как «время», …

Если эти форматы этого не делают, то я не знаю, что продолжается.

Извините. Обратите внимание, что номера времени, такие как:
1562957244000

, верны.
Обратите внимание, что это преобразователь временных меток Unix:
https://www. unixtimestamp.com/index.php

mircsicz

7

Где я смешиваю два формата? Я использую time, а не time_sec.

И использование DIV только потому, что это было предложено компоновщиком, но без DIV это работает почти так же, просто дайте мне больше значений. Но в целом да: ничего не работает.

И до того, как я создал этот пост, я попробовал преобразование unixtime и получил это:

Что мне показалось чертовски неправильным

mynameitsnot

8

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

Затем я посмотрел на ваш запрос по сравнению с тем, что я видел, и ушел от этой касательной, забыв, что на самом деле вы делаете хорошие отчеты о временных отметках в Проводнике.

Что касается «смешивания»

UNIX_TIME(var) идет с как time_sec
и var идет с как «время»

У вас было:
UNIX_TIMESTAMP(Time) как время

В любом случае . Как я пытался объяснить (наверное, плохо), они используют «модифицированную» метку времени Unix. Как в UnixTimeStamp с добавлением миллисекунд в конце.

Чтобы получить правильное время для конвертера Unix, удалите последние 3 нуля:
Итак, для вашей метки времени выше:
1562957244, что означает:

Просто будьте ясны, вот скриншот некоторых моих данных, обратите внимание на даты.

MyData.PNG1259×794 50,5 КБ

mircsicz

9

Нет причин извиняться, я час или два, как я нахожусь в CET…

Но да, моя проблема не во временном разрыве, я просто не вижу графика из данных в моей БД

мое имя не

10

Вот еще пример. Я заметил пару вещей на вашем графике.
Во-первых, когда у него нет данных, он будет говорить «Нет данных» в середине графика.
В вашем этого нет, поэтому он улавливает «что-то».
Далее я не вижу легенду серии, которая должна быть там.

Итак, я пробовал разные способы, чтобы получить одинаковые результаты, и вот что у меня получилось.

Explore.PNG1257×698 35,6 КБ

Я получил это так: столбец № 2 — VARCHAR(20).
Возможно, проблема связана с типом данных, который вы использовали для своих значений.

Вот я переключаюсь на столбец № 1, который в базе данных имеет значение FLOAT.

Explore2.PNG1256×704 36,6 КБ

mynameitsnot

11

Одна моя информация. Сегодня я что-то менял и случайно ввел данные не в тот год. И на графике показано это сообщение для этой проблемы:

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

Отметка времени в MySQL

Еще 10 обсуждений, которые могут вас заинтересовать

1. Программирование

Во-первых, я хочу поблагодарить Нео (LOL) за этот пост из 2018 года, Node. js и mysql — ER_ACCESS_DENIED_ERROR Я не мог заставить работать модуль mysql Node-RED и искал в Google, пока все мои ссылки не стали фиолетовыми! Я продолжал получать ER_ACCESS_DENIED_ERROR с правильными учетными данными. В сети ничего не было… (0 ответов)

Обсуждение начато: Neo

2. Программирование оболочки и создание сценариев

Итак, у меня есть файл журнала, и каждая строка в этом файле журнала начинается с метки времени: ПН ДД ЧЧ:ММ:СС 15 сен 07:30:01 Мне нужно найти все строки между отметкой времени последнего часа и отметкой текущего времени. Затем эти строки будут перемещены в файл tmp, из которого я буду искать определенные строки. … (1 ответ)

Обсуждение начато: nms

3. Программирование оболочки и создание сценариев

Здравствуйте, Я работаю на AIX. Мне нужно преобразовать временную метку Unix в обычную временную метку. Ниже находится файл . Отметке времени Unix всегда будет предшествовать EFFECTIVE_TIME в качестве первого поля, как показано, и в файле может быть несколько EFFECTIVE_TIME: 3. txt Содержание… (6 ответов)

Обсуждение начато: rahul2662

4. Программирование оболочки и создание сценариев

Привет друзья! У меня есть следующий файл журнала. В настоящее время время в Индии 31.07.2014 12:33:34, и у меня есть следующее содержимое в файле журнала. Я хочу отображать только те записи, которые содержат строку «Исключение» за последние 3 часа. В этом случае это будет только последняя строка Я могу получить… (12 ответов)

Обсуждение начато: srkmish

5. Программирование оболочки и создание сценариев

Мне нужно иметь возможность идентифицировать файлы с отметками времени, превышающими заданную отметку времени. Я использую следующее решение, хотя оно, похоже, сравнивает файлы с точностью до «секунд», и мне это нужно в миллисекундах. Когда я тестировал свое решение, оно пропустило файлы с отметками времени… (3 ответа)

Обсуждение начато: nkm0brm

6.

UNIX для чайников Вопросы и ответы

Привет всем, Я новичок в программировании unix. Я пытаюсь выполнить требование, и требование выглядит так….. У меня есть тестовая папка. Который отслеживает файлы журналов. Через определенное время файл журнала перезаписывается другим файлом (случайным образом, поскольку временной интервал не является периодическим). Мне нужно сохранить… (2 ответа)

Обсуждение начато: mailsara

7. Программирование оболочки и создание сценариев

Привет, У меня есть файл в следующем формате 1999-АПР-8 17:31:06 1500 3 45 1999-АПР-8 17:31:15 1500 3 45 1999-АПР-8 17:31:25 1500 3 45 1999-АПР-8 17:31:30 1500 3 45 1999-АПР-8 17:31:55 1500 3 45 1999-APR-8 17:32:06 1500 3 … (1 ответ)

Обсуждение начато: vaibhavkorde

8. Программирование оболочки и создание сценариев

Привет всем, На самом деле моя цель — вызвать сценарий оболочки, когда когда-либо происходит попадание в таблицу mysql, состоящую из 3 значений.

No related posts.