Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (herse.apache.org: local policy)
From: "Marl Atkins" <marl@softlinksys.com>
To: "'Derby Discussion'" <derby-user@db.apache.org>
Subject: RE: Date - Timestamp format for inserts?
Date: Sun, 19 Nov 2006 18:30:28 -0500
Message-ID: <0bd401c70c32$b2d96c00$6401a8c0@matkins>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AccMIlzgUytyOkebTiqR7qN2v9xWpAAEEinA
In-Reply-To: <20061119212545.GA14666@localhost.localdomain>

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