ecmascript 5 DATESTYLE

hi,

i mistakenly had posted this to pgsql-bugs already and got a response
(see below - edited). i'm posting here since afaik it is the way i
should be requesting new features. my suggestion is to add a
DATESTYLE format to match the format specified for date time strings
in ecmascript 5.

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.

thanks,

ben...

On May 16, 2010, at 2:22 AM, Pavel Stehule wrote:
> I have nothing against some new datestyles - xml, ecma5 and I am able
> to add to pg when hackers will agree

On Tue, May 18, 2010 at 6:26 PM, Ben Hockey <neonstalwart(at)gmail(dot)com> wrote:
> hi,
> i mistakenly had posted this to pgsql-bugs already and got a response (see
> below - edited).  i'm posting here since afaik it is the way i should be
> requesting new features.  my suggestion is to add a DATESTYLE format to
> match the format specified for date time strings in ecmascript 5.
> 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.

I don't object, if someone wants to write a patch. I guess the
question is whether to keep adding named formats, or try to create a
general mechanism to allow the user to specify an arbitrary format, as
we do with to_char().

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

2010/5/19 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Tue, May 18, 2010 at 6:26 PM, Ben Hockey <neonstalwart(at)gmail(dot)com> wrote:
>> hi,
>> i mistakenly had posted this to pgsql-bugs already and got a response (see
>> below - edited).  i'm posting here since afaik it is the way i should be
>> requesting new features.  my suggestion is to add a DATESTYLE format to
>> match the format specified for date time strings in ecmascript 5.
>> 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.
>
> I don't object, if someone wants to write a patch.  I guess the
> question is whether to keep adding named formats, or try to create a
> general mechanism to allow the user to specify an arbitrary format, as
> we do with to_char().
>

I can write patch. I am against to general solution - It can be new
way for SQL injection.

Regards

Pavel

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote:
> 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.

Note that we haven't got any other datestyles that are intended to
support interoperability with some language. It is usually the job of
the client driver to convert PostgreSQL data (plural of datum) to the
appropriate type and format for the client environment or language. Is
there any reason why JavaScript would be different?

2010/5/19 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote:
>> 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.
>
> Note that we haven't got any other datestyles that are intended to
> support interoperability with some language.  It is usually the job of
> the client driver to convert PostgreSQL data (plural of datum) to the
> appropriate type and format for the client environment or language.  Is
> there any reason why JavaScript would be different?

JavaScript isn't special language, but JSON is wide used format for
interoperability. And same is true for XML datestyle format.

Regards
Pavel
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Pavel Stehule wrote:
> 2010/5/19 Peter Eisentraut <peter_e(at)gmx(dot)net>:
>
>> On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote:
>>
>>> 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.
>>>
>> Note that we haven't got any other datestyles that are intended to
>> support interoperability with some language. It is usually the job of
>> the client driver to convert PostgreSQL data (plural of datum) to the
>> appropriate type and format for the client environment or language. Is
>> there any reason why JavaScript would be different?
>>

I wouldn't be keen to see dedicated language specific handling of
date/datetime formats. It would lead to an explosion of functions with
new languages needing adding as and when their users jumped up and down
on us. However a generic format could be very useful and would give the
opportunity for people who need a language specific short cut the
opportunity to do a CREATE FUNCTION wrapping the generic one with a hard
coded format specifier.

Other platforms have generic support for this kind of task, for example
SQLServer: http://msdn.microsoft.com/en-us/library/ms187928.aspx. I
wouldn't recommend the SQLServer way, I think numeric format specifiers
are clumsy. Perhaps a mechanism like Java which is nicely summarized
here:
http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html

Pavel: Why do you believe a generic format function would lead to SQL
injections attacks?

> JavaScript isn't special language, but JSON is wide used format for
> interoperability. And same is true for XML datestyle format.
>
> Regards
> Pavel
>

I think that the postgres handling of those data types should handle the
date encoding themselves. For example, a XMLELEMENT call that was passed
a date would format the date string to the xs:date format (e.g.
2010-05-19) and when passed a timestamp format to xs:datetime (e.g.
2010-05-19T09:29:52+01:00). I would see the JSON handling as being no
different.

Thanks,

--
Mike Fowler
Registered Linux user: 379787

