Support for nanoseconds in timestamps

Feedback
1

Describe the problem you’re experiencing?
We would like to be able to store timestamps with nanoseconds. The finance industry, specifically US Equities, has been required by regulation to capture and report timestamps in nanoseconds for a while now. See this: Regulatory Notice 20-41 | FINRA.org. Singlestore doesn’t support TIMESTAMP(9), which causes us to store data using multiple fields, a TIMESTAMP(6) for human readability, and another bigint field which contains nanoseconds since epoch. We use columnstore tables that are sorted by this nanosecond field, but we often query using the TIMESTAMP(6) column because it is easier to interact with. This is not only wasteful in terms of space but not great for performance.

We have tried using a calculated field for this purpose (a calculated timestamp field based on the nanoseconds-since-epoch field), but were disappointed to find that calculated fields cannot have a TIMESTAMP datatype.

What is your ideal solution? What are you looking for?
Be able to use TIMESTAMP(9) or at least a calculated field of type TIMESTAMP(6)

What version(s) of MemSQL or related tools is this affecting?
All

2

Hi @prerak, thanks for your well-thought-out feature request. We are already tracking this one. I added your details to it. I assume you are also okay with DATETIME(9) even if we don’t do TIMESTAMP(9), is that right? Timestamp runs out in 2038 right now, so it is not recommended for new development. Of course, a TIMESTAMP(9) type could cover a longer span, perhaps.

3

Thank you. Yes, datetime(9) is equally good. In our systems, we use a 64-bit integer to represent nanos since epoch, which will work for a couple of hundred years more.

4

That’s good to know. What do you use for your epoch?

5

Just the unix epoch, it’s much easier to interoperate or exchange data with other systems / languages that way (divide by a thousand, million, or billion and can turn that into a timestamp usable by most any language - Python, JavaScript, or even shell scripts)

6

Thanks, @prerak. I can see the benefit of that.

7

Support for nanosecond-precision timestamps can be done with a Wasm extension. We’ve made an open-source sample, written in Rust, available here:

It works by storing the number of nanoseconds since the epoch in a 64-bit integer (bigint). The extension has functions to convert from string to that format and from that format back to string.

For sorting and comparisons, a nice thing about this approach is that you can just do those on the raw (bigint) data.