Subtracting Hours from Date time values

MayaU_01
MayaU_01 Member
in Magic ETL

I have a dataset in which i have two columns of date timestamp values in format YYYY-MM-DD HH:MM:SS

1- Date created

2- Date Closed

I have subtracted this with a beast mode to get the days and hours minutes:

Beast mode name : Test01

CONCAT(
DATEDIFF(Date Closed,Date Created),' ','days',' ',TIMEDIFF(Date Closed,Date Created)
)

By this my output for a random case is coming like 2 days 2:00:00

Challenge - I have to make another beast mode in which i have to subtract 12 hours from the beast mode Test01. How I can do that so that for my case 2 days 2:00:00 if i subtract 12 hours i get 1 days 14:00:00

We can also try to subtract 12 hours from date closed and then calculate the Test01

Please help?

Tagged:

Answers

  • MichelleH
    MichelleH Coach

    @MayaU_01 You can use the same formula as before, but with a DATE_SUB function around each instance of Date Closed like this:

    CONCAT(
  DATEDIFF(DATE_SUB(`Date Closed`, interval 12 hour),`Date Created`),
  ' days ',
  TIMEDIFF(DATE_SUB(`Date Closed`, interval 12 hour),`Date Created`)
)

  • MayaU_01
    MayaU_01 Member

    It is giving me incorrect value. Basically in this method, we are subtracting 12 hours from the date difference and then we are also subtracting 12 hours from time value.

    So it is also subtracting date and then from the hours as well.

    check the column name Delta

    datetime.png

Categories