please help with timestamp import from DB2

please help with timestamp import from DB2

am 13.07.2005 22:40:00 von George Develekos

This is a multi-part message in MIME format.
--------------030357A576F81CC5F65FEA69
Content-Type: text/plain; charset=iso-8859-7
Content-Transfer-Encoding: 7bit

I need to import into mysql data from DB2. One of the DB2 table columns
is of the TIMESTAMP type, which, unlike its MySQL counterpart, supports
fractions of a second, up to 6 digits, i.e. it is of the form

2005-07-13-23:45:32.000000

....whereas the MySQL timestamp type is of the form

2005-07-13 23:45:32

Has anybody done this before? How can I keep the fractions of a second
when I do the import?

Thanks for any help.

George

--------------030357A576F81CC5F65FEA69
Content-Type: text/x-vcard; charset=iso-8859-7;
name="gdevelek.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for George Develekos
Content-Disposition: attachment;
filename="gdevelek.vcf"

begin:vcard
n:Develekos;George
tel;cell:697-2006359
tel;home:210-8323634
x-mozilla-html:FALSE
adr:;;;;;;
version:2.1
email;internet:gdevelek@tee.gr
fn:George I. Develekos
end:vcard

--------------030357A576F81CC5F65FEA69--

Re: please help with timestamp import from DB2

am 13.07.2005 23:51:26 von Thomas Bartkus

"George Develekos" wrote in message
news:42D57C20.886B1581@tee.gr...
> I need to import into mysql data from DB2. One of the DB2 table columns
> is of the TIMESTAMP type, which, unlike its MySQL counterpart, supports
> fractions of a second, up to 6 digits, i.e. it is of the form
>
> 2005-07-13-23:45:32.000000
>
> ...whereas the MySQL timestamp type is of the form
>
> 2005-07-13 23:45:32
>
> Has anybody done this before? How can I keep the fractions of a second
> when I do the import?

MySQL date/time only has a 1 second resollution. MySQL interprets your
date/time string correctly and accepts it into a date/time field BUT it will
discard the fractional seconds. I have the same problem with date/time
fields I import from Postgres. The fractional seconds are significant and I
can not afford to simply throw it away.

My solution was to take the single Postgres date/time field and replace it
with (2) MySQL fields. The first being a MySQL date/time field with a
resolution of 1 second and the 2nd being an unsigned integer Micro Seconds
field that I extract from the original data.

Taking [dt] as the date/time string you have above -

SELECT CAST(LEFT(dt, 19) As DATETIME) As EventTm,
CAST(RIGHT(dt, 6) As UNSIGNED INTEGER) As MicroSecs

My single high resolution Postgres date/time stamp becomes a date/time field
plus a microseconds field for MySQL.

In my case, the moment the event happens is key. Unfortunately, several
items can occur within a single second. I need that MicroSecs field as a
tie breaker so I make the EventTM/MicroSecs a 2 field key value.

Hope this helps
Thomas Bartkus