469,640 Members | 1,561 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,640 developers. It's quick & easy.

please help with timestamp import from DB2

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

Jul 23 '05 #1
1 2822
"George Develekos" <gd******@tee.gr> wrote in message
news:42***************@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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Sean Berry | last post: by
3 posts views Thread by Joe | last post: by
5 posts views Thread by pankaj_wolfhunter | last post: by
5 posts views Thread by Dmitry Bond. | last post: by
5 posts views Thread by E.Bartosiewicz | last post: by
1 post views Thread by Kent Tenney | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.