Thread: changing datestyle

This is driving me a little nuts.

SET datestyle = 'us';
ALTER DATABASE mydb SET datestyle = 'us';
ALTER USER me SET datestyle = 'us';

In postgresql.conf:
datestyle = 'us'

And yet datestyle continues to come back as 'ISO, MDY'.

Re-doing all this using 'MDY, MDY' doesn't work either.

I don't much care about the input format, since postgres is pretty
flexible, but it'd sure be nice to get m/d/y out without having to
manually convert it in all my queries.

What am I missing?

If it helps, I'm running 8.3.7 on Debian Lenny.

Thanks.
Karl Nack Futurity, Inc.773-506-2007

"Karl Nack" <karlnack@futurityinc.com> writes:
> This is driving me a little nuts.
> SET datestyle = 'us';
> And yet datestyle continues to come back as 'ISO, MDY'.

That's exactly what it should do.  'US' is a synonym for 'MDY',
and in any case it determines only one of the two components
of DateStyle.

(The fact that there are two components is a historical aberration,
and I'd be the first to agree it's ugly, but that's how it works.)

> What am I missing?

You need to read the manual a bit more closely, perhaps; or at
least explain what your *actual* problem is.

            regards, tom lane

>> And yet datestyle continues to come back as 'ISO, MDY'.
>
> That's exactly what it should do.  'US' is a synonym for 'MDY',
> and in any case it determines only one of the two components
> of DateStyle.
>
> (The fact that there are two components is a historical aberration,
> and I'd be the first to agree it's ugly, but that's how it works.)

My understanding is that the first component indicates how dates are
output by default, the second how dates are interpreted upon input
(to disambiguate between MDY and DMY).

If this is correct (I'm beginning to think it's not), I'd like something
like 'MDY, MDY', where dates are output as MDY by default (instead of
ISO). That way I don't have to keep writing queries like "SELECT
to_char(date_field, 'MM/DD/YYYY') FROM some_table"

> You need to read the manual a bit more closely, perhaps; or at
> least explain what your *actual* problem is.

Running the following, either directly in psql or upon connecting from a
webserver (php specifically), doesn't change the datestyle setting:
SET datestyle = 'US'
SET datestyle = 'MDY'
SET datestyle = 'MDY, MDY'

I was expecting it to change to something like 'MDY, MDY'.

Obviously I'm missing something here, just not sure what!


Karl Nack

Futurity, Inc.
773-506-2007

Karl Nack <karlnack@futurityinc.com> writes:
>> (The fact that there are two components is a historical aberration,
>> and I'd be the first to agree it's ugly, but that's how it works.)

> My understanding is that the first component indicates how dates are
> output by default, the second how dates are interpreted upon input
> (to disambiguate between MDY and DMY).

The second one affects field order for some of the output styles, too.
See
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
In particular, tables 8-14 and 8-15 illustrate all the output formatting
options you have.  SQL, MDY might be the closest to what you want.

            regards, tom lane

>>> (The fact that there are two components is a historical aberration,
>>> and I'd be the first to agree it's ugly, but that's how it works.)
>
> The second one affects field order for some of the output styles, too.
> See
> http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
> In particular, tables 8-14 and 8-15 illustrate all the output formatting
> options you have.  SQL, MDY might be the closest to what you want.

Yeah, I was re-reading that section as I got your response, and I've come
to the same conclusion. I was misunderstanding how the two components work
together. _Now_ I see why it's ugly!

I knew I was being a knucklehead; thanks for the insight.

Is there any reason I shouldn't simply set my server default to 'SQL, MDY'
instead of specifying it at connection time (or per database/user)?


Karl Nack

Futurity, Inc.
773-506-2007

Karl Nack <karlnack@futurityinc.com> writes:
> Is there any reason I shouldn't simply set my server default to 'SQL, MDY'
> instead of specifying it at connection time (or per database/user)?

If that's what all your applications want, go for it.  pg_dump, and
other applications that really care, are supposed to set it for
themselves instead of assuming the default is what they want.

            regards, tom lane