默认时间戳格式和小数秒

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 ?

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

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()。

参见@Clodoaldo的回答to_char()和如何ALTER TABLE的说明。

关于时间戳和时区处理的深入信息的相关回答:

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