What is the most elegant way to store timestamp wi

问题:

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?

回答1:

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)

回答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;