"I could be a genius if I just put my mind to it, and I,
I could do anything, if only I could get 'round to it"
-PULP 'Glory Days'

2010/5/19 Mike Fowler <mike(at)mlfowler(dot)com>:
> Pavel Stehule wrote:
>>
>> 2010/5/19 Peter Eisentraut <peter_e(at)gmx(dot)net>:
>>
>>>
>>> On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote:
>>>
>>>>
>>>> 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.
>>>>
>>>
>>> Note that we haven't got any other datestyles that are intended to
>>> support interoperability with some language.  It is usually the job of
>>> the client driver to convert PostgreSQL data (plural of datum) to the
>>> appropriate type and format for the client environment or language.  Is
>>> there any reason why JavaScript would be different?
>>>
>
> I wouldn't be keen to see dedicated language specific handling of
> date/datetime formats. It would lead to an explosion of functions with new
> languages needing adding as and when their users jumped up and down on us.
> However a generic format could be very useful and would give the opportunity
> for people who need a language specific short cut the opportunity to do a
> CREATE FUNCTION wrapping the generic one with a hard coded format specifier.
>
> Other platforms have generic support for this kind of task, for example
> SQLServer: http://msdn.microsoft.com/en-us/library/ms187928.aspx. I wouldn't
> recommend the SQLServer way, I think numeric format specifiers are clumsy.
> Perhaps a mechanism like Java which is nicely summarized here:
> http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html
>
> Pavel: Why do you believe a generic format function would lead to SQL
> injections attacks?

see google: lateral sql injection oracle NLS_DATE_FORMAT

I would to like this functionality too - and technically I don't see a
problem - It's less than 100 lines, but I don't need a new security
problem. So my proposal is change nothing on this integrated
functionality and add new custom date type - like cdate that can be
customized via GUC.

Regards
Pavel
>
>> JavaScript isn't special language, but JSON is wide used format for
>> interoperability. And same is true for XML datestyle format.
>>
>> Regards
>> Pavel
>>
>
> I think that the postgres handling of those data types should handle the
> date encoding themselves. For example, a XMLELEMENT call that was passed a
> date would format the date string to the xs:date format (e.g. 2010-05-19)
> and when passed a timestamp format to xs:datetime (e.g.
> 2010-05-19T09:29:52+01:00). I would see the JSON handling as being no
> different.
>
> Thanks,
>
> --
> Mike Fowler
> Registered Linux user: 379787
>
> "I could be a genius if I just put my mind to it, and I,
> I could do anything, if only I could get 'round to it"
> -PULP 'Glory Days'
>
>

Pavel Stehule wrote:
> see google: lateral sql injection oracle NLS_DATE_FORMAT
>
> I would to like this functionality too - and technically I don't see a
> problem - It's less than 100 lines, but I don't need a new security
> problem. So my proposal is change nothing on this integrated
> functionality and add new custom date type - like cdate that can be
> customized via GUC.
>
> Regards
> Pavel

OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf.
From the way I read this, the exploit relies on adjusting the
NLS_DATE_FORMAT to an arbitrary string which is then used for the
attack, To me this is easy to code against, simply lock the date format
right down and ensure that it is always controlled. IMHO I don't see an
Oracle specific attack as a reason why we can't have a generic format.
Surely we can learn from this known vulnerability and get another one up
on Oracle?

Thanks,

--
Mike Fowler
Registered Linux user: 379787

"I could be a genius if I just put my mind to it, and I,
I could do anything, if only I could get 'round to it"
-PULP 'Glory Days'

2010/5/19 Mike Fowler <mike(at)mlfowler(dot)com>:
> Pavel Stehule wrote:
>>
>> see google: lateral sql injection oracle NLS_DATE_FORMAT
>>
>> I would to like this functionality too - and technically I don't see a
>> problem - It's less than 100 lines, but I don't need a new security
>> problem. So my proposal is change nothing on this integrated
>> functionality and add new custom date type - like cdate that can be
>> customized via GUC.
>>
>> Regards
>> Pavel
>
> OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From
> the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to
> an arbitrary string which is then used for the attack, To me this is easy to
> code against, simply lock the date format right down and ensure that it is
> always controlled. IMHO I don't see an Oracle specific attack as a reason
> why we can't have a generic format. Surely we can learn from this known
> vulnerability and get another one up on Oracle?

