在PostgreSQL中使用nanosec存储时间戳的最优雅方法是什么？ [英] What is the most elegant way to store timestamp with nanosec in postgresql?

问题描述

不幸的是，postgresql时间戳类型只能存储微秒精度的时间戳，但是我也需要纳秒。

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

PostgreSQL-8.5。日期/时间类型：

时间戳记和时间间隔接受可选的精度值p，该精度值指定保留在其中的小数位数秒字段。默认情况下，对精度没有明确的限制。 p的时间戳和间隔类型的允许范围是0到6。

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.

我需要7：

0,000 000 001 [十亿分之一秒]纳秒[ns]

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

0.0001十亿分之一秒[微秒] ]

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

0,001 [千分之一秒]毫秒[ms]

0,001 [ thousandth ] millisecond [ ms ]

0.01 [百分之百]厘秒[cs]

0.01 [ hundredth ] centisecond [ cs ]

1.0秒[s]

是否有任何优雅而有效的方式来处理此问题

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

编辑：
可能将时间戳存储在bigint中？

Maybe store the timestamp in bigint?

推荐答案

使用数字作为nano时间戳的基本类型。该函数将数字值转换为其文本时间戳记表示形式：

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)

这篇关于在PostgreSQL中使用nanosec存储时间戳的最优雅方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！