Search code examples
talend

How can I convert local time to UTC and vice versa in a Talend job

I have a database (not SQL) with time fields all filled with local time. And I have a database (SQL) with time fields in UTC. Now I want to exchange information between those databases, but I can only realize this if I can convert from local time to UTC and vice versa. How can I achieve this in Talend?

I know the data in the local time database, is the local time in the Netherlands. (GMT +1 (winter) of GMT +2 (summer))

Examples:
23-10-2015 16:00 Local time => 23-10-2015 14:00 UTC  (and vice versa)
26-10-2015 16:00 Local time => 26-10-2015 15:00 UTC  (and vice versa)
Solution

  • The below screenshot has a

    tFixedFlowInput_1 - define a schema with localDateTime (populated) and utcDateTime (unpopulated)

    tJavaRow_1 - performs Central/Europe to UTC timezone conversation on localDateTime and populates utcDateTime. This is the only essential piece.

    tLogRow_1 - shows the results

    enter image description here

    Next, setup the schema for the tFixedFlowInput and add some data enter image description here

    enter image description here

    Next... setup the tJavaRow_1 component

    tJavaRow_1 Advanced Settings / Imports are below:

    import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;
import java.text.ParseException;

    tJavaRow_1 Basic Settings (the real code) is below:

    Note that the try and catch block have been commented out so that exceptions are thrown and the Talend Job can handle them.

    Two instances of SimpleDateFormat are used, each associated with a time zone. The localDateTime is parsed through the source formatter. Then, the target formatter is used to convert the date to UTC and return it as a string in the original format. To go from UTC back to local is a trivial change.

    String BASE_FORMAT = "dd-MM-yyyy HH:mm";
TimeZone utcTZ = TimeZone.getTimeZone("UTC");
TimeZone ceTZ = TimeZone.getTimeZone("Europe/Amsterdam");

SimpleDateFormat formatUTC = new SimpleDateFormat( BASE_FORMAT );
formatUTC.setTimeZone(utcTZ);

SimpleDateFormat formatCE = new SimpleDateFormat( BASE_FORMAT );
formatCE.setTimeZone(ceTZ); 

output_row.localDateTime = input_row.localDateTime;

// Commented out the try and catch, so the exception is thrown to Talend job
//try {
    Date dateTimeLocal = formatCE.parse(input_row.localDateTime);
    output_row.utcDateTime = formatUTC.format(dateTimeLocal);
//}       
//catch (ParseException pe) {
//  System.out.println( pe.getMessage());
//}

    Next, the tLogRow_1 just displays the data on the flow. Here is an example from running with the sample data.

    enter image description here