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开发者_StackOverflow.
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
- id
- name
- prev_event
- next_event
Is this the most efficient way to model relative events?
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
I don't think you need to link to previous and next event, why not just use a timestamp and order by the timestamp?
If you can have multiple, simultaneous event timelines, then you would use some kind of identifier to identify the timeline (int, guid, whatever) and key that in witht the timestamp. No id is even necessary unless you need to refer to it by an single number.
Something like this:
Event
- TimeLineID (key)
- datetime (key)
- Name
精彩评论