topic join and compare dates with current time stamp on tmap in Talend Studio

join and compare dates with current time stamp on tmap

Anonymous — Mon, 18 Mar 2019 12:36:21 GMT

Hello,

I am trying to populate sales promotion information based on market, category and start and end time stamps.

Market and Category details are there on input and lookup files, I am doing left outer join on those using tmap.

I am facing issue with start and end time stamp. The sales promotion has to be updated as per the timestamp. This will not be there on input feed. If the current time stamp is between start and end timestamp, I want to update sales promotion data on feeds

Any idea on how to handle this?

Regards,

Mahesh

Re: join and compare dates with current time stamp on tmap

akumar2301 — Mon, 18 Mar 2019 14:16:59 GMT

if you want out yo be generated only if current time between Start_time and end_time , you should use filter expression ( a small button with arrow mark)

with expression like

TalendDate.compareDate(TalendDate.getCurrentDate(),row6.START_TIME) >= 0 &&

( row6.end_TIME == null || TalendDate.compareDate(TalendDate.getCurrentDate(),row6.end_TIME) < 0 )

Re: join and compare dates with current time stamp on tmap

Anonymous — Mon, 18 Mar 2019 20:37:17 GMT

Thanks for the details.

I tried it, but i am getting Exception in thread "main" java.lang.Error: Unresolved compilation problem:

getCurrentDate is returning timestamp with time zone, therefore I added format of timestamp which I needed to match lookup field columns, START_DATE and END_DATE

TalendDate.compareDate(TalendDate.getCurrentDate(),row6.START_DATE,"yyyy-MM-dd HH:mm:ss") > = 0

TalendDate.compareDate(TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",TalendDate.getCurrentDate()),TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",row6.START_DATE)) > 0

Both of them giving type conversion error

Re: join and compare dates with current time stamp on tmap

akumar2301 — Mon, 18 Mar 2019 22:19:18 GMT

Usually start_date and end_date is date so no parsing needed here.

You can get correct timezone time , by adding/substracting hours.

But make sure your job server is in same timezone of development server. Otherwise

Re: join and compare dates with current time stamp on tmap

Anonymous — Tue, 19 Mar 2019 11:30:54 GMT

In my case, start and end date has time with it, as sales promotion has to be running during that time period.

You can check the screenshot attached on my previous comment.

The issue seems to be data format. The compareDate functions expects both dates as arguments. In my case I have current date which I am parsing(TalendDate.parseDate) to convert it to date, also tried to parse start_date column to date, still seeing error.

Re: join and compare dates with current time stamp on tmap

akumar2301 — Tue, 19 Mar 2019 11:57:06 GMT

Hello

TalendDate.getCurrentDate() is aleady a date. But it is always in Locale Timezone.

To auto parse 2019-03-17 18:00:00 as Date , in schema definition use date pattern ""yyyy-MM-dd HH:mm:ss"" and type Date.

I tried the expression ,

TalendDate.compareDate(TalendDate.getCurrentDate(),row1.newColumn) >= 0 &&
( row1.newColumn1 == null || TalendDate.compareDate(TalendDate.getCurrentDate(),row1.newColumn1) < 0 )

it works e.g; in input I have

2019-03-17 18:00:00;2019-03-19 18:00:00
2019-03-17 18:00:00;2019-03-18 18:00:00
2019-03-17 18:00:00;2019-03-17 19:00:00
2019-03-17 18:00:00;

Current Time for me is 2019-03-19 10:54:40

with above expression , I get 1st record and last record in Output w/o any Parsing.

2019-03-17 18:00:00|2019-03-19 18:00:00
2019-03-17 18:00:00|

Let me know if it is working for you.

Re: join and compare dates with current time stamp on tmap

Anonymous — Tue, 19 Mar 2019 12:30:57 GMT

Abhishek,

I tried the same solution by changing the column name as per my schema, getting error - Type mismatch cannot convert from boolean to date

Attached screenshot of my workflow

Re: join and compare dates with current time stamp on tmap

akumar2301 — Tue, 19 Mar 2019 12:46:53 GMT

I guess , the variable you created is Date type , Above expression gives true or false.

Try like this

Do not worry about expression editor Test result.

Re: join and compare dates with current time stamp on tmap

Anonymous — Tue, 19 Mar 2019 20:08:47 GMT

Many Thanks Abhishek. It worked.