Thread: date/time formats in 7.2

For 7.2, to support some ISO-8601 variants, I'm tightening up the date
delimiter parsing to require the same delimiter to be used between all
parts of a date.

Does anyone use the German date notation for PostgreSQL? If so, what is
the actual format you input? The reasons I'm asking are:

o I had recalled that the format was "dd.mm/yyyy", but actually
PostgreSQL emits "dd.mm.yyyy".

o By tightening up the parsing, "dd.mm.yyyy" would be accepted, but
"dd.mm/yyyy", "yyyy.mm-dd", etc would not.

o The stricter parsing in this area would allow more general parsing
elsewhere, enabling other variants such as

  yyyymmddThhmmss
  yyyymmdd hhmmss.ss-zz
  Thhmmss-zz

With these changes, more formats should be correctly handled, including
some edge cases which should have worked but seemed not to; the current
regression tests still all pass. As an example of edge case troubles,
7.1 accepts both of the following:

  timestamp '2001-12-27 04:05:06-08'
  timestamp '2001-12-27 040506 -08'

But rejects the latter if the space before the time zone is removed:

  timestamp '2001-12-27 040506-08'

Comments? Suggestions?

                      - Thomas

The one variant I'd like it to accept is 'yyyy-mm-ddThh:nn:ss', as in
'2001-12-31T13:30:46'. This is the ISO-8601 format that perl's Time::Piece and I
think at least one other perl date module use as their 'standard ISO' output
format, and the default format that's closest to what Postgresql 7.1.3 seems to
accept. For now, I'm taking  a string that looks like the above, replacing the T
with a space with Perl, and then inserting the value into Postgresql. Not a huge
deal, of course, but it might make it a little more convenient for some of us.
Thanks,

Wes Sheldahl



Thomas Lockhart <lockhart%fourpalms.org@interlock.lexmark.com> on 12/28/2001
10:39:42 PM

Please respond to lockhart%fourpalms.org@interlock.lexmark.com

To:   Hackers List <pgsql-hackers%postgresql.org@interlock.lexmark.com>, General
      Postgres List <pgsql-general%postgresql.org@interlock.lexmark.com>
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] date/time formats in 7.2


For 7.2, to support some ISO-8601 variants, I'm tightening up the date
delimiter parsing to require the same delimiter to be used between all
parts of a date.

Does anyone use the German date notation for PostgreSQL? If so, what is
the actual format you input? The reasons I'm asking are:

o I had recalled that the format was "dd.mm/yyyy", but actually
PostgreSQL emits "dd.mm.yyyy".

o By tightening up the parsing, "dd.mm.yyyy" would be accepted, but
"dd.mm/yyyy", "yyyy.mm-dd", etc would not.

o The stricter parsing in this area would allow more general parsing
elsewhere, enabling other variants such as

  yyyymmddThhmmss
  yyyymmdd hhmmss.ss-zz
  Thhmmss-zz

With these changes, more formats should be correctly handled, including
some edge cases which should have worked but seemed not to; the current
regression tests still all pass. As an example of edge case troubles,
7.1 accepts both of the following:

  timestamp '2001-12-27 04:05:06-08'
  timestamp '2001-12-27 040506 -08'

But rejects the latter if the space before the time zone is removed:

  timestamp '2001-12-27 040506-08'

Comments? Suggestions?

                      - Thomas

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> The one variant I'd like it to accept is 'yyyy-mm-ddThh:nn:ss', as in
> '2001-12-31T13:30:46'. This is the ISO-8601 format that perl's Time::Piece and I
> think at least one other perl date module use as their 'standard ISO' output
> format, and the default format that's closest to what Postgresql 7.1.3 seems to
> accept. For now, I'm taking  a string that looks like the above, replacing the T
> with a space with Perl, and then inserting the value into Postgresql. Not a huge
> deal, of course, but it might make it a little more convenient for some of us.

Already accomodated in 7.2.

                  - Thomas