postgresql 默认时间戳格式和小数秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22332425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Default timestamp format and fractional seconds
提问by Anko
I'm trying to format the timestamps in my Postgres database to a certain format:
我正在尝试将 Postgres 数据库中的时间戳格式化为某种格式：
YYYY-MM-DD HH24:MI:SS
By doing:
通过做：
update myTable set tds = to_char(tds, 'YYYY-MM-DD HH24:MI:SS')::timestamp;
I managed to set all the previously stored tdsto this format. However, any newly added entry goes back to: YYYY-MM-DD HH24:MI:SS.MSsince the default is set to now().
我设法将以前存储的所有内容都设置tds为这种格式。但是，任何新添加的条目都会返回到：YYYY-MM-DD HH24:MI:SS.MS因为默认设置为now()。
How do I change this so that newly added entries also have the format: YYYY-MM-DD HH24:MI:SS?
如何改变这种做法，新添加的条目也有格式：YYYY-MM-DD HH24:MI:SS？
回答by Clodoaldo Neto
There is no format stored in a timestamp type. You can set its default to a timestamp truncated to the second at creation time
时间戳类型中没有存储格式。您可以将其默认设置为在创建时截断为秒的时间戳
create table t (
tds timestamp default date_trunc('second', now())
)
Or alter the table
或者改变表格
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
If you just don't want to show the milliseconds part format the output
如果您只是不想显示毫秒部分格式输出
select to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
to_char
---------------------
2014-03-11 19:39:40
回答by Erwin Brandstetter
The types timestampor timestamptzoptionally take a precision modifier p: timestamp(p).
To roundto full seconds, set the default to:
类型timestamp或timestamptz可选地采用精度修饰符p: timestamp(p)。
要四舍五入到整秒，请将默认值设置为：
now()::timestamp(0)` or `now()::timestamptz(0)
Standard SQL functions CURRENT_TIMESTAMP(returns timestamptz) or LOCALTIMESTAMP(returns timestamp) allow the same precision modifier:
标准 SQL 函数CURRENT_TIMESTAMP(returns timestamptz) 或LOCALTIMESTAMP(returns timestamp) 允许相同的精度修饰符：
CURRENT_TIMESTAMP(0)
LOCALTIMESTAMP(0)
That's a bit cheaper and shorter than calling date_trunc()- which truncatesfractional seconds (may be what you really want!)
这比调用更便宜和更短date_trunc()-截断小数秒（可能是您真正想要的！）
Store timestamps as timestamptz(or timestamp), notusing a character type.
将时间戳存储为timestamptz(或timestamp)，不使用字符类型。
Finally, to make surethat ...
最后，为了确保...
newly added entries also have the format: YYYY-MM-DD HH24:MI:SS
新添加的条目也有格式：YYYY-MM-DD HH24:MI:SS
you could define your column as type timestamptz(0). This will not only cover the default, but all values entered to that column.
您可以将您的列定义为 type timestamptz(0)。这不仅会涵盖默认值，还会涵盖输入到该列的所有值。
See @Clodoaldo's answerfor instructions on to_char()and how to ALTER TABLE.
请参阅@Clodoaldo 的回答以获取有关to_char()以及如何操作的说明ALTER TABLE。
This related answer for in-depth information on timestamps and time zone handling:
有关时间戳和时区处理的深入信息的相关答案：
