"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
