如何在PostgreSQL中将时间戳(0)设置为默认值而不是时间戳(6)？

Question

我正在使用存储时间序列数据的PostgreSQL数据库。 数据使用“具有时区的时间戳”类型进行存储，并且输出为ISO格式（postgresql默认）。数据源是一个创建带有“具有时区的时间戳”数据类型的表格的闭源软件。

根据Postgres文档，时间戳的精度可达到6位数字（微秒）。因此，我的时间序列每秒记录一次，并在输出中看到毫秒。

e.g. 2012-06-25 15:46:23.001

这个软件在绘制时间序列数据时会出现毫秒级的问题，但我无法修改数据源软件以使用“timestamp（0）”创建表。

我的一个同事编写了一个函数，查询整个数据库将时间戳数据类型更改为每个表中的“带时区的时间戳（0）”。它运行良好，但需要手动操作，必须在每次创建新表后执行。我们希望避免使用该函数。

是否可以通过编辑postgresql.conf告诉PostgreSQL创建“timestamp（0）”时间戳数据类型而不是默认的“timestamp（6）”呢？

- Stb

Answer 1

这个处理方式非常硬编码，用户无法轻松更改。(如果您感兴趣，可以查看源代码中的AdjustTimestampForTypmod()。)

您可以在表上创建一个视图，将时间戳列转换为timestamp(0)或使用to_char格式化它，并将渲染软件指向该视图。

- Peter Eisentraut

---

如果渲染软件也无法调整，则创建“on select”规则。 - Abelisto

@Abelisto：这与创建视图是一样的。 - Peter Eisentraut

关系名称的差异。如果绘图软件无法更改（问题不清楚）。 - Abelisto

Answer 2

这个技巧（不要在生产数据库上执行！）：

postgres=# alter type timestamptz rename to timestamptz6;
ALTER TYPE
postgres=# create domain pg_catalog.timestamptz as timestamptz6(0);
CREATE DOMAIN
postgres=# create table t (tz1 timestamp with time zone, tz2 timestamptz);
CREATE TABLE
postgres=# insert into t values(now(), now());
INSERT 0 1
postgres=# select * from t;
╔════════════════════════╤════════════════════════╗
║          tz1           │          tz2           ║
╠════════════════════════╪════════════════════════╣
║ 2016-08-11 21:06:17+03 │ 2016-08-11 21:06:17+03 ║
╚════════════════════════╧════════════════════════╝
(1 row)

咦！就是这样。

现在让我们把一切恢复到之前的状态：

postgres=# drop table t;
DROP TABLE
postgres=# drop domain pg_catalog.timestamptz;
DROP DOMAIN
postgres=# alter type timestamptz6 rename to timestamptz;
ALTER TYPE

使用时请自行承担风险。祝好运。

- Abelisto

---

哎呀，将一个域替换为 pg_catalog 中的原生类型可能会令所有东西都兴奋地崩溃。但是，嘿，对于一个狭窄的用例来说... - Craig Ringer

@CraigRinger 为什么？现有对象通过OID而不是名称引用类型（除了函数体），域本身引用原始类型，因此隐式类型转换仍然存在...但你是对的，这是针对少数特定情况的解决方案。 - Abelisto