Re: DATE type output does not follow datestyle parameter - Mailing list pgsql-hackers

Hi, Bruce san,

From: "Bruce Momjian" <bruce@momjian.us>
> On Tue, Aug  6, 2013 at 12:09:53PM -0400, Tom Lane wrote:
>> Yes.  I don't see any reason to change it, either, as nobody has
>> complained that it's actually bad.  If you feel a compulsion to
>> change the docs, do that.
>
> OK, seems 'Postgres' is a unique output format for 'date' too, even though
> it doesn't look like the 'Postgres' timestamp output:
>
> default
> SET datestyle = 'ISO, MDY'; SELECT current_timestamp, current_date;
> SET
>               now              |    date
> -------------------------------+------------
> 2013-08-06 16:18:48.218555-04 | 2013-08-06
>
> SET datestyle = 'SQL, MDY'; SELECT current_timestamp, current_date;
> SET
>               now               |    date
> --------------------------------+------------
> 08/06/2013 16:18:43.054488 EDT | 08/06/2013
>
> SET datestyle = 'German, MDY'; SELECT current_timestamp, current_date;
> SET
>               now               |    date
> --------------------------------+------------
> 06.08.2013 16:18:59.026553 EDT | 06.08.2013
>
> MDY
> SET datestyle = 'Postgres, MDY'; SELECT current_timestamp, current_date;
> SET
>                  now                 |    date
> -------------------------------------+------------
> Tue Aug 06 16:18:53.590548 2013 EDT | 08-06-2013
>
> DMY
> SET datestyle = 'Postgres, DMY'; SELECT current_timestamp, current_date;
> SET
>                  now                 |    date
> -------------------------------------+------------
> Tue 06 Aug 16:20:23.902549 2013 EDT | 06-08-2013
>
> I don't think there is even a documentation change I can suggest.

I'm sorry I didn't respond for a long time.  I've come up with a suggestion.

The original reporter of this problem expected the output of the date type 
in 'Postgres,DMY' style to be "17 Dec 1997", when the output of the 
timestamp if "Wed 17 Dec 07:37:16 1997 PST".  He thought so by reading the 
following sentence:

http://www.postgresql.org/docs/current/static/datatype-datetime.html
The output of the date and time types is of course only the date or time 
part in accordance with the given examples.

However, the actual output of the date type in Postgres style is:

* 12-17-1997 if datestyle is 'Postgres,YMD' or 'Postgres,MDY'
* 17-12-1997 if datestyle is 'Postgres,DMY'

So, my suggestion is to just add the following sentence right after the 
above one.

The Postgres style is an exception: the output of the date type is either 
MM-DD-YYYY (e.g. 12-17-1997) or DD-MM-YYYY (e.g. 17-12-1997), which is 
different from the date part of the output of the timestamp type.

Could you consider and add this to the manual?

Regards
MauMau