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: pass (herse.apache.org: local policy)
Date: Sun, 19 Nov 2006 22:25:45 +0100
From: "Bernt M. Johnsen" <Bernt.Johnsen@Sun.COM>
Subject: Re: Date - Timestamp format for inserts?
In-reply-to: <4560B62C.3000708@amberpoint.com>
Sender: Bernt.Johnsen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <20061119212545.GA14666@localhost.localdomain>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: multipart/signed; boundary=EVF5PPMfhYS0aIcm;
 protocol="application/pgp-signature"; micalg=pgp-sha1
Content-disposition: inline
References: <0b8001c70c10$878cf340$6401a8c0@matkins>
 <4560B62C.3000708@amberpoint.com>
User-Agent: Mutt/1.5.9i


--EVF5PPMfhYS0aIcm
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

>>>>>>>>>>>> Bryan Pendleton wrote (2006-11-19 11:53:16):
> >What's wrong with this statement?
>=20
> Although the doc in=20
> http://db.apache.org/derby/docs/dev/ref/rrefsqlj27620.html
> appears to say that the minutes and seconds portions of the timestamp val=
ue=20
> can
> be ommitted, the code does not appear to conform to that behavior.
>=20
> So instead of '2006-09-10-00', use '2006-09-10-00.00.00' or '2006-09-10=
=20
> 00:00:00'.
>=20
> Hopefully that will be a reasonable solution to your problem for now.
>=20
> From what I see by reading through SQLTimestamp.parseDateOrTimestamp(), t=
he=20
> code
> intends for the minutes and seconds portions to be optional, but the=20
> implementation
> doesn't handle that, so my initial reaction is that the documentation is=
=20
> correct
> and this is a bug in the timestamp parser.
>=20
> What do others think? Is Derby supposed to accept '2006-09-10-00' as a va=
lid
> timestamp value?

I would definitely say no. The correct syntax is=20
'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?

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

--EVF5PPMfhYS0aIcm
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFYMvXlFBD9TXBAPARAqx8AJ9mpVRui0d/CF2CUA65nINVK81GGwCeLZpw
g4pT34wQYEd4P0i9ZAJBg8Q=
=xb3+
-----END PGP SIGNATURE-----

--EVF5PPMfhYS0aIcm--