timedatabase-designdata-modelingrelative-date

How do I model "relative" time in a database?


Clarification: I am not trying to calculate friendly times (e.g., "8 seconds ago") by using a timestamp and the current time.

I need to create a timeline of events in my data model, but where these events are only relative to each other. For example, I have events A, B, and C. They happen in order, so it may be that B occurs 20 seconds after A, and that C occurs 20 years after B.

I don't care about the unit of time. For my purpose, there is no time, just relativity.

I intend to model this like a linked list, where each event is a node:

Event

Is this the most efficient way to model relative events?


Solution

  • All time recorded by computers is relative time, nominally it is relative to an epoch as an offset in milliseconds. Normally this epoch is an offset from 1970/01/01 as is the case with Unix.

    If you store normal everyday timestamp values, you already have relative time between events if they are all sequential, you just need to subtract them to get intervals which are what you are calling relative times but they are actually intervals.

    You can use whatever resolution you need to use, milliseconds is what most things use, if you are sampling things at sub-millisecond resolution, you would use nanoseconds