Thread: DATE type output does not follow datestyle parameter
Hello, The description of datestyle parameter does not seem to match the actual behavior. Is this a bug to be fixed? Which do you think should be corrected, the program or the manual? The manual says: DateStyle (string) 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). ... And says: http://www.postgresql.org/docs/current/static/datatype-datetime.html 8.5.2. Date/Time Output The output of the date and time types is of course only the date or time part in accordance with the given examples. After doing "SET datestyle = 'Postgres, MDY'" on the psql prompt, I did the following things on the same psql session: 1. SELECT current_timestamp; now ---------------------------------- Wed Jul 24 10:51:00.217 2013 GMT (1 行) This is exactly as I expected. 2. SELECT current_date; I expected the output "Wed Jul 24 2013" or "Jul 24 2013", but I got: date ------------ 07-24-2013 (1 行) This does not follow the above statement in 8.5.2. This output is created by EncodeDateOnly() in src/backend/utils/adt/datetime.c. Regards MauMau
On 07/26/2013 05:31 AM, MauMau wrote: > Hello, > > The description of datestyle parameter does not seem to match the actual > behavior. Is this a bug to be fixed? Which do you think should be > corrected, the program or the manual? > > > The manual says: > > DateStyle (string) > 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). ... > > > And says: > > http://www.postgresql.org/docs/current/static/datatype-datetime.html > > 8.5.2. Date/Time Output > The output of the date and time types is of course only the date or time > part in accordance with the given examples. > > > After doing "SET datestyle = 'Postgres, MDY'" on the psql prompt, I did the > following things on the same psql session: > > > 1. SELECT current_timestamp; > > now > ---------------------------------- > Wed Jul 24 10:51:00.217 2013 GMT > (1 行) > > This is exactly as I expected. > > > 2. SELECT current_date; > I expected the output "Wed Jul 24 2013" or "Jul 24 2013", but I got: > > date > ------------ > 07-24-2013 > (1 行) > > This does not follow the above statement in 8.5.2. This output is created > by EncodeDateOnly() in src/backend/utils/adt/datetime.c. Actually the relevant code seems to be here: /src/ backend/parser/gram.y CURRENT_DATE { /* * Translate as "'now'::text::date". * .... CURRENT_TIMESTAMP { /* * Translate as "now()", since we have a function that * does exactly what is needed. ... Also from : 8.5.1.4. Special Values The following SQL-compatible functions can also be used to obtain the current time value for the corresponding data type: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. The latter four accept an optional subsecond precision specification. (See Section 9.9.4.) Note that these are SQL functions and are not recognized in data input strings. It would seem the functions are special cased. > > > Regards > MauMau > > > > -- Adrian Klaver adrian.klaver@gmail.com
From: "Adrian Klaver" <adrian.klaver@gmail.com>
> Actually the relevant code seems to be here:
>
> /src/ backend/parser/gram.y
>
>
> CURRENT_DATE
> {
> /*
> * Translate as
> "'now'::text::date".
> *
> ....
>
> CURRENT_TIMESTAMP
> {
> /*
> * Translate as "now()", since
> we have a function that
> * does exactly what is needed.
> ...
>
>
> Also from :
>
> 8.5.1.4. Special Values
>
> The following SQL-compatible functions can also be used to obtain the
> current time value for the corresponding data type: CURRENT_DATE,
> CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. The latter
> four accept an optional subsecond precision specification. (See Section
> 9.9.4.) Note that these are SQL functions and are not recognized in data
> input strings.
>
>
>
> It would seem the functions are special cased.
As below, normal DATE constants also does not follow the setting of
datestyle. Is this a bug?
SET datestyle = 'Postgres, MDY';
SELECT DATE 'Jan 2 2013';
date
------------
01-02-2013
(1 row)
Regards
MauMau
On 07/26/2013 01:24 PM, MauMau wrote:
> From: "Adrian Klaver" <adrian.klaver@gmail.com>
>> Actually the relevant code seems to be here:
>>
>> /src/ backend/parser/gram.y
>>
>>
>> CURRENT_DATE
>> {
>> /*
>> * Translate as
>> "'now'::text::date".
>> *
>> ....
>>
>> CURRENT_TIMESTAMP
>> {
>> /*
>> * Translate as "now()", since
>> we have a function that
>> * does exactly what is needed.
>> ...
>>
>>
>> Also from :
>>
>> 8.5.1.4. Special Values
>>
>> The following SQL-compatible functions can also be used to obtain the
>> current time value for the corresponding data type: CURRENT_DATE,
>> CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. The latter
>> four accept an optional subsecond precision specification. (See Section
>> 9.9.4.) Note that these are SQL functions and are not recognized in data
>> input strings.
>>
>>
>>
>> It would seem the functions are special cased.
>
>
> As below, normal DATE constants also does not follow the setting of
> datestyle. Is this a bug?
>
> SET datestyle = 'Postgres, MDY';
> SELECT DATE 'Jan 2 2013';
> date
> ------------
> 01-02-2013
> (1 row)
I could not tell you. The best I can do is point you at the bug
reporting page:
http://www.postgresql.org/support/submitbug/
>
> Regards
> MauMau
>
--
Adrian Klaver
adrian.klaver@gmail.com