How to organize the storage of objects in the database?

Question

Java

Hey! I'm trying to make my own organizer, in which the minimum unit of time is a segment of 15 minutes (so that all events are 30, 45, etc. minutes long), but the question confuses me: how to store and identify them in database? Several options came to mind that do not suit me:
- time in milliseconds (start time of a 15-minute segment)
- year, number of the day in the year, segment number on this day (96 segments per day, respectively)
- just the serial number of the segment , starting from a certain date
All these options seem to me somehow clubfoot. Is there a smarter solution to this problem?)

Answer 1

And why not select a number starting from 1, increasing by one with each step. And identify all events that occurred from the beginning of the time interval to the beginning of the next time interval, not including the very beginning of the next interval, with this identifier.
Thus, there are 96 such segments in a day, in a year 36500 - 4 * 365 = 36500 - 1460 = 35040 steps per year (+96 in a leap year). It turns out that with one global object - a sequence - which constantly grows by one with the help of a metronome set for 15 minutes, it is possible to serve the entire database with an authoritative single source of the current time interval identifier, without wasting time on calculations within a single procedure or query.
In my opinion, Postgres even has some very convenient levers for this, allowing you to set a sweep, global or narrower, which will provide access to such an object as a global variable with minimal resources.

Answer 2

Why not just have two fields for the segment: dateStart, dateFinish Field
type DATETIME or TIMESTAMP
That's more correct, all of a sudden you need to change the length of the segment tomorrow.

Answer 3

In the datetime base. The application monitors everything for 15 minutes.

Answer 4

maybe so, but haven't tried it: dateStart timestamp + itrvl interval
in PostgreSql there is also the DateRange type, you can then build indexes on it: https://www.postgresql.org/docs/9.3/static/rangety...