I am not a security expert - you can simply don't allow apostrophe,
double quotes - but I am not sure, if this can be safe - simply - I am
abe to write this patch, but I am not able to ensure security.

Regards
Pavel
>
> Thanks,
>
> --
> Mike Fowler
> Registered Linux user: 379787
>
> "I could be a genius if I just put my mind to it, and I,
> I could do anything, if only I could get 'round to it"
> -PULP 'Glory Days'
>
>

Pavel Stehule wrote:
> 2010/5/19 Mike Fowler <mike(at)mlfowler(dot)com>:
>
>> Pavel Stehule wrote:
>>
>>> see google: lateral sql injection oracle NLS_DATE_FORMAT
>>>
>>> I would to like this functionality too - and technically I don't see a
>>> problem - It's less than 100 lines, but I don't need a new security
>>> problem. So my proposal is change nothing on this integrated
>>> functionality and add new custom date type - like cdate that can be
>>> customized via GUC.
>>>
>>> Regards
>>> Pavel
>>>
>> OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From
>> the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT to
>> an arbitrary string which is then used for the attack, To me this is easy to
>> code against, simply lock the date format right down and ensure that it is
>> always controlled. IMHO I don't see an Oracle specific attack as a reason
>> why we can't have a generic format. Surely we can learn from this known
>> vulnerability and get another one up on Oracle?
>>
>
> I am not a security expert - you can simply don't allow apostrophe,
> double quotes - but I am not sure, if this can be safe - simply - I am
> abe to write this patch, but I am not able to ensure security.
>
> Regards
> Pavel
>

Well you've rightly identified a potential security hole, so my
recommendation would be to put the patch together bearing in mind the
Oracle vulnerability. Once you've submitted the patch it can be reviewed
and we can ensure that you've managed to steer clear of introducing the
same/similar vulnerability into postgres.

Am I right in thinking that you're now proposing to do the generic patch
that Robert Haas and I prefer?

Thanks,

--
Mike Fowler
Registered Linux user: 379787

2010/5/19 Mike Fowler <mike(at)mlfowler(dot)com>:
> Pavel Stehule wrote:
>>
>> 2010/5/19 Mike Fowler <mike(at)mlfowler(dot)com>:
>>
>>>
>>> Pavel Stehule wrote:
>>>
>>>>
>>>> see google: lateral sql injection oracle NLS_DATE_FORMAT
>>>>
>>>> I would to like this functionality too - and technically I don't see a
>>>> problem - It's less than 100 lines, but I don't need a new security
>>>> problem. So my proposal is change nothing on this integrated
>>>> functionality and add new custom date type - like cdate that can be
>>>> customized via GUC.
>>>>
>>>> Regards
>>>> Pavel
>>>>
>>>
>>> OK I found www.databasesecurity.com/dbsec/lateral-sql-injection.pdf. From
>>> the way I read this, the exploit relies on adjusting the NLS_DATE_FORMAT
>>> to
>>> an arbitrary string which is then used for the attack, To me this is easy
>>> to
>>> code against, simply lock the date format right down and ensure that it
>>> is
>>> always controlled. IMHO I don't see an Oracle specific attack as a reason
>>> why we can't have a generic format. Surely we can learn from this known
>>> vulnerability and get another one up on Oracle?
>>>
>>
>> I am not a security expert - you can simply don't allow apostrophe,
>> double quotes - but I am not sure, if this can be safe - simply - I am
>> abe to write this patch, but I am not able to ensure security.
>>
>> Regards
>> Pavel
>>
>
> Well you've rightly identified a potential security hole, so my
> recommendation would be to put the patch together bearing in mind the Oracle
> vulnerability. Once you've submitted the patch it can be reviewed and we can
> ensure that you've managed to steer clear of introducing the same/similar
> vulnerability into postgres.
>
> Am I right in thinking that you're now proposing to do the generic patch
> that Robert Haas and I prefer?

I'll look on code and I'll see

Pavel

>
> Thanks,
>
> --
> Mike Fowler
> Registered Linux user: 379787
>
>

On May 19, 2010, at 4:31 AM, Mike Fowler wrote:

