Postgresql日期格式 [英] Postgresql date format

问题描述

我有一个网页应用程序（用python / django编写）（由于规范不好）Web窗体期待YYYY-mm-dd日期格式，其他使用dd / mm / yy日期格式。

有没有办法告诉postgresql接受两种格式的日期？例如，尝试dd / mm / yy，如果失败，请尝试yyyy-mm-dd。

这将是非常棒的。 p>

解决方案

从精细手册：

日期和时间输入被接受几乎任何合理的格式，包括ISO 8601，SQL兼容，传统POSTGRES等。对于某些格式，日期，月份和年份输入日期的排序是不明确的，并且支持指定这些字段的预期排序。将DateStyle参数设置为MDY以选择月 - 年解读，DMY选择日月解读，或选择YMD以选择年月解读。

$ b $所以PostgreSQL应该能够处理你抛出的任何日期格式。但是，您的dd / mm / yy格式是不明确的。但是， DateStyle 配置参数来帮助解决这种歧义。

例如：

  =>创建表x（d日期不为null）; 
 =>插入x值（'2001-01-10'）; 
 => insert into x values（'Feb 2 2980'）; 
 =>插入x值（'01 / 02/03'）; 
 =>选择*从x; 
d 
 ------------ 
 2001-01-10 
 2980-02-02 
 2003-02-01 $ b $那么说，我建议在内部将所有内容移至ISO 8601（YYYY-MM-DD），并处理转换（conversions Y Y MM MM MM conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions conversions在应用程序的边缘。 OTOH，有现实的对抗，所以你应该做任何你需要做的，让它走。
 
I have a web application (written with python/django) that (due a bad specification) Web Forms expecting "YYYY-mm-dd" date format and others using "dd/mm/yy" date format. 

Is there a way to tell postgresql to accept dates in both formats? for example, to try "dd/mm/yy" and, if it fails, then try "yyyy-mm-dd".

That would be awesome.
解决方案
From the fine manual:

  
Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. For some formats, ordering of day, month, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields. Set the DateStyle parameter to MDY to select month-day-year interpretation, DMY to select day-month-year interpretation, or YMD to select year-month-day interpretation.
  
  
PostgreSQL is more flexible in handling date/time input than the SQL standard requires. See Appendix B for the exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones.
So PostgreSQL should be able to deal with just about any date format you throw at it. Your "dd/mm/yy" format is, however, ambiguous. But, there is the DateStyle configuration parameter to help with such ambiguity.

For example:
=> create table x (d date not null);
=> insert into x values ('2001-01-10');
=> insert into x values ('Feb 2 2980');
=> insert into x values ('01/02/03');
=> select * from x;
     d      
------------
 2001-01-10
 2980-02-02
 2003-02-01
That said, I'd recommend moving everything to ISO 8601 (YYYY-MM-DD) internally and handle the conversions at the edges of the application. OTOH, there is reality to contend with so you should do whatever you have to do to make it go.

                        
这篇关于Postgresql日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！