Date getting converted to 0001-01-01T00:00:00

I am loading tables using pipe delimited file and tMySQLOutputBulkExec
These tables have some date columns. Date in pipe delimited file is in the following format
""|"1"|""|""|""|""|""|""|""|"0"|"5736"|"1"|""
""|"5"|""|"01-01-1970 00:00:00"|""|""|""|""|""|"0"|"5736"|"1"|""
""|"6"|""|"01-01-1970 00:00:00"|""|""|""|""|""|"0"|"5736"|"1"|""
""|"7"|""|""|""|""|""|""|""|"0"|"5736"|"1"|""
But when it is inserted in the MySQL database with tMySQLOutputBulkExec it is loaded as
0001-01-01T00:00:00.
If I am using tMySQLOutput its working fine
but with tMySQLOutputBulkExec its always inserting a value as 0001-01-01T00:00:00. whether date is blank or has some value in the input.
Please note that I am using a java project.
Regds,
Anuradha.
13 REPLIES

Re: Date getting converted to 0001-01-01T00:00:00

Any updates please its urgent?
Would appreciate if you could respond to the same ASAP.
Thanks & Regds
Anuradha.
Community Manager

Re: Date getting converted to 0001-01-01T00:00:00

Hello Anuradha
You need to use the same pattern as target table.
yyyy-MM-dd HH:mm:ss
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Date getting converted to 0001-01-01T00:00:00

Thanks alot shong for the quick update.
The issue got resolved.
But now the problem is that, it is showing correct values for non null dates.
For input dates with null string its still inserting "0001-01-01T00:00:00".
So for this input wherin 4th column is a date column
""|"1"|""|""|""|""|""|""|""|"0"|"5736"|"1"|""
""|"5"|""|"01-01-1970 00:00:00"|""|""|""|""|""|"0"|"5736"|"1"|""
""|"6"|""|"01-01-1970 00:00:00"|""|""|""|""|""|"0"|"5736"|"1"|""
""|"7"|""|""|""|""|""|""|""|"0"|"5736"|"1"|""
Its inserting following in MYSQL
0001-01-01 00:00:00
1970-01-01 00:00:00
1970-01-01 00:00:00
0001-01-01 00:00:00
I could not understand why for null values it is getting converted to 0001-01-01 00:00:00?
Any other settings I need to do for null data handling.
Can you please give a demo example to load date column from delimited file to MYSQL5.0? This delimited file should have some null and some non null values as mentioned above in the input file.
Thanks & Regds,
Anuradha.

Re: Date getting converted to 0001-01-01T00:00:00

Waiting for the response .....
Thanks & Regds,
Anuradha.
Community Manager

Re: Date getting converted to 0001-01-01T00:00:00

Hello
I could not understand why for null values it is getting converted to 0001-01-01 00:00:00?
Any other settings I need to do for null data handling.

if you won't get the default value 0001-01-01 00:00:00 for null value,
you can add a new default date which you can consider invalid to replace 0001-01-01 00:00:00.
see my screenshot.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Date getting converted to 0001-01-01T00:00:00

Thanks shong, but the issue is I am migrating some data from Interbase to MYSQL now if something is null in Interbase I cant load it with some default value in MYSQL. I need to load it as null only.... Thats the issue.
I am new to both MySQL/Interbase and also TOS.
I had used the following flow.
t_InterbaseInput-->t_Map-->t_FileDelimitedOutput-->t_MySQLBulkExecuteOutput.
t_InterbaseInput--> reads data from Interbase table with Dates as String
t_Map-->Converts String to date format -- "yyyy-MM-dd HH:mm:ss"
t_FileDelimitedOutput-->Generates a pipe delimited file with each field enclosed by "" seperated by |.
t_MySQLBulkExecuteOutput--Read input delimted file and loads data in bulk to target table.
Now I used Logrow after each step to identify what values are passing at each step
t_InterbaseInput--> displays null as null in LogRow output
t_Map-->displays null as null in LogRow output
t_FileDelimitedOutput-->Converts null to ""
t_MySQLBulkExecuteOutput--Reads "" in date and load it as 0001-01-01 00:00:00 .
I also used a Java row in between t_FileDelimitedOutput and t_MySQLBulkExecuteOutput and found that Javarow reads "" as null only but loads its as 0001-01-01 00:00:00 . Please find the code below
**************************************************
output_row.SSN = input_row.SSN;
output_row.ACCOUNT_NUMBER = input_row.ACCOUNT_NUMBER;
output_row.COMMENTS = input_row.COMMENTS;
if (input_row.DATE_OF_BIRTH==null)
{
output_row.DATE_OF_BIRTH = TalendDate.parseDate("yyyy-MM-dd HH:mm:ss","9999-09-09 01:01:01");
}
else
output_row.DATE_OF_BIRTH=input_row.DATE_OF_BIRTH;
**************************************************
On running this code null dates were replaced by "9999-09-09 01:01:01" but not as "null" or "".
Could you make out where I am doing something wrong??
Thanks & Regds,
Anuradha.
Community Manager

