Invalid date time output for mysql

Four Stars

Invalid date time output for mysql

Hi,
I am having a simple job to write mysql table contents into the csv file. Current datatype for the column is datetime and the format is "yyyy-MM-dd HH:mm:ss". But when we write the data into the file it has additional .0 i.e. "yyyy-MM-dd HH:mm:ss.x" even though the length of the column is set to 19 still getting 21 chars in output.
I am using mysql 5.6.15 and TOS 5.3.0. Also verified on MySQL 5.5.35, same is the output. Adding 0 as millisecond in the output.
Mysql database table I/p - 2014-04-17 00:00:00
O/p - 2014-04-17 00:00:00.0
How to solve this? is this a bug?
Thanks
vaibhav
One Star

Re: Invalid date time output for mysql

Hi sanvaibhav - I suppose this was a straight read / write with no transformation. Yes, it's puzzling that you'd have the trailing milliseconds when you didn't define one in your date format. I'd be curious to know what you'd get if you used the TalendDate.formatDate to force the date format..
Four Stars

Re: Invalid date time output for mysql

Unfortunately I don't have any control over input and output. Input and output are going to sync and I would be working on output file which was generated. I can try to trail the output, but it would be for one scenario. For other scenario it won't work because we don't know as there is no transformation there.
What I remember is older version @(5.0 or older) of mysql did not have millisecond support and now that they extended support for millisecond. But the talend which reads the schema has some problem which is considering millisecond part of the date.
Vaibhav
Four Stars

Re: Invalid date time output for mysql

I have fixed the issue by changing query at the input as follows -
DATE_FORMAT("moment", GET_FORMAT(DATETIME,'USA')).
But new problem is that, talend is converting input time stamp to other format. Pl check the screenshots..
How to prevent this date time format conversion?
Thanks
Vaibhav