Time format research and discussion

[cbrake]

Doing a little research on how timestamps are stored:

https://github.com/simpleiot/simpleiot/blob/master/docs/adr/4-time.md

... and thinking through if we should make any changes -- especially in the timestamps stored in SQLite -- thinking a single value of ns since 1970 makes sense. Is there ever a time where having seconds separate is beneficial? It seems like splitting out seconds and ns just makes everything harder -- comparisons, math, etc.

cc @bminer

[bminer]

Here are my thoughts...

Background

Barring the use of an atomic clock, no RTC (and thus no timestamp) will have nanosecond accuracy (even if it has nanosecond precision). So, it only makes sense to compare nanosecond-precision timestamps for data samples coming from the same device; they aren't portable. When comparing samples between devices, millisecond-precision will be more than sufficient.

Proposal

Separate wall clock and monotonic time. Wall clock times would have millisecond precision while monotonic times would have nanosecond precision.

• MCU -- keep wall / monotonic time separated. See MCU time spec for more details.
• Go -- convert MCU monotonic time into some sort of "adjusted" time.Time value
• InfluxDB -- store wall and monotonic time in separate columns
• SQLite -- probably no need to store anything other than millisecond precision
• Browser -- When graphing nanosecond-precision timestamps, I think that the x-axis should be number of nanoseconds offset from a particular second in time. It seems that many (most?) graphing libraries won't support anything more precise than millisecond-precision anyway, so we need some sort of workaround that doesn't involve using the JS Date object.
• Serialization
  • Wall clock times -- encoded into int64 - number of milliseconds since UNIX epoch UTC
  • Monotonic clock times -- encoded into int64 - number of nanoseconds since device booted up
  • If needed, timestamps would be transferred as a tuple of (monotonic, wall) time.
  • Other options is to encode using protobuf's default int64 seconds since UNIX epoch UTC + int32 nanoseconds.

[cbrake]

@bminer thanks for the input -- still processing.

Do you know why so many formats Go, NTP, etc. separate seconds and nanoseconds into two variables?

What is the benefit of this over just using 64-bit nanoseconds?

I guess int64 seconds would practically never run out (Go time.Time).

Maybe 584 years is not enough for some people, but I can't really imagine software deployed today ever still running in 584 years ...

32 bits worth of seconds is only 136 years, so 64-bit nanoseconds would generally be better than 32-bit seconds + 32-bit nanoseconds (what NTP does).

SQLite -- is there any harm in storing nanosecond precision? Space would be a few more bytes per point.

I agree that for comparing timestamps for CRDT operations, MS is plenty, as times will not be synced between systems any more closely than that. However, since points also represent events in time, I hesitate to throw away the precision as it may be useful in rules or other algorithms.

InfluxDB offers the following suggestion:

https://docs.influxdata.com/influxdb/v2.4/write-data/best-practices/optimize-writes/#use-the-coarsest-time-precision-possible

Use the coarsest time precision possible

By default, InfluxDB writes data in nanosecond precision. However if your data isn’t collected in nanoseconds, there is no need to write at that precision. For better performance, use the coarsest precision possible for timestamps.

Specify timestamp precision when writing to InfluxDB.

[bminer]

Do you know why so many formats Go, NTP, etc. separate seconds and nanoseconds into two variables?

I think some of the rationale is historical in nature. Storing UNIX timestamps in seconds as an int32 was a common standard for a long time. For example, some file systems still use int32 to store UNIX timestamps in seconds.

But most importantly, I think that comparing nanosecond-precision timestamps is an uncommon use case because of clock drift.

SQLite -- is there any harm in storing nanosecond precision? Space would be a few more bytes per point.

Yeah, pretty much. But, since SQLite doesn't store the high-precision time series data anyway, I think it's okay to truncate to nearest ms.

However, since points also represent events in time, I hesitate to throw away the precision as it may be useful in rules or other algorithms.

I could be wrong, but I don't think we need high precision for syncing the CRDT. Upstream nodes could have clocks that differ by hundreds or even thousands of ms.