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
Four Stars

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

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now