Migrating from MySQL to Apache - having difficulty with Timestamp.

[Paul Linehan <li...@tcd.ie>]

Hi all,

I have the following MySQL statement and I wish to insert the same
data into a Derby table.

INSERT INTO analysis VALUES (6,'0000-00-00
00:00:00','RNASeqGene',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)

My Derby table is of the fomat (some columns missing).

========
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ANALYSIS_ID         |SMALLINT |0   |10  |5     |NULL      |NULL      |NO
CREATED             |TIMESTAMP|9   |10  |29    |NULL      |NULL      |NO
LOGIC_NAME          |VARCHAR  |NULL|NULL|128   |NULL      |256       |NO
DB                  |VARCHAR  |NULL|NULL|120   |NULL      |240       |YES
DB_VERSION          |VARCHAR  |NULL|NULL|40    |NULL      |80        |YES
DB_FILE             |VARCHAR  |NULL|NULL|120   |NULL      |240       |YES
PROGRAM             |VARCHAR  |NULL|NULL|80    |NULL      |160       |YES
================

>From here http://db.apache.org/derby/docs/10.5/ref/rrefsqlj27620.html,
is *_appears_* that
my data is OK, but I get the error
==============
Error: The string representation of a date/time value is out of range.
SQLState:  22007
ErrorCode: 30000
===============

>From Squirrel SQL.

Could anybody kindly point out where I'm going wrong?


TIA and rgs,


Paul...

-- 

linehanp@tcd.ie

Mob: 00 353 86 864 5772

Re: Migrating from MySQL to Apache - having difficulty with Timestamp.

[Paul Linehan <li...@tcd.ie>]

Hi Rick,


> Maybe the meaning of that MySQL timestamp is supposed to be equivalent to
> null?

Thanks again for your help. My project is a proof of concept -
basically, I have a system
(genomes/genomics related) that uses MySQL. This system is Java/Javascript based
and I'm seeing if the datastore/database side could be swapped out for Derby.

The advantages of this is that there would be no need for a MySQL install, no
admin  (the reaction of most biologists to Unix "Eeek, what's that?"...)

I've taken enough short cuts for the time being (changed both enum and set
types into VARCHAR...) that I think that changing those dates to null might do
the trick. Obviously, there could be dependencies on these dates that I don't
know about, but having looked at all my (sample) data, no timestamp is
anything other than '0000-00-00 00:00:00' so null is probably a good idea.

I'll go with that and if the worst comes to the worst, I'll look at
the code <gulp...>


Rgs,



Paul...


> -Rick

-- 

linehanp@tcd.ie

Mob: 00 353 86 864 5772

Re: Migrating from MySQL to Apache - having difficulty with Timestamp.

[Rick Hillegas <ri...@oracle.com>]

On 1/23/14 11:41 AM, Paul Linehan wrote:
> 0000-00-00
> 00:00:00'
Hi Paul,

I see that the Reference Manual does not state that the year, month, and 
day fields of the timestamp must be positive integers. The following 
script shows this behavior:

connect 'jdbc:derby:memory:db;create=true';

create table t( a timestamp );

-- fails because year, month, and day must be positive integers
insert into t values ( '0000-00-00 00:00:00' );
insert into t values ( '0001-00-00 00:00:00' );
insert into t values ( '0001-01-00 00:00:00' );

-- succeeds
insert into t values ( '0001-01-01 00:00:00' );

select * from t;

Maybe the meaning of that MySQL timestamp is supposed to be equivalent 
to null?

Hope this helps,
-Rick