> Pavel Stehule wrote:
>> 2010/5/19 Peter Eisentraut <peter_e(at)gmx(dot)net>:
>>
>>> On tis, 2010-05-18 at 18:26 -0400, Ben Hockey wrote:
>>>
>>>> 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.
>>>>
>>> Note that we haven't got any other datestyles that are intended to
>>> support interoperability with some language. It is usually the
>>> job of
>>> the client driver to convert PostgreSQL data (plural of datum) to
>>> the
>>> appropriate type and format for the client environment or
>>> language. Is
>>> there any reason why JavaScript would be different?
>>>
>
> I wouldn't be keen to see dedicated language specific handling of
> date/datetime formats. It would lead to an explosion of functions
> with new languages needing adding as and when their users jumped up
> and down on us. However a generic format could be very useful and
> would give the opportunity for people who need a language specific
> short cut the opportunity to do a CREATE FUNCTION wrapping the
> generic one with a hard coded format specifier.
>
> Other platforms have generic support for this kind of task, for
> example SQLServer: http://msdn.microsoft.com/en-us/library/ms187928.aspx
> . I wouldn't recommend the SQLServer way, I think numeric format
> specifiers are clumsy. Perhaps a mechanism like Java which is nicely
> summarized here: http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html

i think that http://unicode.org/reports/tr35/#Date_Format_Patterns is
very similar to (maybe the same as) the java simple date format but
the unicode link gives a more complete explanation of exactly how the
formatters will be interpreted - ie y will represent the full
representation of the year but yy will represent the 2 digit form of
the year, etc.. just thought i'd share the reference since it
provides a powerful way to generically specify date formats and is
possibly something that many people might already be familiar with.

thanks for looking into adding this feature. custom formats for
parsing and formatting of dates would certainly be the better option
if it can be done securely.

thanks,

ben..

On Thu, May 20, 2010 at 9:25 PM, Ben Hockey <neonstalwart(at)gmail(dot)com> wrote:
> thanks for looking into adding this feature.  custom formats for parsing and
> formatting of dates would certainly be the better option if it can be done
> securely.

Well, Pavel expressed a concern about SQL injection, but I can't see
why that would be a problem. If having multiple date formats is
insecure, then we are already insecure. If it's not, then I don't see
why having user-definable formats would be any more insecure than
having formats from a fixed list. In any case, I can't see the
connection to SQL injection - it seems like the worst case scenario is
that some client gets confused about what the date format is and some
dates get misinterpreted.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

i know its been over a year without any activity on this thread but did
anything ever come of this? i'd really like to be able to get dates to
match the format specified for date time strings in ecmascript 5. a
generic way to specify the format would be ideal if it can be done
securely. has there been other threads discussing this more recently?

thanks,

ben...

On Wed, May 19, 2010 at 6:21 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:
>
>
> I am not a security expert - you can simply don't allow apostrophe,
> double quotes - but I am not sure, if this can be safe - simply - I am
> abe to write this patch, but I am not able to ensure security.
>
> Regards
> Pavel

On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey <neonstalwart(at)gmail(dot)com> wrote:
> i know its been over a year without any activity on this thread but did
> anything ever come of this?  i'd really like to be able to get dates to
> match the format specified for date time strings in ecmascript 5.  a generic
> way to specify the format would be ideal if it can be done securely.  has
> there been other threads discussing this more recently?

Not to my knowledge, though I don't read pgsql-general. I think this
is the sort of thing that really only gets done if someone cares
enough about it to settle down and put together a detailed design
proposal, get consensus, and write a patch. IOW, it's unlikely that
anyone else will do this for you, but you can certainly make a try at
doing it yourself, and get help from others along the way.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey <neonstalwart(at)gmail(dot)com> wrote:
>> i know its been over a year without any activity on this thread but did
>> anything ever come of this? i'd really like to be able to get dates to
>> match the format specified for date time strings in ecmascript 5. a generic
>> way to specify the format would be ideal if it can be done securely. has
>> there been other threads discussing this more recently?

> Not to my knowledge, though I don't read pgsql-general. I think this
> is the sort of thing that really only gets done if someone cares
> enough about it to settle down and put together a detailed design
> proposal, get consensus, and write a patch. IOW, it's unlikely that
> anyone else will do this for you, but you can certainly make a try at
> doing it yourself, and get help from others along the way.

