Как лучше сохранять дату и время?

Question

[skoch244]

Как лучше сохранять дату и время в базе где много таблиц и в каждой она есть, небольшой пример: есть несколько источников температур (T) и давлений (P), мы собираем с них информацию в разные таблицы, а дату и время этих показаний T и P можем сохранять в те же таблицы или создать общий справочник, в котором будет записываться дата и время, а уже таблицы с показаниями будут ссылаться на них, то есть создать справочник с датой и временем.

база со справочником

spoiler

Какие я вижу плюсы: это понятное присоединение таблиц (join), нормализация. В дальнейшем хотелось бы отсечь старые данные секционированием, но не совсем понимаю как это сделать с данной архитектурой.

Answer 1

[Владимир Коротенко]

Есть встроенный тип времени и даты и даже не один. Используйте его, а не костыли.

INT -231 (-2,147,483,648) to 231-1 (2,147,483,647) 4 Bytes
DATETIME 8 bytes
7 * 4 = 28 это одна ваша запись оверхед в 20 байтов.
Плюс по вашим данным нельзя искать используя встроенные функции и операторы, в общем реальная дичь.

Кстати что натолкнуло на такую конструкцию?

Comments

[skoch244]

Такой принцип используют в хранилищах данных (DWH), один справочник должен быть с датой и временем, там даже лучше чтобы гранулярность была высокая (детализация как в 1 картинке).
Подумал что может будет лучше реализовать это в операционной таблице.
К примеру у меня 20 таблиц Module, в каждой есть DATETIME 8 bytes, в итоге 160 bytes
Или справочник, даже с высокой гранулярностью 7 полей INT по 4 bytes, и итоге 28 bytes, плюс хороший первичный ключ у Module.

[Владимир Коротенко]

PoolModules
id - первичный ключ
when - когда произошел опрос

ModuleDatas
id - первичный ключ
poolID - внешний ключ на PoolModules
moduleID - внешний ключ на справочник модулей
T - ваши данные
P - ваши данные

[skoch244]

Распишите если не сложно

[FanatPHP]

В итоге 160 bytes

показания по каждому модулю есть за каждую секунду?
или они в принципе синхронные, всегда в одно и то же время для всех модулей?

[FanatPHP]

зачем нужна гранулярность?
выборки делаются по определенной секунде за весь период наблюдений?

[skoch244]

Всегда в одно и тоже время, пример 10.10.2021 22:30:31, 10.10.2021 22:30:32, 10.10.2021 22:30:33

[skoch244]

Это еще одна причина создания справочника

[skoch244]

FanatPHP, насчет гранулярности задумался, возможно Вы правы, я подумаю над этим

[Владимир Коротенко]

skoch244,

Всегда в одно и тоже время, пример 10.10.2021 22:30:31, 10.10.2021 22:30:32, 10.10.2021 22:30:33

Это другая табличка планировщика. Именно по ней запускается запрос.

Answer 2

[Руслан .]

Если таблицы будут большими по числу записей, то любое связывание это уже минус, т.к. Join это по сути вложенный цикл и даже если во второй таблице есть индекс все равно это дороже чем один раз пройтись и отобрать записи по одной таблице, где есть поле дата.

С точки зрения размера базы, с одной таблицей будет компактнее.

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

Comments

[skoch244]

С секционированием придется подумать, спасибо

Answer 3

[FanatPHP]

вот я тоже не понял, в чем смысл этого "справочника"

А справочник для цифр не хотите ввести? Ну там к примеру датчик показывает 200 миллизюзиков
вы пишете 200 не в таблицу с показаниями, а берете её из справочника показаний, а в таблице с показаниями делается ссылка на это значение.
А можно ещё сделать справочник цифр от 0 до 9, и присоединить его как многие ко многим. Сделать промежуточную таблицу, в которую записать ссылки на цифры 2, 0 и 0. И получить 200 через "понятное присоединение таблиц (join)"!

Comments

[skoch244]

Насчет "давайте все цифры складывать в справочник", если у Вас есть такая необходимость не вижу причин не делать этого) Во всем должен быть смысл, вот смотрите, запрос:
SELECT *
FROM DataTime T
JOIN Module_1 M1
ON T.ID = M1.IDtd
JOIN Module_2 M2
ON T.ID = M2.IDtd

Или

SELECT *
FROM Module_1 M1
FULL JOIN Module_2 M2
ON M1.Data = M2.Data

Просто мне нравится первый вариант, ну тут кому как. P.S. Извиняюсь за *

[FanatPHP]

Во всем должен быть смысл

Просто мне нравится

Вы определитесь сначала, вы про смысл или про нравится.
А потом уже задавайте свой вопрос

[skoch244]

FanatPHP, буду иметь ввиду, спасибо

[RabraBabr]

А справочник для цифр не хотите ввести?

А разве ASCII таблица не оно?

[FanatPHP]

RabraBabr, ASCII таблица для лохов! Настоящие пацаны делают таблицу в БД!

Answer 4

[Akina]

Дата в описанном случае - это не отдельная сущность-свойство сущности Измерение, а атрибут экземпляра этой сущности.

Соответственно "справочник дат" - дурь, не имеющая права на существование.

Для хранения дат в любой СУБД есть соответствующие встроенные типы (их, как правило, не один). Хранение значения даты "кусками" - точно такая же не имеющая право на существование дурь. До тех пор пока выделяемые в отдельные поля компоненты этой даты не являются самостоятельной сущностью. Но в этом случае вычисляемое поле скорее всего будет более разумным решением.

Answer 5

[Vitsliputsli]

Вариант с таблицей дат, вы вероятно подсмотрели где-то, где в одной таблице хранятся агрегаты разной гранулярности. Только в этом случае такие манипуляции имеют смысл. Но хорошим я бы такое решение все равно не назвал бы. Сделать несколько таблиц агрегатов выгоднее, чем все свалить в кучу и потом терять в чтении и записи, на манипуляции с дополнительной таблицей. Или еще лучше, запихнуть все в колоночное хранилище, которое само будет управлять агрегатами.
Но вы ведь вообще не агрегаты собираете, а просто данные, так что все это ни к чему.