postgresql 在 postgres 中，您可以按会话或全局设置时间戳的默认格式吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8723574/
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
in postgres, can you set the default formatting for a timestamp, by session or globally?
提问by cc young
In Postgres, is it possible to change the default format mask for a timestamp?
在 Postgres 中，是否可以更改时间戳的默认格式掩码？
right now comes back as
现在回来了
2012-01-03 20:27:53.611489
I would like resolution to minute like this:
我希望分辨率像这样：
2012-01-03 20:27
I know I can do this on individual columns with to_char() asor stripped down with a substr()by the receiving app, but having it formatted correctly initially would save a lot of work and reduce a lot of errors.
我知道我可以通过接收应用程序在单个列上执行此操作，to_char() as或者substr()通过接收应用程序对其进行剥离，但是最初将其正确格式化将节省大量工作并减少大量错误。
采纳答案by Erwin Brandstetter
In postgres, you canchange the default format mask for datetimes - using the set datestyleoption; the available options can be found here(see 8.5.2. Date/Time Output).
在 postgres 中，您可以更改日期时间的默认格式掩码 - 使用该set datestyle选项；可以在此处找到可用选项（请参阅 8.5.2. 日期/时间输出）。
Unfortunately, all the available options include the number of seconds - you will therefore need to reformat them either in the query or the application code (if applicable).
不幸的是，所有可用选项都包括秒数 - 因此您需要在查询或应用程序代码（如果适用）中重新格式化它们。
回答by Eric Leschinski
In PostgreSQL, The formatting of timestamps is independent of storage. One answer is to use to_charand format the timestamp to whatever format you need at the moment you need it, like this:
在 PostgreSQL 中，时间戳的格式与存储无关。一个答案是在to_char您需要时使用时间戳并将其格式化为您需要的任何格式，如下所示：
select to_char(current_timestamp, 'yyyy-MM-dd HH24:MI:SS.MS');
select to_timestamp('2012-10-11 12:13:14.123',
'yyyy-MM-dd HH24:MI:SS.MS')::timestamp;
But if you must set the default formatting:
但是如果你必须设置默认格式：
Change the postgresql timestamp format globally:
全局更改 postgresql 时间戳格式：
Take a look at your timezone, run this as an sql query:
查看您的时区，将其作为 sql 查询运行：
show timezone
Result: "US/Eastern"
So when you are printing out current_timestamp, you see this:
因此，当您打印 current_timestamp 时，您会看到：
select current_timestamp
Result: 2012-10-23 20:58:35.422282-04
The -04at the end is your time zone relative to UTC. You can change your timezone with:
将-04在年底为您的时间相对于UTC区。您可以通过以下方式更改时区：
set timezone = 'US/Pacific'
Then:
然后：
select current_timestamp
Result: 2012-10-23 18:00:38.773296-07
So notice the -07there, that means we Pacific is 7 hours away from UTC. How do I make that unsightly timezone go away? One way is just to make a table, it defaults to a timestamp without timezone:
所以请注意-07那里，这意味着我们太平洋地区距 UTC 时间为 7 小时。我如何让那个难看的时区消失？一种方法是制作一个表格，它默认为没有时区的时间戳：
CREATE TABLE worse_than_fail_table
(
mykey INT unique not null,
fail_date TIMESTAMP not null
);
Then if you add a timestamp to that table and select from it
然后，如果您向该表添加时间戳并从中选择
select fail_date from worse_than_fail_table
Result: 2012-10-23 21:09:39.335146
yay, no timezone on the end. But you want more control over how the timestamp shows up by default! You could do something like this:
是的，最后没有时区。但是您想要更多地控制默认情况下时间戳的显示方式！你可以这样做：
CREATE TABLE moo (
key int PRIMARY KEY,
boo text NOT NULL DEFAULT TO_CHAR(CURRENT_TIMESTAMP,'YYYYMM')
);
It's a text field which gives you more control over how it shows up by default when you do a select somecolumns from sometable. Notice you can cast a string to timestamp:
这是一个文本字段，可让您更好地控制在执行select somecolumns from sometable. 请注意，您可以将字符串转换为时间戳：
select '2012-10-11 12:13:14.56789'::timestamp
Result: 2012-10-11 12:13:14.56789
You could cast a current_timestamp to timestampwhich removes the timezone:
您可以将 current_timestamp 转换timestamp为删除时区：
select current_timestamp::timestamp
Result: 2012-10-23 21:18:05.107047
You can get rid of the timezone like this:
您可以像这样摆脱时区：
select current_timestamp at time zone 'UTC'
Result: "2012-10-24 01:40:10.543251"
But if you really want the timezone back you can do this:
但如果你真的想要时区回来，你可以这样做：
select current_timestamp::timestamp with time zone
Result: 2012-10-23 21:20:21.256478-04
You can yank out what you want with extract:
您可以使用提取物提取您想要的内容：
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20
And this monstrosity:
还有这个怪物：
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'EST';
Result: 2001-02-16 20:38:40
回答by Erwin Brandstetter
to_char()is used to create a string literal. If you want a different timestamp value, use:
to_char()用于创建字符串文字。如果您想要不同的时间戳值，请使用：
date_trunc('minute', now())
For an inputmask you can use:
对于输入掩码，您可以使用：
to_timestamp('2012-01-03 20:27:53.611489', 'YYYY-MM-DD HH24:MI')
Cast to timestamp without time zoneby appending ::timestamp.
投射到timestamp without time zone通过附加::timestamp。
To my knowledge, there is not setting in PostgreSQL that would trim seconds from timestamp literals by default.
据我所知，PostgreSQL 中没有设置会默认从时间戳文字中修剪秒数。