TBH, I think that inventing a new datestyle setting "ECMA" would be a
more appropriate investment of effort. Generic format strings sound
like a nightmare. Maybe I've just been turned off by the
to_date/to_char mess, but I'm very down on the idea of anything like
that propagating into the main datetime I/O code.

regards, tom lane

2011/12/6 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey <neonstalwart(at)gmail(dot)com> wrote:
>>> i know its been over a year without any activity on this thread but did
>>> anything ever come of this?  i'd really like to be able to get dates to
>>> match the format specified for date time strings in ecmascript 5.  a generic
>>> way to specify the format would be ideal if it can be done securely.  has
>>> there been other threads discussing this more recently?
>
>> Not to my knowledge, though I don't read pgsql-general.  I think this
>> is the sort of thing that really only gets done if someone cares
>> enough about it to settle down and put together a detailed design
>> proposal, get consensus, and write a patch.  IOW, it's unlikely that
>> anyone else will do this for you, but you can certainly make a try at
>> doing it yourself, and get help from others along the way.
>
> TBH, I think that inventing a new datestyle setting "ECMA" would be a
> more appropriate investment of effort.  Generic format strings sound
> like a nightmare.  Maybe I've just been turned off by the
> to_date/to_char mess, but I'm very down on the idea of anything like
> that propagating into the main datetime I/O code.
>

I am for ECMA datestyle

it is there but just is not public, if I remember well

Theoretically some custom output/input transform routine can be very
interesting - for domains, for boolean type - but on second hand - the
usage of this feature is minimal and there is risk for less advanced
users - so ECMA datestyle is very adequate solution.

Regards

Pavel

>                        regards, tom lane

On 12/6/2011 3:20 PM, Pavel Stehule wrote:
>
> I am for ECMA datestyle
>
> it is there but just is not public, if I remember well
>
> Theoretically some custom output/input transform routine can be very
> interesting - for domains, for boolean type - but on second hand - the
> usage of this feature is minimal and there is risk for less advanced
> users - so ECMA datestyle is very adequate solution.
>
> Regards
>
> Pavel
>
i don't particularly need anything other than ECMA datestyle - i was
just under the impression that a more generic solution was preferred.
so, ECMA is enough to stop me from making any more noise about this.

pavel, is there a way i can use this currently? if not, would it take
much effort to make this public?

thanks,

ben...

2011/12/6 ben hockey <neonstalwart(at)gmail(dot)com>:
>
> On 12/6/2011 3:20 PM, Pavel Stehule wrote:
>>
>>
>> I am for ECMA datestyle
>>
>> it is there but just is not public, if I remember well
>>
>> Theoretically some custom output/input transform routine can be very
>> interesting - for domains, for boolean type - but on second hand - the
>> usage of this feature is minimal and there is risk for less advanced
>> users - so ECMA datestyle is very adequate solution.
>>
>> Regards
>>
>> Pavel
>>
> i don't particularly need anything other than ECMA datestyle - i was just
> under the impression that a more generic solution was preferred.  so, ECMA
> is enough to stop me from making any more noise about this.
>
> pavel, is there a way i can use this currently?  if not, would it take much
> effort to make this public?

I am not sure, if this patch is 100% correct

but it does something

the name is not ECMA but XSD - I hope, so both formats are same

postgres=# set datestyle TO 'XSD';
SET
postgres=# select current_timestamp;
now
──────────────────────────────────
2011-12-06T21:50:34.142933+01:00
(1 row)

postgres=# select '2011-12-06T22:46:53.455866+01:00'::timestamp;
timestamp
────────────────────────────
2011-12-06T22:46:53.455866
(1 row)

but maybe this will be some more, if XSD format is not exact ECMA

Regards

Pavel

>
> thanks,
>
> ben...

2011/12/6 ben hockey <neonstalwart(at)gmail(dot)com>:
>
> On 12/6/2011 3:20 PM, Pavel Stehule wrote:
>>
>>
>> I am for ECMA datestyle
>>
>> it is there but just is not public, if I remember well
>>
>> Theoretically some custom output/input transform routine can be very
>> interesting - for domains, for boolean type - but on second hand - the
>> usage of this feature is minimal and there is risk for less advanced
>> users - so ECMA datestyle is very adequate solution.
>>
>> Regards
>>
>> Pavel
>>
> i don't particularly need anything other than ECMA datestyle - i was just
> under the impression that a more generic solution was preferred.  so, ECMA
> is enough to stop me from making any more noise about this.

