What is the most elegant way to store timestamp with nanosec in postgresql?

Question

Unfortunately the postgresql timestamp type only can store timestamps with microsec precision but i need the nanosec also.

PostgreSQL - 8.5. Date/Time Types:

Timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6 for the timestamp and interval types.

And i need 7:

0,000 000 001 [ billionth ] nanosecond [ ns ]

0,000 001 [ millionth ] microsecond [ µs ]

0,001 [ thousandth ] millisecond [ ms ]

0.01 [ hundredth ] centisecond [ cs ]

1.0 second [ s ]

Is there any elegant and efficient way to handle this problem?

EDIT: Maybe store the timestamp in bigint?

I recommend to write your own C function to get the current nanoseconds and store it in custom datatype. Anyway I think this is a duplicate: https://stackoverflow.com/questions/41514225/postgresql-support-for-timestamps-to-nanosecond-resolution — JustMe, Oct 17 '17 at 13:07
Which OS / device provides the nano seconds? (In practice, with other digits than 0 at the end.) — jarlh, Oct 17 '17 at 15:09
@jarlh: MSSQL provides datetime2 type with nanosec precision. — Csuszmusz, Oct 18 '17 at 07:58
@Csuszmusz, the data type can store that precision, but who provides the actual values? AFAIK Windows can't. — jarlh, Oct 18 '17 at 08:07
@jarlh According to this it can: https://stackoverflow.com/questions/275004/timer-function-to-provide-time-in-nano-seconds-using-c — Csuszmusz, Oct 18 '17 at 11:31
@Csuszmusz do you want to use the nanosecond field as part of a key (identifying the row), or to determine the ordering of the rows? (or: is it only an attribute) — joop, Oct 19 '17 at 09:44
@joop Use nanosec as key sounds absurd to me. :) Its the second option tho, i would like to order rows by timestamps (including the nanosec) — Csuszmusz, Oct 19 '17 at 12:29
Maintaining nanosecond timing seems rather absurd to me ... how many observations do you expect to be in the same usec interval? The solution I was aiming at adding a serial to the "key" := {timestamp, bigserial} if the timestamps are entered in a non-decreasing sequence, the order will at least be preserved. (you might have to reset the serial periodically to avoid fold-over) — joop, Oct 19 '17 at 14:47
Does this answer your question? [PostgreSQL support for timestamps to nanosecond resolution](https://stackoverflow.com/questions/41514225/postgresql-support-for-timestamps-to-nanosecond-resolution) — sumek, Feb 07 '23 at 10:14

score 5 · Accepted Answer · answered Oct 18 '17 at 12:26

Use numeric as a base type of nano timestamps. The function converts a numeric value to its textual timestamp representation:

create or replace function nanotimestamp_as_text(numeric)
returns text language sql immutable as $$
    select concat(to_timestamp(trunc($1))::timestamp::text, ltrim(($1- trunc($1))::text, '0'))
$$;

You can also easily convert numeric values to regular timestamps in cases where the super precision is not necessary, example:

with my_data(nano_timestamp) as (
    select 1508327235.388551234::numeric
)

select 
    to_timestamp(nano_timestamp)::timestamp,
    nanotimestamp_as_text(nano_timestamp)
from my_data;

        to_timestamp        |     nanotimestamp_as_text     
----------------------------+-------------------------------
 2017-10-18 13:47:15.388551 | 2017-10-18 13:47:15.388551234
(1 row)

score 3 · Answer 2 · answered Sep 20 '19 at 09:38

As others have pointed out, Postgres doesn't provide such type out of the box. However, it's relatively simple to create an extension that supports nanosecond resolution due to the open-source nature of Postgres. I faced similar issues a while ago and created this timestamp9 extension for Postgres.

It internally stores the timestamp as a bigint and defines it as the number of nanoseconds since the UNIX epoch. It provides some convenience functions around it that make it easy to view and manipulate the timestamps. If you can live with the limited time range that these timestamps can have, between the year 1970 and the year 2262, then this is a good solution.

Klin's answer is a perfect solution if you don't want to have an extension installed on your system. However, it cooperates less nicely with existing timestamp/interval types and it's also less efficient, because it uses a numeric type as storage. Having an extension gives you greater flexibility.

Disclaimer: I'm the author of the extension

a curiosity question ? will your approach work for the following : `guid UUID NOT NULL DEFAULT gen_random_uuid() , id bigint UNIQUE NOT NULL DEFAULT cast (to_char(current_timestamp, 'YYMMDDHH12MISS-HERE-YOUR-TIMESTAMP-DATA-TYPE') as bigint)` , so that postgres would create automatically on huge select inserts the ids which are nanoseconds away from each other, unique AND however kind of human readable ? OR Should I ask a separate stackoverflow question for this one ;o) — Yordan Georgiev, Sep 13 '20 at 09:48
current_timestamp still has microsecond precision, and is a transaction timestamp, so it'll be the same for all rows. I don't think there's currently a way to generate a nanosecond precision timestamp in Postgres itself (although such a function could also be added in an extension). What we generally do is generate the nanosecond timestamp on the client-side when preparing the INSERT/COPY — fvannee, Sep 14 '20 at 10:39

score 2 · Answer 3 · answered Nov 05 '17 at 16:21

2

Bigint will work. If you are going to save all the timestamps with nanosecond precision, I would recommend to define a new cast:

CREATE CAST (timestamp AS bigint)
WITHOUT FUNCTION;

answered Nov 05 '17 at 16:21

3

Can you explain why [`WITHOUT FUNCTION`](https://www.postgresql.org/docs/10/static/sql-createcast.html) works here -- are there any postgres docs that guarantee that you can just do this binary coercion? – nh2 Jul 11 '18 at 01:29

What is the most elegant way to store timestamp with nanosec in postgresql?

3 Answers3

Linked

Related