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