Timestamp with nanoseconds for sorting (insertion order)

We are thinking about storing timestamps for e.g. created_at with a nanoseconds precision e.g. 2019-03-06 09:21:19.942988 (current we store it with 3 digits less e.g. 2019-03-06 09:21:19.942) We mainly need this for sorting for very fast imports where multiple inserts can be at the same millisecond. So we wrote code (java) which makes sure that the last three digits are unique and monotonic increasing (given they are calculated on the same host/instance/vm).

My questions are:

  1. What are the side effects of such longer timestamps? We have an index on the created_at column which is used e.g. for range queries (start BETWEEN end).
    We now want put an order by created_at to make sure that values are sorted increasing (insertion order).

  2. Or should we just store this as an additional field just for sorting and leave the created_at as it (yyyy-MM-dd HH:mm:ss.SSS -> 2019-03-06 09:21:19.942 instead of 2019-03-06 09:21:19.942988)?

There is no data type timestamp. timestamp is stored as ISO-8601 standard string format. Once in this format it is string comparable. Also N1QL has convenient string functions https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/datefun.html.

The convenient functions uses milliseconds due to limit of number in JSON. int64 can only fit certain range any number 2**53 will stored as float64 and looses precision. https://golang.org/pkg/time/#Time.UnixNano

If you are only looking for sort store AS ISO-8601 format with fraction second contains upto 9 digits with leading 0 then reset of things in N1QL should work seamlessly. When use N1QL date functions it should truncate to milliseconds(i have not tested but you can do quick test).
Example: “2019-03-06T06:18:51.000000491-08:00”

1 Like