Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- please help with timestamp import from DB2
- George Develekos
July 14, 2005, 3:40 am
rate this thread
Content-Type: text/plain; charset=iso-8859-7
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
....whereas the MySQL timestamp type is of the form
Has anybody done this before? How can I keep the fractions of a second
when I do the import?
Thanks for any help.
Content-Type: text/x-vcard; charset=iso-8859-7;
Content-Description: Card for George Develekos
fn:George I. Develekos
Re: please help with timestamp import from DB2
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
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum