Thread

1. ecmascript 5 DATESTYLE

   ben hockey <neonstalwart@gmail.com> — 2010-05-18T22:26:18Z

   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
   
   
   

2. Re: ecmascript 5 DATESTYLE

   Robert Haas <robertmhaas@gmail.com> — 2010-05-19T04:20:51Z

   On Tue, May 18, 2010 at 6:26 PM, Ben Hockey <neonstalwart@gmail.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
   
   

3. Re: ecmascript 5 DATESTYLE

   Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-19T05:28:23Z

   2010/5/19 Robert Haas <robertmhaas@gmail.com>:
   > On Tue, May 18, 2010 at 6:26 PM, Ben Hockey <neonstalwart@gmail.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@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-hackers
   >
   
   

4. Re: ecmascript 5 DATESTYLE

   Peter Eisentraut <peter_e@gmx.net> — 2010-05-19T06:19:35Z

   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?
   
   
   

5. Re: ecmascript 5 DATESTYLE

   Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-19T06:25:44Z

   2010/5/19 Peter Eisentraut <peter_e@gmx.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@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-hackers
   >
   
   

6. Re: ecmascript 5 DATESTYLE

   Mike Fowler <mike@mlfowler.com> — 2010-05-19T08:31:44Z

   Pavel Stehule wrote:
   > 2010/5/19 Peter Eisentraut <peter_e@gmx.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'
   
   
   

7. Re: ecmascript 5 DATESTYLE

   Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-19T08:54:05Z

   2010/5/19 Mike Fowler <mike@mlfowler.com>:
   > Pavel Stehule wrote:
   >>
   >> 2010/5/19 Peter Eisentraut <peter_e@gmx.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'
   >
   >
   
   

8. Re: ecmascript 5 DATESTYLE

   Mike Fowler <mike@mlfowler.com> — 2010-05-19T10:15:10Z

   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'
   
   
   

9. Re: ecmascript 5 DATESTYLE

   Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-19T10:21:20Z

   2010/5/19 Mike Fowler <mike@mlfowler.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'
   >
   >
   
   

10. Re: ecmascript 5 DATESTYLE

    Mike Fowler <mike@mlfowler.com> — 2010-05-19T10:37:04Z

    Pavel Stehule wrote:
    > 2010/5/19 Mike Fowler <mike@mlfowler.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
    
    
    

11. Re: ecmascript 5 DATESTYLE

    Pavel Stehule <pavel.stehule@gmail.com> — 2010-05-19T10:43:12Z

    2010/5/19 Mike Fowler <mike@mlfowler.com>:
    > Pavel Stehule wrote:
    >>
    >> 2010/5/19 Mike Fowler <mike@mlfowler.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
    >
    >
    
    

12. Re: ecmascript 5 DATESTYLE

    ben hockey <neonstalwart@gmail.com> — 2010-05-21T01:25:26Z

    On May 19, 2010, at 4:31 AM, Mike Fowler wrote:
    
    > Pavel Stehule wrote:
    >> 2010/5/19 Peter Eisentraut <peter_e@gmx.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..
    
    

13. Re: ecmascript 5 DATESTYLE

    Robert Haas <robertmhaas@gmail.com> — 2010-05-21T03:34:41Z

    On Thu, May 20, 2010 at 9:25 PM, Ben Hockey <neonstalwart@gmail.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
    
    

14. Re: ecmascript 5 DATESTYLE

    ben hockey <neonstalwart@gmail.com> — 2011-12-06T18:11:54Z

    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.stehule@gmail.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
    

15. Re: ecmascript 5 DATESTYLE

    Robert Haas <robertmhaas@gmail.com> — 2011-12-06T20:09:31Z

    On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey <neonstalwart@gmail.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
    
    

16. Re: ecmascript 5 DATESTYLE

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-12-06T20:15:55Z

    Robert Haas <robertmhaas@gmail.com> writes:
    > On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey <neonstalwart@gmail.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
    
    

17. Re: ecmascript 5 DATESTYLE

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-12-06T20:20:54Z

    2011/12/6 Tom Lane <tgl@sss.pgh.pa.us>:
    > Robert Haas <robertmhaas@gmail.com> writes:
    >> On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey <neonstalwart@gmail.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
    
    

18. Re: ecmascript 5 DATESTYLE

    ben hockey <neonstalwart@gmail.com> — 2011-12-06T20:26:12Z

    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...
    
    

19. Re: ecmascript 5 DATESTYLE

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-12-06T20:53:05Z

    2011/12/6 ben hockey <neonstalwart@gmail.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...
    

20. Re: ecmascript 5 DATESTYLE

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-12-06T20:58:41Z

    2011/12/6 ben hockey <neonstalwart@gmail.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...
    
    

21. Re: ecmascript 5 DATESTYLE

    ben hockey <neonstalwart@gmail.com> — 2011-12-06T21:11:14Z

    
    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...
    
    

22. Re: ecmascript 5 DATESTYLE

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-12-06T21:19:35Z

    2011/12/6 ben hockey <neonstalwart@gmail.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...
    
    

23. Re: ecmascript 5 DATESTYLE

    ben hockey <neonstalwart@gmail.com> — 2011-12-06T21:23:30Z

    
    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...
    
    

24. Re: ecmascript 5 DATESTYLE

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-12-06T21:28:39Z

    2011/12/6 ben hockey <neonstalwart@gmail.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...
    
    

25. Re: ecmascript 5 DATESTYLE

    ben hockey <neonstalwart@gmail.com> — 2011-12-06T22:15:16Z

    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...
    
    

26. Re: ecmascript 5 DATESTYLE

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-12-06T22:24:50Z

    Hello
    
    2011/12/6 ben hockey <neonstalwart@gmail.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...
    
    

27. Re: ecmascript 5 DATESTYLE

    Peter Eisentraut <peter_e@gmx.net> — 2011-12-07T20:56:02Z

    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.
    
    
    
    

28. Re: ecmascript 5 DATESTYLE

    ben hockey <neonstalwart@gmail.com> — 2011-12-07T21:11:37Z

    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...

29. Re: ecmascript 5 DATESTYLE

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-12-07T21:40:34Z

    2011/12/7 ben hockey <neonstalwart@gmail.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...