Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

12 AM Time Conversion

Miglani
7 - Meteor

Hi all,
I have two columns in my data.

Structure:

 

Miglani_0-1620048550580.png

 

What I want to achieve: Date time difference between these 2 columns.

 

What I'm doing: 

1. Convert Column2 to time format use "hh:mm" in datetime tool.

2. Formula Tool: If column2 = "Midnight" then "00:00:00" else column2 endif

2. Do datetimediff(column1,column2,"minutes")

 

What's going wrong:

3rd row is returning 1429(It's taking 12 AM of next to next day) when it should return 11.

 

Why is this happening and what can I do to make this work?

Thanks.

11 REPLIES 11
JosephSerpis
17 - Castor
17 - Castor

Hi @Miglani the reason is that is happening is because its thinks 00:00:00 on the same day as your data, as it only has the time and no reference to the date. Alteryx in your example thinks its 00:00:00 on 2021-05-03 and then when it compares it to 23:49:00 it assumes it's at 23:49:00 2021-05-03. If you are able to add in the date to your time data then this should remove this issue.

Miglani
7 - Meteor

In such a case, the answer should be negative but it's positive.

00:00:00 (today) - 23:49:00 (today) should be negative.

It's strange. You can try this on Alteryx, it doesn't give the results.

 

On top of that, I don't have dates given in the data, so even if I create a dummy date, the process will become error-prone as I'd have to define a way to allot the next date for 00:00:00 (12 AM) as it becomes the next day. @JosephSerpis 

JosephSerpis
17 - Castor
17 - Castor

Hi @Miglani it would negative if your formula was  datetimediff(column2,column1,"minutes") as that would be 00:00:00 - 23:49:00 however you stated your formula was datetimediff(column1,column2,"minutes") so its 23:49:00 - 00:00:00 hence why its positive. If you don't have dates to add to your data and I agree with your observation that creating mock data will potentially cause errors. You could change your conversation to midnight to 23:59:00 which will give you a difference of ten and then you can add an additional 1 if you keep a record that it was originally midnight.  

Miglani
7 - Meteor

@JosephSerpis  That hacks fails in some cases.

1. If my column1 is 00:08:00 and column2 is 23:59:00(converted from 00:00:00). It will return -1431 when it should return 8.

2. I have to check whether to add 1 or subtract 1 based on some condition if date is greater or not in column 1.

 

Maybe I can handle all the cases by incorporating complex calculations but there surely has to be a better way? I wonder.

 

JosephSerpis
17 - Castor
17 - Castor

If you can get the date from your data source and incorporate it into your datetime calculation, that would be the simplest solution and would remove the complications you are currently experiencing. So it might be worth have discussions to see if you can get your data expanded.

Miglani
7 - Meteor

@JosephSerpis 
I tried to add a date and that doesn't change much. Because I still have to do some calculations to move the date of column2 to the next day when a certain condition in met. Or maybe I'm missing something here?

 

See the screenshot below. Same formula with dates added.

Miglani_0-1620056328018.png

 

JosephSerpis
17 - Castor
17 - Castor

I meant the actual Dates associated with your data in your example 23:49:00 and 00:00:00 are in fact two different dates e.g. today 2021-05-03 and 2021-05-04 00:00:00 so if the real dates were part of your data you would get the answer you are looking for 11. However as you just have time data Alteryx is assuming its on the same day so hence the answers you are seeing. If you had the real dates as part of your data this issues would disappear, otherwise you will need to come up with logic to deal with these scenarios. 

apathetichell
18 - Pollux

To @JosephSerpis 's point - you are actually looking for date related information - not time related information. Let me show you what I mean:

 

test test2 time1 time2 datetimediff
0:0:0 8:0:0 00:00:00 08:00:00 -480
8:0:0 0:0:0 08:00:00 00:00:00 480
0:01:0 8:0:0 00:01:00 08:00:00 -479
23:59:0 8:0:0 23:59:00 08:00:00 959
8:0:0 23:59:0 08:00:00 23:59:00 -959

 

Clearly the time from midnight to 8 a.m. should be different than the time from 8 a.m. to midnight but in TIME measurement that is not the case.

 

In your scenario you'd expect a 1 minute difference between 0:0:0 and 23:59. That is true in DATE format - but in TIME the two are not related. Time always looks at the absolute difference between two points whereas with DATE it's clock difference.

 

 

Miglani
7 - Meteor

@apathetichell I see what you mean but in there is a difference between time as well. It's not absolute, there's a SIGN difference.

 

The above discussed didn't really work. What I've done is that in the cases where there is a "midnight" case I have subtracted 1440 from #minutes in the formula and for the rest it is as it is.

 

This seems to be working but I think I'll soon find a problem in this as well.

Labels