postgresql - 如何更改 Postgres 中的日期格式？

Question

我收到以下错误消息

错误:日期/时间字段值超出范围:“13/01/2010”提示:也许您需要不同的“datestyle”设置。

我想以 DD/MM/YYYY 格式获取我的日期

Answer 1

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

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).

当然最好使用无歧义的输入格式(ISO 8601)，但根据需要调整也没有问题。