CODEWITHSUNDEEP
mysqldatetimetimestampprecisionarbitrary-precision
Jimmix
Jimmix

Reputation: 6536

MySQL 8 timestamp precision in nanoseconds

Is it possible to store in MySQL DATETIME or other equivalent datatype with a precision of nanoseconds?

As per fractional-seconds documentation it looks to me not possible (max 6 digit sub second precision = 1 usec). To quote the manual:

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision.

But maybe there is another datatype or function that can solve this problem?

I would like to be able to calculate difference between time ranges with precision of nanosecond and would like to avoid storing them in INT and doing my own math.

Perhaps if MySQL does not support this precision at all there is a plugin/extension I could install to get a custom DATETIME precision I could set to nanoseconds (9 digits after second comma)?

If this is not possible is there any RDBMS that supports nanosecond precision? or maybe at least NoSQL (with a function to calculate time diff in nsec precision)?

Upvotes: 2

Views: 3494

Answers (1)

Brad
Brad

Reputation: 950

I'd recommend using timestamps but store them as a decimal.

DECIMAL

I started using this as my answer on the Y2038 bug.

This is the solution I landed on as you get a lot of control and you can still easily use it as an autogenerated timestamp.

This is what I used for 100th of a second:

`created` decimal(18,2) NOT NULL DEFAULT (unix_timestamp(now(2))) COMMENT 'unix timestamp'

I was doing this because I was limiting it to a 8bytes. You will need to increase it for nanoseconds as 18 digits with 9 decimal places will only give a cover 31 years from the epoch.

For your case:

DECIMAL(20,9) 2 digits more will give you 3 thousand years which should be enough.

You could maybe use 19 digits but it will still require the same storage anyway so why not 20.

  • 9 digits for the nanoseconds = 4bytes.
  • 20-9 = 11 digits for seconds = 4bytes for the first 9 digits.
  • 11-9 = 2 digits left over = 1byte (same for 1 digit leftover).

You will lose the auto-generated timestamp precision I had as the now() function can only support 6 digits of precision.

If that is a requirement your app is going to have to manually insert the created timestamp.

Upvotes: 0

Related Questions