Unix Epoch to Date Redshift Invalid Date Format

MattE — Sat, 16 Nov 2024 03:28:55 GMT

Hi,

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".

Anyone any ideas how to fix this?

Thanks

Re: Unix Epoch to Date Redshift Invalid Date Format

MattE — Tue, 28 Jan 2020 12:07:13 GMT

ffs redshift datatype was DATE and not TIMESTAMP

topic Re: Unix Epoch to Date Redshift Invalid Date Format in Talend Studio

Unix Epoch to Date Redshift Invalid Date Format

Re: Unix Epoch to Date Redshift Invalid Date Format