Re: Date getting converted to 0001-01-01T00:00:00

Hello
Could you make out where I am doing something wrong??

I see that your code in tJavaRow is right, you are using 9999-09-09 01:01:01 replace 0001-01-01 00:00:00 when the date is null.
About replace null value wtih 0001-01-01 00:00:00, I think it is due to mysql bulk action. tMysqlOutput can load it with 'null' value.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Date getting converted to 0001-01-01T00:00:00

But tMySQLOutput is too slow with large amount of data thats why I opted tMySQLBulkOutput.
Any other option to pass it as a null value.
tMySQLBulkOutput takes few secs to load 60,000 data while tMySQLOutput takes more than a minute.

Re: Date getting converted to 0001-01-01T00:00:00

Hi Shong,
Any other work around to resolve this issue?
Thanks & Regds,
Anuradha.

Re: Date getting converted to 0001-01-01T00:00:00

Shong,
As you know I am using following flow:
t_InterbaseInput-->t_Map-->t_FileDelimitedOutput-->t_MySQLBulkExecuteOutput.
Where
t_InterbaseInput--> reads data from Interbase table with Dates as String
t_Map-->Converts String to date format -- "yyyy-MM-dd HH:mm:ss"
t_FileDelimitedOutput-->Generates a pipe delimited file with each field enclosed by "" seperated by |.
t_MySQLBulkExecuteOutput--Read input delimted file and loads data in bulk to target table.
Currently for Null Dates file is generated as
"1"|"5736"|"0"||""|||||"*"|"*** CASH SALE ***"|||||||"1"|"N"|"Admin "|"1"|"1"|||||||||||||||||||||"1"|""
"5"|"5736"|"0"||"1970-01-01"|||||"STOCK"|"****"|||||||"1"|"N"|"Admin "|"1"|"1"|||||||||||||||||||||"1"|""
Here 5th column is Date where "" in first record indicate null date value and "1970-01-01" valid date in the second record.
And with this file For input dates with null string t_MySQLBulkExecuteOutput is inserting "0001-01-01T00:00:00".
********************************************************************************
I found a solution that if in the file we pass null date as "\N" instead of "" the LOAD INFILE utility which is used by tMySQLBulkOutputExec interprets it as NULL.
I tested it by running LOAD INFILE on MySQL prompt and in the csv file I passed NULL as "\N" and it loaded data as NULL.
Command:---
LOAD DATA INFILE '/home/vanket/poc_demo/patients.csv' IGNORE INTO table I_PATIENTS FIELDS TERMINATED BY '|' ESCAPED BY '\\' ENCLOSED BY '"' LINES TERMINATED BY '\n';
the input patients.csv file was
"1"|"5736"|"0"||"\N"|||||"*"|"*** CASH SALE ***"|||||||"1"|"N"|"Admin "|"1"|"1"|||||||||||||||||||||"1"|""
"5"|"5736"|"0"||"1970-01-01"|||||"STOCK"|"****"|||||||"1"|"N"|"Admin "|"1"|"1"|||||||||||||||||||||"1"|""
Here 5th column is Date where I had passed "\N" in first record to load null value and a valid date in the second record.
***************************************************************************************
I tried to implement the same in TOS, what I am doing is , I tried passing date value as string and hardcoded it as "\N" in date column just to see all date values are getting inserted as NULL for each record.
But its giving error "Invalid escape sequence( valid ones are \n,\r etc.......
In the delimited file - Escape Char is set as -- "\\"
Also in t_MySQLBulkExecuteOutput Escape Char is set as -- "\\"

Can you please help, its really urgent as we have alot of tables with date columns.
And we have to implement this at all places.
Thanks .
Anuradha.

Re: Date getting converted to 0001-01-01T00:00:00

Waiting for reply ... from Talend Team ..... Smiley Sad
Community Manager

Re: Date getting converted to 0001-01-01T00:00:00

Hello Anuradha
Let's go ahead...
First, output the result to a txt file, then use tMysqlBulkExec instead of tMysqlOutputBulkExec to run the txt file. See my screenshot.
in.csv:

1;2009-02-26 01:02:33
2;
3;2009-02-27 01:02:33

expression of date column:
(row1.date.equals("")?"\"\\N\"":row1.date)

Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Date getting converted to 0001-01-01T00:00:00

Thanks alot shong for this demo.It solved my problem that was like a bottleneck.
Thanks Again.
Anuradha.