db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marl Atkins" <m...@softlinksys.com>
Subject RE: Date - Timestamp format for inserts?
Date Sun, 19 Nov 2006 23:30:28 GMT
A DATE field won't let me record the time will it? 

-----Original Message-----
From: Bernt.Johnsen@Sun.COM [mailto:Bernt.Johnsen@Sun.COM] 
Sent: Sunday, November 19, 2006 4:26 PM
To: Derby Discussion
Subject: Re: Date - Timestamp format for inserts?

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