FAQ
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

Search Discussions

  • Robert Haas at May 19, 2010 at 4:20 am

    On Tue, May 18, 2010 at 6:26 PM, Ben Hockey 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
  • Pavel Stehule at May 19, 2010 at 5:28 am

    2010/5/19 Robert Haas <robertmhaas@gmail.com>:
    On Tue, May 18, 2010 at 6:26 PM, Ben Hockey 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
  • Peter Eisentraut at May 19, 2010 at 6:19 am

    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?
  • Pavel Stehule at May 19, 2010 at 6:26 am

    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
  • Mike Fowler at May 19, 2010 at 8:32 am

    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'
  • Pavel Stehule at May 19, 2010 at 8:54 am

    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'
  • Mike Fowler at May 19, 2010 at 10:15 am

    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'
  • Pavel Stehule at May 19, 2010 at 10:21 am

    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'
  • Mike Fowler at May 19, 2010 at 10:37 am

    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
  • Pavel Stehule at May 19, 2010 at 10:43 am

    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
  • Ben Hockey at Dec 6, 2011 at 6:12 pm
    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 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
  • Robert Haas at Dec 6, 2011 at 8:09 pm

    On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey 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
  • Tom Lane at Dec 6, 2011 at 8:16 pm

    Robert Haas writes:
    On Tue, Dec 6, 2011 at 1:11 PM, Ben Hockey 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
  • Pavel Stehule at Dec 6, 2011 at 8:21 pm

    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 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
  • Ben hockey at Dec 6, 2011 at 8:26 pm

    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...
  • Pavel Stehule at Dec 6, 2011 at 8:54 pm
    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...
  • Ben hockey at Dec 6, 2011 at 9:11 pm

    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...
  • Pavel Stehule at Dec 6, 2011 at 9:20 pm
    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...
  • Ben hockey at Dec 6, 2011 at 9:24 pm

    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...
  • Pavel Stehule at Dec 6, 2011 at 9:29 pm
    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...
  • Ben hockey at Dec 6, 2011 at 10:15 pm
    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...
  • Pavel Stehule at Dec 6, 2011 at 10:25 pm
    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...
  • Pavel Stehule at Dec 6, 2011 at 8:59 pm
    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...
  • Peter Eisentraut at Dec 7, 2011 at 8:56 pm

    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.
  • Ben hockey at Dec 7, 2011 at 9:11 pm

    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...
  • Pavel Stehule at Dec 7, 2011 at 9:41 pm
    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...
  • Ben Hockey at May 21, 2010 at 1:25 am

    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..
  • Robert Haas at May 21, 2010 at 3:34 am

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMay 18, '10 at 10:26p
activeDec 7, '11 at 9:41p
posts29
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase