What Is the Most Elegant Way to Store Timestamp with Nanosec in Postgresql

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

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)

Group by day from nanosecond timestamp

Basic conversion as instructed here:

• What kind of datestyle can this be?

Repeat the same expression in GROUP BY, or use a simple positional reference, like:

SELECT date_trunc('day', to_timestamp(transaction_timestamp / 1000000000.0))
     , count(*)
FROM   transaction
GROUP  BY 1;

Be aware that to_timestamp() assumes UTC time zone for the given epoch to produce a timestamp with time zone (timestamptz). The following date_trunc() then uses the timezone setting of your current session to determine where to truncate "days". You may want to define a certain time zone explicitly ...

Basics:

• Ignoring time zones altogether in Rails and PostgreSQL

Typically, it's best to work with a proper timestamptz to begin with. Unfortunately, Postgres timestamps only offer microsecond resolution. Since you need nanoseconds, your approach seems justified.

Storing DateTime in PostgreSQL without loosing precision

PostgreSQL's timestamps have a precision limit of microseconds.

If you need more, store the nanoseconds in a separate bigint attribute.

PostgreSQL : Why Time type allocates the same size as Timestamp?

There are 86,400,000,000 microseconds in a day. That's more than 2³², so the result can't be stored in 32 bits. The next best option is 64 bits, i.e. 8 bytes.

Compare that with the date type which covers 4713BC to 5874897AD, i.e. 5879611 years. That's around 2147483820 days, which is less than 2³² and so can be stored in a 32-bit integer. (In fact it's less than 2³¹, which may make various things slightly simpler.)

---

Related Topics