a general solution is not simple - there is possible a SQL injection
and therefore result must be escaped, and it means some overhead

else - is very common a good style to use functions to_char, to_date
or to_timestamp functions. Then your application will be more robust.
Using default datestyle is user friendly technique, but it can be
source of some issues - is better don't use it for large and complex
application.

Regards

Pavel

>
> pavel, is there a way i can use this currently?  if not, would it take much
> effort to make this public?
>
> thanks,
>
> ben...

On 12/6/2011 3:53 PM, Pavel Stehule wrote:
> I am not sure, if this patch is 100% correct
>
> but it does something
>
> the name is not ECMA but XSD - I hope, so both formats are same

that format works for me. in fact a simple test to see if it would do
what i hope for would be to open the developer console (firebug, web
developer, etc) of a browser and take the formatted output and pass it
to the Date constructor - eg using chrome:

> var a = new Date('2011-12-06T22:46:53.455866+01:00');
Date
> a.toDateString();
"Tue Dec 06 2011"
> a.toTimeString();
"16:46:53 GMT-0500 (Eastern Standard Time)"

you can see that the Date was properly created with the time converted
to my local time zone. this would be of great help to anyone passing
data from postrgres to a web browser since it eliminates the need to
have to transform the format somewhere between the database and the browser.

i'm not familiar with the process of getting this feature added to
postgres - what needs to happen now?

ben...

2011/12/6 ben hockey <neonstalwart(at)gmail(dot)com>:
>
>
> On 12/6/2011 3:53 PM, Pavel Stehule wrote:
>>
>> I am not sure, if this patch is 100% correct
>>
>> but it does something
>>
>> the name is not ECMA but XSD - I hope, so both formats are same
>
>
> that format works for me.  in fact a simple test to see if it would do what
> i hope for would be to open the developer console (firebug, web developer,
> etc) of a browser and take the formatted output and pass it to the Date
> constructor - eg using chrome:
>
>> var a = new Date('2011-12-06T22:46:53.455866+01:00');
>      Date
>> a.toDateString();
>      "Tue Dec 06 2011"
>> a.toTimeString();
>      "16:46:53 GMT-0500 (Eastern Standard Time)"
>
> you can see that the Date was properly created with the time converted to my
> local time zone.  this would be of great help to anyone passing data from
> postrgres to a web browser since it eliminates the need to have to transform
> the format somewhere between the database and the browser.
>
> i'm not familiar with the process of getting this feature added to postgres
> - what needs to happen now?

it can be in 9.2 (if will be accepted) - it will be release at summer 2012

http://wiki.postgresql.org/wiki/Submitting_a_Patch

Regards

Pavel Stehule

>
> ben...

On 12/6/2011 4:19 PM, Pavel Stehule wrote:
> it can be in 9.2 (if will be accepted) - it will be release at summer 2012
>
> http://wiki.postgresql.org/wiki/Submitting_a_Patch
>
> Regards
>
> Pavel Stehule

ok, so i assume your patch is now considered "submitted" and is waiting
to be reviewed. i'll wait to see what happens.

thanks,

ben...

2011/12/6 ben hockey <neonstalwart(at)gmail(dot)com>:
>
>
> On 12/6/2011 4:19 PM, Pavel Stehule wrote:
>>
>> it can be in 9.2 (if will be accepted) - it will be release at summer 2012
>>
>> http://wiki.postgresql.org/wiki/Submitting_a_Patch
>>
>> Regards
>>
>> Pavel Stehule
>
>
> ok, so i assume your patch is now considered "submitted" and is waiting to
> be reviewed.  i'll wait to see what happens.

not yet :)

there should be proposal, and maybe more hacking - documentation is
missing, there are no regression tests. It needs half of day.

Patch that I sent you is just VIP patch

Regards

Pavel

>
> thanks,
>
> ben...

i may have spoken a little too soon about the format being right... i
just took a look at the postgres source code and it would need one more
change to completely meet my needs. EncodeDateTime should put a 'Z' for
UTC timezone rather than '+0'. with this being the case, do you think
there would need to be an ECMA datestyle or would XSD be compatible with
this change?

i haven't touched any c code in quite a while but the changes look
simple enough that i could work towards an ECMA patch if that's the best
way to go about this.

