Date

java.util.Date and java.sql.Date hold timestamps. The tLogRow component displays these timestamps based on the pattern you configure. If the pattern only contains YYYY, the underlying data object will still contain more information. Timezones are one example of a piece of information that is often overlooked, which can result in dates moving when you don't expect them to.

For example, one database returns a date as 2021-03-10 Midnight, but the other database treats it as 2021-03-10 Midnight in Central Europe GMT+2, then converts it to UTC to store it and transforms it into a date type which results in 2021-03-09 after ingestion. This is often seen as a bug, but there is a logical explanation.

The database stores the object as 2021-03-10.

This goes to Java, where it becomes (depending on the conversion used):

2021-03-10 00:00:00.000 UTC
2021-03-10 02:00:00.000 GMT+2

Then it goes to the database, which treats it as:

2021-03-10 00:00:00.000 GMT+2
2021-03-09 22:00:00.000 UTC

The conversion to UTC will cause the date to move up a day. Because of this, when you have date-related issues, you should always analyze the entire date, including the timezone, hours, and seconds.

Precision

Since Talend uses java.util.Date, the micro and nanosecond precision is not available. However, some components might check if the incoming Object type is java.sql.Timestamp. In that case, the nanoseconds are available as well. To use this, you need to select the Object type in the schema definition, and the source and target components need to support it.

Experiments worth execution

You can try the following ways to troubleshoot any issue:

Use a tFileOutput component to dump and read these values. This way, you can control the timezone better.
Use the -Duser.timezone parameter to see if this changes the behavior. For more information, see Time Zone Settings in the JRE.
Consult the JDBC driver documentation to learn how to handle these timezones.
Use a tLogRow component with the following pattern and result:
```
"yyyy-MM-dd'T'HH:mm:ss.SSSXXX" 2001-07-04T12:08:56.235-07:00
```

Then the target database may or may not be able to store the timestamp part of the previous examples, which is combined with the timezone. This could lead to days shifting.

Possible workarounds

Use Strings and see if the databases or their driver can handle the transformation. With String, what you see is what you get, and there is not extra hidden information.

Convert between timezones using Java code in a tJavaRow component:

String pattern = "yyyy-MM-dd";
log.info(TalendDate.formatDate(pattern, input_row.startDate));
output_row.startDateUTC = TalendDate.parseDateInUTC("yyyy-MM-dd zzz",
TalendDate.formatDate("yyyy-MM-dd", input_row.startDate)+" UTC");

For more information about data behavior, see Your Calendrical Fallacy Is....

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here