1

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)
PSVSupporter
  • 248
  • 3
  • 16
  • Maybe this can help you. http://stackoverflow.com/questions/16663990/casting-date-in-talend-data-integration – error505 Mar 01 '16 at 10:48

2 Answers2

3

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

dbh
  • 1,607
  • 13
  • 18
  • I tried, but I get multiple errors "The Import java.util.TimeZone collides with another import statement" "The method getTimeZone(String) is undefined for the type TimeZone" "The method setTimeZone(TimeZone) in the type DateFormat is not applicable for the arguments (TimeZone)" – PSVSupporter Mar 02 '16 at 14:04
  • This worked in Talend 6.1.1 with Java 1.8. What version of Java are you using? I can also provide a link to download the example project, later today. – dbh Mar 02 '16 at 14:08
  • Both older versions. I will update them and try again. – PSVSupporter Mar 02 '16 at 14:30
  • Java 1.7.0_21 (I cannot update this version, some company software isn't compatible with newer Java versions) Talend V5.1.2, this I can upgrade to 6.1.1 – PSVSupporter Mar 02 '16 at 14:32
  • 1
    Upgrading to Talend 6.1.1 did the job. It works. Thank you!! – PSVSupporter Mar 02 '16 at 14:49
2

I needed to get the current date in UTC. So, I made a column with the type Date and specific format as shown here:

enter image description here

In the expression Builder I wrote the following expression:

TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", TalendDate.formatDateInUTC("yyyy-MM-dd HH:mm:ss", TalendDate.getCurrentDate()))

That is my solution!

Also, I found out that if you put a component called: tlogrow after another component that you want to see the output from it - that would tlogrow do -> it will print you in the console a snapshot of data for each row. It is really useful for debugging.

I also am leading conversation here: https://community.talend.com/s/question/0D55b00006CvD7aCAF/return-utc-date-object

I hope it would be useful to someone!

Reneta
  • 486
  • 4
  • 12