Thread: BUG #3563: DATESTYLE feature suggestion
The following bug has been logged online: Bug reference: 3563 Logged by: Randolf Richardson Email address: randolf+postgresql.org@inter-corporate.com PostgreSQL version: 8.2.4 Operating system: NetBSD 4 (beta), NetBSD 3.1, NetWare 6.5 Description: DATESTYLE feature suggestion Details: After convincing clients and colleagues to switch from Oracle (and others) to PostgreSQL, an issue that comes up is the need to customize DATESTYLE. Because this isn't possible, the developers who were against the move to PostgreSQL make it political and recommended work-around solutions such as using to_char() or implementing a view for each table that contain TIMESTAMP[TZ]s is very difficult to argue with management because a lot of time is required to implement these items. In a future version, to solve this problem, an additional DATESTYLE option that uses the same rules as the to_char() function for date formatting would solve this problem. Here's an example: SET DATESTYLE = 'Custom YYYY-Mon-DD'; This feature would not only resolve this particular political strife, but would also solve many other problems, including simplifying coding for raw SQL output serving as reports (e.g., users still get confused about dates like "2007-06-03," wondering if they refer to June 3rd, or March 6th). I'm hoping that this suggestion will be an easy one to implement. Thanks in advance. P.S.: I searched around for a "feature suggestions" page but couldn't find it (if one exists, it should be linked to from the "Report a Bug" page).
Randolf Richardson wrote: > After convincing clients and colleagues to switch from Oracle (and others) > to PostgreSQL, an issue that comes up is the need to customize DATESTYLE. > Because this isn't possible, the developers who were against the move to > PostgreSQL make it political and recommended work-around solutions such as > using to_char() or implementing a view for each table that contain > TIMESTAMP[TZ]s is very difficult to argue with management because a lot of > time is required to implement these items. > > In a future version, to solve this problem, an additional DATESTYLE option > that uses the same rules as the to_char() function for date formatting would > solve this problem. Here's an example: > > SET DATESTYLE = 'Custom YYYY-Mon-DD'; > > This feature would not only resolve this particular political strife, but > would also solve many other problems, including simplifying coding for raw > SQL output serving as reports (e.g., users still get confused about dates > like "2007-06-03," wondering if they refer to June 3rd, or March 6th). > > I'm hoping that this suggestion will be an easy one to implement. Probably wouldn't be too hard. I'm curious, what datestyle do you need? The current datestyle GUC variable provides the most common ones already. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > Randolf Richardson wrote: > > >> After convincing clients and colleagues to switch from Oracle (and others) >> to PostgreSQL, an issue that comes up is the need to customize DATESTYLE. >> Because this isn't possible, the developers who were against the move to >> PostgreSQL make it political and recommended work-around solutions such as >> using to_char() or implementing a view for each table that contain >> TIMESTAMP[TZ]s is very difficult to argue with management because a lot of >> time is required to implement these items. >> >> In a future version, to solve this problem, an additional DATESTYLE option >> that uses the same rules as the to_char() function for date formatting would >> solve this problem. Here's an example: >> >> SET DATESTYLE = 'Custom YYYY-Mon-DD'; >> >> This feature would not only resolve this particular political strife, but >> would also solve many other problems, including simplifying coding for raw >> SQL output serving as reports (e.g., users still get confused about dates >> like "2007-06-03," wondering if they refer to June 3rd, or March 6th). >> >> I'm hoping that this suggestion will be an easy one to implement. >> > > Probably wouldn't be too hard. > > I'm curious, what datestyle do you need? The current datestyle GUC > variable provides the most common ones already. > The issue is output, not input. SET datestyle='dmy'; SELECT '03-03-2004'::date Will return '2007-03-03', not 03-03-2004 as is the set datestyle. Regards Russell
Russell Smith wrote: > The issue is output, not input. > > SET datestyle='dmy'; > SELECT '03-03-2004'::date > > Will return '2007-03-03', not 03-03-2004 as is the set datestyle. You are aware that DateStyle controls both input and output, _separately_, yes? -- Alvaro Herrera http://www.advogato.org/person/alvherre A male gynecologist is like an auto mechanic who never owned a car. (Carrie Snow)
Randolf Richardson wrote: >>> I'm hoping that this suggestion will be an easy one to implement. >> Probably wouldn't be too hard. > > That's great! I'm guessing that this is due to the work already > done with the to_char() function. Just to be clear, I don't have any plans to actually do it, just saying that if someone wanted to do it, it probably wouldn't be too difficult. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Randolf Richardson wrote: > After convincing clients and colleagues to switch from Oracle (and others) > to PostgreSQL, an issue that comes up is the need to customize DATESTYLE. > Because this isn't possible, the developers who were against the move to > PostgreSQL make it political and recommended work-around solutions such as > using to_char() or implementing a view for each table that contain > TIMESTAMP[TZ]s is very difficult to argue with management because a lot of > time is required to implement these items. > > In a future version, to solve this problem, an additional DATESTYLE option > that uses the same rules as the to_char() function for date formatting would > solve this problem. Here's an example: > > SET DATESTYLE = 'Custom YYYY-Mon-DD'; > > This feature would not only resolve this particular political strife, but > would also solve many other problems, including simplifying coding for raw > SQL output serving as reports (e.g., users still get confused about dates > like "2007-06-03," wondering if they refer to June 3rd, or March 6th). > > I'm hoping that this suggestion will be an easy one to implement. > > Thanks in advance. > > P.S.: I searched around for a "feature suggestions" page but couldn't find > it (if one exists, it should be linked to from the "Report a Bug" page). Yea, it isn't too hard to do, especially for output; input might be harder. This is the first request we have ever gotten for this so it is doubtful we would add this feature unless there is more demand. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> Randolf Richardson wrote:
>
> > After convincing clients and colleagues to switch from Oracle (and others)
> > to PostgreSQL, an issue that comes up is the need to customize DATESTYLE.
> > Because this isn't possible, the developers who were against the move to
> > PostgreSQL make it political and recommended work-around solutions such as
> > using to_char() or implementing a view for each table that contain
> > TIMESTAMP[TZ]s is very difficult to argue with management because a lot of
> > time is required to implement these items.
> >
> > In a future version, to solve this problem, an additional DATESTYLE option
> > that uses the same rules as the to_char() function for date formatting would
> > solve this problem. Here's an example:
> >
> > SET DATESTYLE = 'Custom YYYY-Mon-DD';
> >
> > This feature would not only resolve this particular political strife, but
> > would also solve many other problems, including simplifying coding for raw
> > SQL output serving as reports (e.g., users still get confused about dates
> > like "2007-06-03," wondering if they refer to June 3rd, or March 6th).
> >
> > I'm hoping that this suggestion will be an easy one to implement.
>
> Probably wouldn't be too hard.
That's great! I'm guessing that this is due to the work already
done with the to_char() function.
> I'm curious, what datestyle do you need? The current datestyle GUC
> variable provides the most common ones already.
The datestyle I need is "YYYY-Mon-DD" so that reports can easily be
generated that show dates like 2007-Aug-22. For people reading the
output, there will be absolutely no confusion about what the date is.
But that's the format that I'm interested in (and I deal with many
others who really like it as well); I think that using the same rules
as to_char() would potentially serve everyone better than supplying a
special datestyle just to match my preference.
Thanks.
Randolf Richardson - randolf@richardson.tw
Vancouver, British Columbia, Canada
http://www.randolf.richardson.tw/
"Radio-active cats have 18 half-lives."
On Aug 22, 2007, at 10:13 , Randolf Richardson wrote: > The datestyle I need is "YYYY-Mon-DD" so that reports can easily be > generated that show dates like 2007-Aug-22. For people reading the > output, there will be absolutely no confusion about what the date is. Are you generating reports straight from PostgreSQL or using some kind of middleware? Michael Glaesemann grzm seespotcode net
Bruce Momjian <bruce@momjian.us> writes:
> Yea, it isn't too hard to do, especially for output; input might be
> harder. This is the first request we have ever gotten for this so it is
> doubtful we would add this feature unless there is more demand.
I don't think any input-side changes are being requested here; the input
reader is already flexible enough to cope with nearly any sane format.
The complaint is evidently that he shouldn't have to use to_char() to
produce an *output* format of his liking.
I'm far from convinced that we should allow any arbitrary output format,
since it's easy to imagine shooting yourself in the foot that way by
making something the input reader wouldn't recognize reliably. But
I think we could consider redesigning the datestyle feature to offer a
wider set of known-safe formats. We already did this on the input side
--- remember the old "euro" kluge? We now have MDY/DMY/YMD, which is to
my mind a lot better design. On the output side we still have
ISO/SQL/POSTGRES/GERMAN, which just reeks of non orthogonality.
ISTM the base features you'd want to control are date field order, the
date field separator character (slash dash or dot are probably
sufficient), and whether month is numeric or not. Perhaps a design
oriented around that type of breakdown might fly.
regards, tom lane
Randolf Richardson wrote: > After convincing clients and colleagues to switch from Oracle (and > others) to PostgreSQL, an issue that comes up is the need to > customize DATESTYLE. Because this isn't possible, Sure it's possible. You replace the respective data type output functions with something that calls to_char on the value. You can even register your own configuration parameter to control the template used by to_char. If this is the only thing stopping you from making a major technology choice, you can have it fixed by dawn. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Alvaro Herrera wrote: > Russell Smith wrote: > >> The issue is output, not input. >> >> SET datestyle='dmy'; >> SELECT '03-03-2004'::date >> >> Will return '2007-03-03', not 03-03-2004 as is the set datestyle. > > You are aware that DateStyle controls both input and output, > _separately_, yes? > No, I've RTFM'd to fix that. Thanks Russell
i apologize for bringing this up from over 2 years ago but i haven't been able to find how this issue was resolved. the following is from the ecmascript 5 specification at http://www.ecmascript.org/docs/tc39-2009-043.pdf page 168: > 15.9.1.15 Date Time String Format > ECMAScript defines a string interchange format for date-times based > upon a simplification of the ISO 8601 > Extended Format. The format is as follows: YYYY-MM-DDTHH:mm:ss.sssZ ecmascript 5 is the most recent specification for JavaScript and i would think that having a DATESTYLE format to simplify interoperability with JavaScript applications would be highly desirable. simplifying interoperability could be achieved by either providing a new format that matched this specific format or by allowing a way to specify a custom DATESTYLE format. being able to specify a custom DATESTYLE format would be preferred since it is the more flexible option. perhaps this is already possible but i just haven't managed to find it. any help appreciated. thanks, ben... On Aug 21, 2007, at 7:53 PM, Randolf Richardson wrote: > > The following bug has been logged online: > > Bug reference: 3563 > Logged by: Randolf Richardson > Email address: randolf+postgresql.org@inter-corporate.com > PostgreSQL version: 8.2.4 > Operating system: NetBSD 4 (beta), NetBSD 3.1, NetWare 6.5 > Description: DATESTYLE feature suggestion > Details: > > After convincing clients and colleagues to switch from Oracle (and > others) > to PostgreSQL, an issue that comes up is the need to customize > DATESTYLE. > Because this isn't possible, the developers who were against the > move to > PostgreSQL make it political and recommended work-around solutions > such as > using to_char() or implementing a view for each table that contain > TIMESTAMP[TZ]s is very difficult to argue with management because a > lot of > time is required to implement these items. > > In a future version, to solve this problem, an additional DATESTYLE > option > that uses the same rules as the to_char() function for date > formatting would > solve this problem. Here's an example: > > SET DATESTYLE = 'Custom YYYY-Mon-DD'; > > This feature would not only resolve this particular political > strife, but > would also solve many other problems, including simplifying coding > for raw > SQL output serving as reports (e.g., users still get confused about > dates > like "2007-06-03," wondering if they refer to June 3rd, or March 6th). > > I'm hoping that this suggestion will be an easy one to implement. > > Thanks in advance. > > P.S.: I searched around for a "feature suggestions" page but > couldn't find > it (if one exists, it should be linked to from the "Report a Bug" > page).
Hello 2010/5/16 Ben Hockey <neonstalwart@gmail.com>: > i apologize for bringing this up from over 2 years ago but i haven't been > able to find how this issue was resolved. it isn't bug, but request for new feature. look on http://wiki.postgresql.org/wiki/Developer_FAQ I have nothing against some new datestyles - xml, ecma5 and I am able to add to pg when hackers will agree Parametrised datestyle is little bit different. I know so it can be used for SQL injection on Oracle. So I am not sure if it is a good idea. But isn't problem create external project (maybe on pgFoundry) for customized datatype. Regards Pavel Stehule > > the following is from the ecmascript 5 specification at > http://www.ecmascript.org/docs/tc39-2009-043.pdf page 168: > >> 15.9.1.15 Date Time String Format >> ECMAScript defines a string interchange format for date-times based upon= a >> simplification of the ISO 8601 >> Extended Format. =C2=A0The format is as follows: YYYY-MM-DDTHH:mm:ss.sssZ > > > ecmascript 5 is the most recent specification for JavaScript and i would > think that having a DATESTYLE format to simplify interoperability with > JavaScript applications would be highly desirable. =C2=A0simplifying > interoperability could be achieved by either providing a new format that > matched this specific format or by allowing a way to specify a custom > DATESTYLE format. =C2=A0being able to specify a custom DATESTYLE format w= ould be > preferred since it is the more flexible option. > > perhaps this is already possible but i just haven't managed to find it. = =C2=A0any > help appreciated. > > thanks, > > ben... > > On Aug 21, 2007, at 7:53 PM, Randolf Richardson wrote: > >> >> The following bug has been logged online: >> >> Bug reference: =C2=A0 =C2=A0 =C2=A03563 >> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Randolf Richardson >> Email address: =C2=A0 =C2=A0 =C2=A0randolf+postgresql.org@inter-corporat= e.com >> PostgreSQL version: 8.2.4 >> Operating system: =C2=A0 NetBSD 4 (beta), NetBSD 3.1, NetWare 6.5 >> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0DATESTYLE feature suggestion >> Details: >> >> After convincing clients and colleagues to switch from Oracle (and other= s) >> to PostgreSQL, an issue that comes up is the need to customize DATESTYLE. >> Because this isn't possible, the developers who were against the move to >> PostgreSQL make it political and recommended work-around solutions such = as >> using to_char() or implementing a view for each table that contain >> TIMESTAMP[TZ]s is very difficult to argue with management because a lot = of >> time is required to implement these items. >> >> In a future version, to solve this problem, an additional DATESTYLE opti= on >> that uses the same rules as the to_char() function for date formatting >> would >> solve this problem. =C2=A0Here's an example: >> >> SET DATESTYLE =3D 'Custom YYYY-Mon-DD'; >> >> This feature would not only resolve this particular political strife, but >> would also solve many other problems, including simplifying coding for r= aw >> SQL output serving as reports (e.g., users still get confused about dates >> like "2007-06-03," wondering if they refer to June 3rd, or March 6th). >> >> I'm hoping that this suggestion will be an easy one to implement. >> >> Thanks in advance. >> >> P.S.: =C2=A0I searched around for a "feature suggestions" page but could= n't >> find >> it (if one exists, it should be linked to from the "Report a Bug" page). > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >