db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Date - Timestamp format for inserts?
Date Sun, 19 Nov 2006 21:25:45 GMT
>>>>>>>>>>>> Bryan Pendleton wrote (2006-11-19 11:53:16):
> >What's wrong with this statement?
> 
> Although the doc in 
> http://db.apache.org/derby/docs/dev/ref/rrefsqlj27620.html
> appears to say that the minutes and seconds portions of the timestamp value 
> can
> be ommitted, the code does not appear to conform to that behavior.
> 
> So instead of '2006-09-10-00', use '2006-09-10-00.00.00' or '2006-09-10 
> 00:00:00'.
> 
> Hopefully that will be a reasonable solution to your problem for now.
> 
> From what I see by reading through SQLTimestamp.parseDateOrTimestamp(), the 
> code
> intends for the minutes and seconds portions to be optional, but the 
> implementation
> doesn't handle that, so my initial reaction is that the documentation is 
> correct
> and this is a bug in the timestamp parser.
> 
> What do others think? Is Derby supposed to accept '2006-09-10-00' as a valid
> timestamp value?

I would definitely say no. The correct syntax is 
'2006-09-10 00:00:00'.

I think that we should stick to the SQL spec and JDBC spec which
again is built upon ISO 8601 (An ok summary is found here
http://www.cl.cam.ac.uk/~mgk25/iso-time.html)

Dates should the use "-" as a separator and times should use ":" and
"." between seconds and fractions of seconds. SQL and JDBC uses " " as
a separator between date and time in timestamp while ISO 8601
specifies "T", so it would be an idea to let Derby accept both
'2006-09-10 00:00:00' and '2006-09-10T00:00:00'.

All applications built on top of JDBC should, however ise JDBC's
escape syntax for maximum portabiliety: {ts '2006-09-10 00:00:00'}


BTW: Why use TIMESTAMP for a date column? Wouldn't DATE be more
appropriate?

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message