Date - Timestamp format for inserts?

[Marl Atkins]

Hi:

I can't figure this out.

I'm getting an error that says:
'The syntax of the string representation of a datetime value is incorrect.'

I've tried 4 or 5 different formats. It doesn't seem to like anything.
The only 2 fields that are Date fields are DateCreated and DateExpired.
They're taken from a MySql DATETIME datatype but I'm not sure what they
convert to in Derby.

What's wrong with this statement?
INSERT INTO users
(RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr2,City,S
tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,AccessLeve
lID,DateCreated,CreatedBy,DateExpired ) VALUES(1,NULL,'SoftLink Systems,
Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,'marl','marl',0,1,'2006-09-10-00',1,'2050-01-01-00' )

Marl K. Atkins
Microsoft Certified Professional
SoftLink Systems, Inc.
[phone]

[Bryan Pendleton]

Post by Marl Atkins
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?

The documentation in question appears to have been added as part of DERBY-234:
http://issues.apache.org/jira/browse/DERBY-234

I've attached some simple experiments I tried, below.

thanks,

bryan

ij> create table tmstp (c1 timestamp);
0 rows inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22 10:00:00');
1 row inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22-11');
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
ij> insert into tmstp values ('1990-03-22 11');
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
ij> insert into tmstp values ('1990-03-22 11.00');
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
ij> insert into tmstp values ('1990-03-22 11.00.00');
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
ij> insert into tmstp values ('1990-03-22-11.00.00');
1 row inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22-11.00');
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
ij> insert into tmstp values ('1990-03-22-11.00');
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
ij> insert into tmstp values ('1990-03-22-11.00.');
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.

[Marl Atkins]

Yep, that did it. Thanks.


-----Original Message-----
From: Bryan Pendleton [mailto:bpendleton-***@public.gmane.org]
Sent: Sunday, November 19, 2006 2:53 PM
To: Derby Discussion
Subject: Re: Date - Timestamp format for inserts?

Post by Marl Atkins
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?

The documentation in question appears to have been added as part of
DERBY-234:
http://issues.apache.org/jira/browse/DERBY-234

I've attached some simple experiments I tried, below.

thanks,

bryan

ij> create table tmstp (c1 timestamp);
0 rows inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22 10:00:00');
1 row inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22-11');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22 11');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22 11.00');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22 11.00.00');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22-11.00.00');
1 row inserted/updated/deleted
ij> insert into tmstp values ('1990-03-22-11.00');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22-11.00');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> insert into tmstp values ('1990-03-22-11.00.');
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.

[Bernt M. Johnsen]

Post by Bryan Pendleton

Post by Marl Atkins
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?

[Marl Atkins]

A DATE field won't let me record the time will it?

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

Post by Bryan Pendleton

Post by Marl Atkins
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

[Bernt M. Johnsen]

Post by Marl Atkins
A DATE field won't let me record the time will it?

Of course not. But the value '2006-09-10-00' led me to believe that
the time was of no importance to the datum.

[Marl Atkins]

Yea, you're right.
I should probably make some fields DATE and some TIMESTAMP.
I'm creating the tables programmatically from MySql.
It's not that big a deal for me. I think I'll just leave it as is.

Thanks for the input.

