What is the best way to save the date and time?

Question

Database design

How best to save the date and time in a database where there are many tables and each has it, a small example: there are several sources of temperatures (T) and pressures (P), we collect information from them in different tables, and the date and time of these readings are T and P can be saved in the same tables or create a general directory in which the date and time will be recorded, and tables with readings will already refer to them, that is, create a directory with date and time.

base with directory

spoiler

What I see pluses: this is a clear connection of tables (join), normalization. In the future, I would like to cut off the old data by partitioning, but I don’t quite understand how to do this with this architecture.

Answer 1

There is a built-in type of time and date, and not even one. Use it, not crutches.
INT -231 (-2,147,483,648) to 231-1 (2,147,483,647) 4 Bytes
DATETIME 8 bytes
7 * 4 = 28 is one of your 20 bytes overhead records.
Plus, according to your data, you can’t search using built-in functions and operators, in general, real game.
By the way, what prompted such a design?

Answer 2

If the tables are large in terms of the number of records, then any linking is already a minus, because Join is essentially a nested loop, and even if the second table has an index, it's still more expensive than going through and selecting records from one table once, where there is a date field.
In terms of database size, one table will be more compact.
If partitioning is required, then the field for the partitioning function must be part. partitioned table.

Answer 3

So I also did not understand what is the meaning of this "reference book"
Do you want to enter a reference book for numbers? Well, there, for example, the sensor shows 200 millijuices,
you write 200 not in the table with indications, but take it from the reference book of indications, and in the table with indications a reference is made to this value.
And you can also make a directory of numbers from 0 to 9, and attach it as many to many. Make an intermediate table in which to write references to the numbers 2, 0 and 0. And get 200 through "clear join tables (join)"!

Answer 4

The date in the described case is not a separate entity-property of the Dimension entity, but an attribute of an instance of this entity.
Accordingly, the "date book" is nonsense that has no right to exist.
To store dates in any DBMS, there are corresponding built-in types (there are usually more than one of them). Storing the date value in "chunks" is exactly the same crap that has no right to exist. As long as the components of this date allocated in separate fields are not an independent entity. But in this case, a calculated field is likely to be a more reasonable solution.

Answer 5

The option with the date table, you probably peeped somewhere, where aggregates of different granularity are stored in one table. Only in this case such manipulations make sense. But I wouldn't call it a good solution anyway. Making several tables of aggregates is more profitable than dumping everything in a heap and then losing it in reading and writing, for manipulating an additional table. Or better yet, push everything into a column store that will manage the aggregates itself.
But you are not collecting aggregates at all, but simply data, so all this is useless.