thanks,

ben...

Hello

2011/12/6 ben hockey <neonstalwart(at)gmail(dot)com>:
> i may have spoken a little too soon about the format being right...  i just
> took a look at the postgres source code and it would need one more change to
> completely meet my needs.  EncodeDateTime should put a 'Z' for UTC timezone
> rather than '+0'.  with this being the case, do you think there would need
> to be an ECMA datestyle or would XSD be compatible with this change?
>

probably we can't to change a XSD format - but new format, that is
exactly ECMA should not be problem.

> i haven't touched any c code in quite a while but the changes look simple
> enough that i could work towards an ECMA patch if that's the best way to go
> about this.

you can become a postgreql's hacker :) - anybody starts on simple jobs

PostgreSQL hacking is good lecture

Pavel

>
> thanks,
>
> ben...

On tis, 2011-12-06 at 15:15 -0500, Tom Lane wrote:
> TBH, I think that inventing a new datestyle setting "ECMA" would be a
> more appropriate investment of effort.

So we'd have a setting called "ECMA" that's really ISO, and a setting
called "ISO" that's really SQL, and a setting called "SQL" that's really
Postgres, and a setting called "Postgres" that's also Postgres but
different.

On Dec 7, 2011, at 3:56 PM, Peter Eisentraut wrote:

> On tis, 2011-12-06 at 15:15 -0500, Tom Lane wrote:
>> TBH, I think that inventing a new datestyle setting "ECMA" would be a
>> more appropriate investment of effort.
>
> So we'd have a setting called "ECMA" that's really ISO, and a setting
> called "ISO" that's really SQL, and a setting called "SQL" that's
> really
> Postgres, and a setting called "Postgres" that's also Postgres but
> different.
>

...and a setting called "XSD" that's also ISO.

for now i'm backing away from the ECMA option - what i was thinking of
would be exactly the same as "XSD" except rather than a timezone of
'+00:00' it would be a 'Z'. from some quick searching, it seems that
XSD should be capable of understanding 'Z' rather than '+00:00' so if
i was going to do anything i'd work towards making that change to 'XSD'.

however, as it turns out, the constraint i have that is requiring me
to use 'Z' is not actually from ECMAScript 5 but from json-schema (http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.23
). XSD is fully compatible with ECMAScript 5 date time string format (http://es5.github.com/#x15.9.1.15
) so i'm going to sit on this again for a little while and think some
more. maybe try to convince json-schema to relax their definition of
date-time format.

i'll be back when i have a clear picture of what i think makes the
most sense.

thanks,

ben...

2011/12/7 ben hockey <neonstalwart(at)gmail(dot)com>:
>
> On Dec 7, 2011, at 3:56 PM, Peter Eisentraut wrote:
>
> On tis, 2011-12-06 at 15:15 -0500, Tom Lane wrote:
>
> TBH, I think that inventing a new datestyle setting "ECMA" would be a
>
> more appropriate investment of effort.
>
>
> So we'd have a setting called "ECMA" that's really ISO, and a setting
> called "ISO" that's really SQL, and a setting called "SQL" that's really
> Postgres, and a setting called "Postgres" that's also Postgres but
> different.
>
>
> ...and a setting called "XSD" that's also ISO.
>
> for now i'm backing away from the ECMA option - what i was thinking of would
> be exactly the same as "XSD" except rather than a timezone of '+00:00' it
> would be a 'Z'.  from some quick searching, it seems that XSD should be
> capable of understanding 'Z' rather than '+00:00' so if i was going to do
> anything i'd work towards making that change to 'XSD'.
>
> however, as it turns out, the constraint i have that is requiring me to use
> 'Z' is not actually from ECMAScript 5 but from json-schema
> (http://tools.ietf.org/html/draft-zyp-json-schema-03#section-5.23).  XSD is
> fully compatible with ECMAScript 5 date time string format
> (http://es5.github.com/#x15.9.1.15)  so i'm going to sit on this again for a
> little while and think some more.  maybe try to convince json-schema to
> relax their definition of date-time format.
>
> i'll be back when i have a clear picture of what i think makes the most
> sense.

please do it - we still would to have JSON support, so date style can
be processed together

Regards

Pavel

>
> thanks,
>
> ben...