How to subtract time from datetime field

[Darmesh]

Hi All,

i have datetime field and another field has only time. i need to subtract time from date

eg:

Date : 2022-06-29 10:04:00

time : 00:04:00

output :

2022-06-29 00:04:00.

[InfoCraft]

Hi @Darmesh Sureshbabu​,

I didn't quite understand what you want to achieve but here's a tip:

1. you just have to play on the petterne

2.Using substring(start index, end index)

 

Kind regards,

😷19

[Darmesh]

@not specified not specified​  My question is . if you see the screenshot i have given

i need to subtract the time with datetime field

datetime : 2022-04-01 04:50:45

time : 00:04:00

if i subtract 4 minutes with the datetime field. i should get 2022-04-01 04:46:45 as output.

how to achieve this in talend.

@Richard Hall​ , @Manohar B​ can you help me on this.

[InfoCraft]

1. Try to convert date times to milliseconds (Long)

2.do the subtraction

3. Convert the result: Long ---> Datetime 

String time1 = "2022-04-01 04:50:45";

String time2 = "00:04:00";

SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

SimpleDateFormat format2 = new SimpleDateFormat("HH:mm:ss");

Date date1 = format1.parse(time1);

Date date2 = format2.parse(time2);

long difference = date1.getTime() - date2.getTime();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

System.out.println(sdf.format(new Date(difference)));

Kind regards,

😷19

[Darmesh]

But Expected output is wrong in the above screenshot.

output in screenshot : 2022-04-01 05:46:45

expected output : 2022-04-01 04:46:45

it increases 1 hour

[SMR040]

if you want to subtract from a date you can use the addDate function and instead of adding you can subtract by putting minus sign in front of the number.

eg: TalendDate.addDate(row1.dateTime, -4, "mm") this will subtract 4 minutes from your dateTime variable.

You also need to slice up your subtraction time by hours/minutes/seconds to subtract each part separately. This because i'm not sure you can subtract for example -00:04:00 by using "hh:mm:ss" pattern but you can try it 🙂

[InfoCraft]

Hi @Darmesh Sureshbabu​,

oooops it was necessary to specify that it is a duration and not the hour

00:04:00 (time) = midnight and four minutes

00:04:00 (duration) = four minutes

the trick is:

1. transform dateTime to milliseconds (Long)

2. transform duration in milliseconds (Long): (HH*3600 +mm*60 + ss)*1000

3. do the subtraction to get a long

4. convert Long ---> DateTimetimeLong= ((Long.valueOf(Var.timeString.substring(0,2))*3600)+(Long.valueOf(Var.timeString.substring(2,4))*60)+(Long.valueOf(Var.timeString.substring(4,6))))*10002nd test  

Select as Best if its 👌

Kind regards,

😷19.