tSchemaComplianceCheck not validating dates with timezone

Six Stars

tSchemaComplianceCheck not validating dates with timezone

Hi all,

 

I'm facing a problem when using tSchemaComplianceCheck component to validate incoming date/time.

 

The data comes in the format "yyyy-MM-dd'T'HH:mm:ssXXX", for example "2019-10-02T21:45:33+01:00".

Schema check fails with the message "wrong DATE pattern or wrong DATE data"

 

However, when I'm parsing the same date using the same format, it's working without any issues:

TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ssXXX",... )

 

I have tried setting "Ignore TimeZone when check date" without any effect. 

 

Using Talend Open Studio for Data Integration, v7.1.1

 

Any ideas?

 

Thanks,

Rimvis


Accepted Solutions
Highlighted
Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

It appears that there is a check box called Use Strict Date Check (in Advanced settings). Validation is working correctly under UTC if this check box is enabled.

View solution in original post


All Replies
Employee

Re: tSchemaComplianceCheck not validating dates with timezone

Hi,

 

    If its not working for tSchemaComplianceCheck component alone, could you please create a feature request using JIRA?

 

https://jira.talendforge.org

 

    The interim feature will be to parse it using date parse function and catch any error and convert it to yes or no flag for rejection.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

Thanks @nikhilthampi 

 

At the moment I'm just trying to understand, if I'm doing something wrong, or the component does not support date formats with timezone (XXX) at all. I'm happy to raise a feature request if this is the case.

 

Thanks,

Rimvis

Employee

Re: tSchemaComplianceCheck not validating dates with timezone

Hi Rimvis,

 

       Apologies for the confusion. Just now I checked it again.

 

      You can definitely check the results for this format. You need to read the data as String and do the date check in tSchemaComplainceCheck component as shown below.

image.png

 

If the data is correct, it will be transferred to the main output as shown below.

image.png

 

If the data is wrong, it will be transferred to Reject flow as shown below.

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

Hi @nikhilthampi ,

 

It appears that the problem exists only if local time zone is set to UTC. It works as expected under other time settings.

Do you know if there is any workaround for that? Without changing the time settings that is Smiley Happy

 

Thanks,

Rimvis 

 

Employee

Re: tSchemaComplianceCheck not validating dates with timezone

Hi,

 

     Could you please share a sample value you are using and the error you are getting with screenshots?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

Hi @nikhilthampi ,

 

It's the same value: "2019-10-02T21:45:33+01:00"

The error: wrong DATE pattern or wrong DATE data

 

Here's screenshot with time zone set to UTC+2 (Helsinki , ...)

Schema_check_OK.png

 

And here's with UTC:

Schema_check_UTC.png

 

I'm attaching my sample job as well.

 

Thanks,

Rimvis

Employee

Re: tSchemaComplianceCheck not validating dates with timezone

Hi,

 

   I could not understand any difference between 2 screenshots. I was able to run your job with the data you have given in the sample job.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

Hi @nikhilthampi ,

 

In the first screenshot record is validated, and it goes to Main output (Check OK)

In the second screenshot validation fails, and the record goes into Rejects (Check Failed)

All I did between those runs is changed time zone to UTC.

 

Are you saying it runs OK for you in UTC time zone?

 

Thanks,

Rimvis 

Employee

Re: tSchemaComplianceCheck not validating dates with timezone

Hi,

 

   I could not see any difference in the values in both screenshots. Where are you changing the UTC time zone?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

Hi @nikhilthampi ,

 

I'm not changing the value, that's the point Smiley Happy The same value is being validated if process is running in UTC+2 timezone, but fails if running in UTC.

 

I'm changing time zone in Date & Time settings on my computer:

Timezone.png

Employee

Re: tSchemaComplianceCheck not validating dates with timezone

Hi,

 

    I got your point now :-)

 

    It looks like a bug and could you please create jira ticket for this issue https://jira.talendforge.org

 

   If you are an enterprise customer, please create a support case so that they can report the bug on your behalf and fast track the resolution. 

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

Hi @nikhilthampi ,

 

Sorry for not responding for a while. I was about to create a ticket, but I'm not sure which project should I pick. Can you please advise?

 

Thanks,

Rimvis

Employee

Re: tSchemaComplianceCheck not validating dates with timezone

Hi,

 

    You can select Studio or Talend Data Integration.

 

    In case you selected wrong project accidentally, they will change it to right project :-)

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

Thanks @nikhilthampi ,

 

I have raised an issue: https://jira.talendforge.org/browse/TDI-43136 

Let's see how it will go.

 

Cheers,

Rimvis

Highlighted
Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

It appears that there is a check box called Use Strict Date Check (in Advanced settings). Validation is working correctly under UTC if this check box is enabled.

View solution in original post

Six Stars

Re: tSchemaComplianceCheck not validating dates with timezone

@nikhilthampi 

 

Thanks, But if we use String as data type then invalid date like "0000-00-00T00:00:00.000" will be treated as valid date. how to capture this scenario then?

Two Stars

Re: tSchemaComplianceCheck not validating dates with timezone

It appears that there is a check box called Use Strict Date Check (in Advanced settings). Validation is working correctly under UTC if this check box is enabled.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog