BUG #3563: DATESTYLE feature suggestion

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:

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