在PostgreSQL中存储具有纳秒级别时间戳的最优雅方式是什么？

Question

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

PostgreSQL - 8.5. 日期/时间类型:

Timestamp和interval接受一个可选的精度值p，该值指定保留在秒字段中的小数位数。默认情况下，没有显式的精度界限。允许的p范围为0到6，适用于timestamp和interval类型。

但我需要7个:

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

0.000 001 [百万分之一] 微秒[µs]

0.001 [千分之一] 毫秒[ms]

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

1.0 秒[s]

有没有一种优雅而高效的方法来解决这个问题呢？

编辑： 也许将时间戳存储在bigint中？

- Csuszmusz

---

你是需要这些来确认身份，还是只是用于下单？ - joop

谁将为数据库提供纳秒级时间戳？ - jarlh

我建议编写自己的C函数来获取当前纳秒并将其存储在自定义数据类型中。无论如何，我认为这是一个重复的问题：https://dev59.com/4lgR5IYBdhLWcg3wRbk1 - JustMe

哪个操作系统/设备提供纳秒级的精度？（实际应用中，除了末尾为0的其他数字。） - jarlh

@Csuszmusz，这种数据类型可以存储那种精度，但是提供实际值的是谁呢？据我所知，Windows 不能。 - jarlh

Answer 1

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

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'))
$$;

您还可以很容易地将数字值转换为普通时间戳，例如在超精度不必要的情况下：

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)

- klin

Answer 2

正如其他人所指出的，Postgres原生不提供这种类型。但是，由于Postgres的开源特性，创建支持纳秒分辨率的扩展相对简单。我以前遇到过类似的问题，并为Postgres创建了此timestamp9扩展。

它将时间戳作为bigint进行内部存储，并将其定义为自UNIX纪元以来的纳秒数。它提供了一些方便的功能，使查看和操作时间戳变得容易。如果您可以接受这些时间戳可以拥有的有限时间范围，即1970年至2262年之间，则这是一个很好的解决方案。

如果您不想在系统上安装扩展程序，Klin的答案是完美的解决方案。然而，它与现有的时间戳/间隔类型的配合效果不佳，并且效率也较低，因为它使用数字类型作为存储。拥有扩展程序可以给您更大的灵活性。

免责声明：我是该扩展程序的作者。

- fvannee

---

一个好奇的问题？ 你的方法能否适用于以下内容： 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)，这样Postgres会自动创建在巨大的选择插入中彼此相隔纳秒的ID，既唯一又人类可读吗？ 还是我应该为这个问题单独提问stackoverflow;o) - Yordan Georgiev

current_timestamp仍然具有微秒精度，并且是事务时间戳，因此对于所有行它都是相同的。我认为目前在Postgres本身中没有生成纳秒精度时间戳的方法（尽管这样的函数也可以添加到扩展中）。我们通常在客户端准备INSERT/COPY时生成纳秒级时间戳。 - fvannee

Answer 3

Bigint可以正常工作。如果您要保存所有纳秒级精度的时间戳，我建议定义一个新的转换:

CREATE CAST (timestamp AS bigint)
WITHOUT FUNCTION;

- user8369145

---

为什么WITHOUT FUNCTION在这里起作用，你能解释一下吗？有没有PostgreSQL文档保证可以进行二进制强制转换？ - nh2