By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,866 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,866 IT Pros & Developers. It's quick & easy.

please help with timestamp import from DB2

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
"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.