-----Original Message-----
From: Bernt.Johnsen-UdXhSnd/***@public.gmane.org [mailto:Bernt.Johnsen-UdXhSnd/***@public.gmane.org]
Sent: Monday, November 20, 2006 5:08 AM
To: Derby Discussion
Subject: Re: Date - Timestamp format for inserts?

Post by Marl Atkins
A DATE field won't let me record the time will it?

Of course not. But the value '2006-09-10-00' led me to believe that the time
was of no importance to the datum.


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

[Daniel Noll]

Post by Marl Atkins
What's wrong with this statement?
INSERT INTO users
(RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr2,City,S
tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,AccessLeve
lID,DateCreated,CreatedBy,DateExpired ) VALUES(1,NULL,'SoftLink Systems,
Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,'marl','marl',0,1,'2006-09-10-00',1,'2050-01-01-00' )

Is there any particular reason you're not doing this the normal way?

(i.e. using ? and then setTimestamp(int,Timestamp))

Daniel
--
Daniel Noll

Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: [phone]
Web: http://nuix.com/ Fax: [phone]

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.

[Marl Atkins]

It would have to be because I don't know it.
Could you enlighten me?

-----Original Message-----
From: Daniel Noll [mailto:daniel-9mgTGGxUu+***@public.gmane.org]
Sent: Sunday, November 19, 2006 4:49 PM
To: Derby Discussion
Subject: Re: Date - Timestamp format for inserts?

Post by Marl Atkins
What's wrong with this statement?
INSERT INTO users
(RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr2,
City,S
tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,Acce
ssLeve lID,DateCreated,CreatedBy,DateExpired ) VALUES(1,NULL,'SoftLink
Systems,
Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
L,NULL ,NULL,'marl','marl',0,1,'2006-09-10-00',1,'2050-01-01-00' )

Is there any particular reason you're not doing this the normal way?

(i.e. using ? and then setTimestamp(int,Timestamp))

Daniel

--
Daniel Noll

Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: [phone]
Web: http://nuix.com/ Fax: [phone]

This message is intended only for the named recipient. If you are not the
intended recipient you are notified that disclosing, copying, distributing
or taking any action in reliance on the contents of this message or
attachment is strictly prohibited.

[Daniel Noll]

(Removing top posting.)

Post by Marl Atkins

Post by Daniel Noll

Post by Marl Atkins
What's wrong with this statement?
INSERT INTO users
(RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr2,
City,S
tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,Acce
ssLeve lID,DateCreated,CreatedBy,DateExpired ) VALUES(1,NULL,'SoftLink
Systems,
Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
L,NULL ,NULL,'marl','marl',0,1,'2006-09-10-00',1,'2050-01-01-00' )

Is there any particular reason you're not doing this the normal way?
(i.e. using ? and then setTimestamp(int,Timestamp))

It would have to be because I don't know it.
Could you enlighten me?

This is JDBC basics but I'll answer anyway since I'm still drinking the
morning coffee.

In your particular case... (ignore my wrapping, it won't actually be
valid in Java so either put it on one line or concat the strings.)

PreparedStatement st = conn.prepareStatement(
"INSERT INTO users (RecordID, CTMCClientID, OrgName, Prefix, FName,
MidInit, LName, Addr1, Addr2, City, State, Zip,
Phone, CellPhone, AltPhone, Fax, Email, Login,
Password, Status, AccessLevelID, DateCreated,
CreatedBy, DateExpired)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?)");

// more sets here...
st.setTimestamp(22, new Timestamp(...));
// more sets here...
st.executeUpdate();

In such a fashion we can insert dates without actually knowing the
syntax for them... and also insert strings without actually knowing how
to escape them. etc.

If it isn't immediately obvious, it's also possible to reuse that
PreparedStatement (in fact it's recommended, if you're calling the
statement multiple times.)

Daniel
--
Daniel Noll

Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: [phone]
Web: http://nuix.com/ Fax: [phone]

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.

[Marl Atkins]

Thanks:)

-----Original Message-----
From: Daniel Noll [mailto:daniel-9mgTGGxUu+***@public.gmane.org]
Sent: Monday, November 20, 2006 5:08 PM
To: Derby Discussion
Subject: Re: Date - Timestamp format for inserts?

(Removing top posting.)

Post by Marl Atkins

Post by Daniel Noll

Post by Marl Atkins
What's wrong with this statement?
INSERT INTO users
(RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr
2,
City,S
tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,Ac
ce ssLeve lID,DateCreated,CreatedBy,DateExpired )
VALUES(1,NULL,'SoftLink Systems,
Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
UL L,NULL ,NULL,'marl','marl',0,1,'2006-09-10-00',1,'2050-01-01-00'
)

Is there any particular reason you're not doing this the normal way?
(i.e. using ? and then setTimestamp(int,Timestamp))

It would have to be because I don't know it.
Could you enlighten me?

This is JDBC basics but I'll answer anyway since I'm still drinking the
morning coffee.

In your particular case... (ignore my wrapping, it won't actually be valid
in Java so either put it on one line or concat the strings.)

PreparedStatement st = conn.prepareStatement(
"INSERT INTO users (RecordID, CTMCClientID, OrgName, Prefix, FName,
MidInit, LName, Addr1, Addr2, City, State, Zip,
Phone, CellPhone, AltPhone, Fax, Email, Login,
Password, Status, AccessLevelID, DateCreated,
CreatedBy, DateExpired)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?)");

// more sets here...
st.setTimestamp(22, new Timestamp(...)); // more sets here...
st.executeUpdate();

In such a fashion we can insert dates without actually knowing the syntax
for them... and also insert strings without actually knowing how to escape
them. etc.

If it isn't immediately obvious, it's also possible to reuse that
PreparedStatement (in fact it's recommended, if you're calling the statement
multiple times.)

Daniel


--
Daniel Noll

Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: [phone]
Web: http://nuix.com/ Fax: [phone]

This message is intended only for the named recipient. If you are not the
intended recipient you are notified that disclosing, copying, distributing
or taking any action in reliance on the contents of this message or
attachment is strictly prohibited.

[Dan Scott]

Post by Daniel Noll

Post by Marl Atkins
What's wrong with this statement?
INSERT INTO users
(RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr2,City,S
tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,AccessLeve
lID,DateCreated,CreatedBy,DateExpired ) VALUES(1,NULL,'SoftLink Systems,
Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,'marl','marl',0,1,'2006-09-10-00',1,'2050-01-01-00' )

Is there any particular reason you're not doing this the normal way?
(i.e. using ? and then setTimestamp(int,Timestamp))

The original poster didn't mention which language was being used to
access Derby. Yes, it's 99% likely that it was Java, but Derby does
give people the ability to connect via OCBC, PHP, Perl, Python, etc...
so a workaround in one specific language doesn't apply to all of those
other languages. That being said, all of the official Derby docs speak
JDBC only, so I should probably hold my tongue.

But I do have to point out that saying "the normal way" isn't enough.
You have to realize that people are coming to Derby from all kinds of
different database backgrounds. For example, until recently it was
common MySQL programming practice to simply interpolate language-level
variables into SQL statements because MySQL lacked the ability to bind
columns against parameter markers.

In this case, you're really suggesting that the poster use a prepared
SQL statement that contains one or more parameter markers (the ?
symbol) that are bound against input variable(s). There's a general
description of prepared statements at
http://db.apache.org/derby/docs/dev/tuning/ctunperf18705.html, which
doesn't mention one other major benefit of prepared statements -- they
provide a fairly effective defence against SQL injection attacks.

Dan