默认时间戳格式和小数秒

Question

我正在尝试将我的Postgres数据库中的时间戳格式化为特定格式：

YYYY-MM-DD HH24:MI:SS

通过以下方式：

update myTable set tds = to_char(tds, 'YYYY-MM-DD HH24:MI:SS')::timestamp;

我成功将之前存储的所有tds都设置为此格式。然而，任何新添加的条目都会回到：YYYY-MM-DD HH24:MI:SS.MS，因为默认设置为now()。

我该如何更改这个设置，使得新添加的条目也具有以下格式：YYYY-MM-DD HH24:MI:SS？

- Anko

---

我看到你想截断毫秒部分？你可以通过使用触发器来实现。 - Houari

你的表定义在哪里？我们需要看到它才能确定。在psql中使用\d myTable命令。 - Erwin Brandstetter

Answer 1

时间戳类型中没有存储格式。您可以将其默认设置为创建时被截断到秒的时间戳。

create table t (
    tds timestamp default date_trunc('second', now())
)

或者修改表

alter table t 
alter column tds 
set default date_trunc('second', now());

insert into t values (default);
INSERT 0 1

select * from t;
         tds         
---------------------
 2014-03-11 19:24:11

如果您不想显示毫秒部分，请格式化输出。

select to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
       to_char       
---------------------
 2014-03-11 19:39:40

- Clodoaldo Neto

Answer 2

类型 timestamp 或者 timestamptz 可以选择性地带有精度修饰符 p： timestamp(p)。
要进行四舍五入到整秒，将默认值设置为：

now()::timestamp(0)

或者：

now()::timestamptz(0)

标准SQL函数CURRENT_TIMESTAMP（返回timestamptz）或LOCALTIMESTAMP（返回timestamp）允许使用相同的精度修饰符：

CURRENT_TIMESTAMP(0)
LOCALTIMESTAMP(0)

这比调用date_trunc()要短一些——它截断了小数秒（也许这正是你想要的！）

date_trunc('second', now())

请将时间戳存储为timestamptz（或timestamp），而不是字符类型。

最后，为了确保新添加的条目也具有格式：YYYY-MM-DD HH24: MI:SS

您可以将列定义为timestamptz(0)类型。这将覆盖输入到该列中的所有值，而不仅仅是默认值。但是，四舍五入可能会引入未来半秒的时间戳。如果这可能会造成问题，请使用date_trunc()。

有关说明to_char()和如何ALTER TABLE的详细信息，请参见@Clodoaldo的答案。

此相关答案提供有关时间戳和时区处理的详细信息：

• 完全忽略时区在Rails和PostgreSQL中的处理

- Erwin Brandstetter