postgresql 如何更改 Postgres 中的日期格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6123484/
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
How do I alter the date format in Postgres?
提问by deltanovember
I'm getting the following error message
我收到以下错误消息
ERROR: date/time field value out of range: "13/01/2010" HINT: Perhaps you need a different "datestyle" setting.
错误：日期/时间字段值超出范围：“13/01/2010” 提示：也许您需要不同的“日期样式”设置。
I want to get my date in the format DD/MM/YYYY
我想以 DD/MM/YYYY 格式获取我的日期
回答by Grzegorz Szpetkowski
SHOW datestyle;
DateStyle
-----------
ISO, MDY
(1 row)
INSERT INTO container VALUES ('13/01/2010');
ERROR: date/time field value out of range: "13/01/2010"
HINT: Perhaps you need a different "datestyle" setting.
SET datestyle = "ISO, DMY";
SET
INSERT INTO container VALUES ('13/01/2010');
INSERT 0 1
SET datestyle = default;
SET
http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE
http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE
DateStyle - Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD).
DateStyle - 设置日期和时间值的显示格式，以及解释不明确日期输入值的规则。由于历史原因，此变量包含两个独立的组件：输出格式规范（ISO、Postgres、SQL 或德语）和年/月/日排序的输入/输出规范（DMY、MDY 或 YMD）。
Of course it's best to use unambiguous input format (ISO 8601), but there is no problem to adjust it as you need.
当然最好使用无歧义的输入格式（ISO 8601），但是根据需要进行调整是没有问题的。
回答by Berry Langerak
You could set the date style to Europian (dd/mm/yyyy):
您可以将日期样式设置为 Europian (dd/mm/yyyy)：
SET DateStyle TO European;
I'd advise against this though. I generally try to convert between formats, and keep ISO formatted dates in the data source. After all, it's only a matter of representation, not a matter of different data.
不过我建议不要这样做。我通常尝试在格式之间进行转换，并在数据源中保留 ISO 格式的日期。毕竟，这只是一个表示问题，而不是不同数据的问题。
回答by a_horse_with_no_name
Edit:
编辑：
When using this COPY, the valid input format isdefined by the server configuration and can either be changed for the current session using the SET command as described by Berry or by adjusting the server configuration.
当使用这种COPY，有效输入格式是由服务器配置中定义和可以被改变为使用SET命令由浆果或通过调整服务器配置描述的当前会话。
DateStyle description in the manual:
http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE
手册中的DateStyle描述：http:
//www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE
The following is not valid for the real situation, but I'm keeping it for reference anyway
以下内容不适用于实际情况，但无论如何我都会保留以供参考
When using date (or timestamp) literals always specify a format mask to convert them. Otherwise your statements aren't portable and won't necessarily run on every installation.
当使用日期（或时间戳）文字时，总是指定一个格式掩码来转换它们。否则，您的语句不可移植，并且不一定在每次安装时都运行。
The ANSI SQL standard for date literals is like this:
日期文字的 ANSI SQL 标准是这样的：
UPDATE some_table
SET date_column = DATE '2011-05-25'
WHERE pk_column = 42;
If you cannot change the literal format, you need to apply the to_date() function
如果无法更改文字格式，则需要应用 to_date() 函数
UPDATE some_table
SET date_column = to_date('13/01/2010', 'dd/mm/yyyy')
WHERE pk_column = 42;
If this is not what you are doing you should show us the full SQL statement that generated the error.
如果这不是您所做的，您应该向我们展示产生错误的完整 SQL 语句。
