topic Re: How to subtract time from datetime field in Talend Studio

How to subtract time from datetime field

Darmesh — Fri, 15 Nov 2024 22:46:47 GMT

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.

Re: How to subtract time from datetime field

InfoCraft — Wed, 29 Jun 2022 13:14:39 GMT

Hi @Darmesh Sureshbabu,

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

you just have to play on the petterne

2.Using substring(start index, end index)

Kind regards,

😷19

Re: How to subtract time from datetime field

Darmesh — Wed, 29 Jun 2022 13:22:04 GMT

@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.

Re: How to subtract time from datetime field

InfoCraft — Wed, 29 Jun 2022 16:06:24 GMT

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

Re: How to subtract time from datetime field

Darmesh — Thu, 30 Jun 2022 05:59:26 GMT

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

Re: How to subtract time from datetime field

SMR040 — Thu, 30 Jun 2022 10:06:40 GMT

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 🙂

Re: How to subtract time from datetime field

InfoCraft — Fri, 01 Jul 2022 18:57:33 GMT

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.