4 ms·
> I would avoid using Unix timestamps or anything that is too implementation dependent (internal sequence numbers, etc.) if you are going to build it yourself.
by ripley12 7y ago
> I would avoid using Unix timestamps or anything that is too implementation dependent (internal sequence numbers, etc.) if you are going to build it yourself.
I generally agree, but this might depend on the use case. Timestamps (as they are provided by the DB or OS) may not be granular enough if you need to record the exact order of changes in a system with a lot of near-simultaneous writes.
I ended up storing both sequence numbers and timestamps in one SQL Server implementation for this reason.
- sixdimensional 7y agoInterestingly, sequence numbers are good for the use case of immutable logs and change data capture. But that is a different use case than temporal tracking. In SQL Server, CDC uses log sequence numbers (LSN) whereas temporal features use datetime2. I guess I didn't mean to say that sequence numbers shouldn't be used at all, tables that are bitemporal still have keys and identity values, just to use real dates and preferably GMT for the from/to dates for consistency. Sequence numbers are especially good for audit log forensics so long as the logs are immutable or protected. Gaps, etc. help with detection of deletions, tampering, etc. I agree with your comment about use case. Good point!