Postgres Set To Datestyle Iso Mdy
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. Karl Nack Futurity, Inc.773-506-2007. Adobe premiere pro cs4 32 bit portable oxygen.
From PostgreSQL help: 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).
See More On Stackoverflow
Karl Nack 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 tochar(datefield, 'MM/DD/YYYY') FROM sometable' Running the following, either directly in psql or upon connecting.
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 tochar(datefield, 'MM/DD/YYYY') FROM sometable'. Karl Nack 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 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 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.
Give More Feedback
I know this has been asked many times but no matter what I try in PostgreSQL 9.4, the datestyle will revert to ISO,MDY within a minute or so. I require the date format: SET datestyle to SQL,DMY; Show datesyle; E.g. After entering this, the 'show' command displays SQL,DMY SELECT NOW; Displays the current date in correct format Each time I apply this setting and look at my date column it still turns my input into yyyy-mm-dd. Then shortly after if i run: Show datestyle; It's back to ISO,MDY. I have applied the datestyles in three other ways: ALTER DATABASE 'database' SET datestyle TO SQL,DMY; ALTER USER 'User' SET datestyle to SQL,DMY;. postgresql.config - Changed the datestyle variable and restarted the server I have spent hours looking up advice and tried editing LCTIME and the results are the same.