I've written a java routine to convert from Unix epoch to a java date
//java routine
public static Date unixTimestampToDate(String ts, Boolean isMs) {
Date date;
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
if(!isMs){
date = new Date(Long.parseLong(ts));
return format.parse(date.toString());
} else {
return date = new Date(Long.parseLong(ts)*1000);
}
} catch (ParseException e) {
return date = new Date(Long.parseLong(ts));
}
}
I call this in a tMap with the output column set to Date "yyyy-MM-dd HH:mm:ss"
The incoming value is 1579478403484 which according to https://www.epochconverter.com/ should be Monday, January 20, 2020 12:00:03.484 AM. I can confirm this conversion is happening correctly by putting a tLogRow on the output of the tMap and seeing the following in the console
2020-01-20 00:00:03
However when i run the job i get the error
java.sql.SQLException: [Amazon](500310) Invalid operation: Load into table 'staging_adzerk_requests' failed. Check 'stl_load_errors' system table for details.;
on checking stl_load_errors i can see that redshift is complaining of
2020-01-20 0024:00:02 - Invalid Date Format - length must be 10 or more
It looks like Talend is formatting the date wrong in the tDBOutputBulkExec even though i also have that set to "yyyy-MM-dd HH:mm:ss".
