Getting time in correct format to see difference and total time (overnight)

Question

I have:

01 23:58:23 and 02 00:02:11 where the format is "Day of month_hour:minute:second" in UTC. this would be an order placed at 11:58pm on day 1 of the month and it's delivered at 12:02am on the next day. I need to see total time in minutes to deliver, and the difference as well in minutes. I also need to convert the time from UTC to PST.

Help on any of the above would be very appreciated!

Thanks!

StephV · Answer

Hi @mcas1,

Thank you for your question to the Community! I moved your post to the Designer Discussions forum there you can ask anything technical about Designer.

This article "Q&A: Where Do I Post My Questions?" should also help you out.

If these @joshuaburkhow's or @MichalM's posts helped you, please, mark it as solution, so others can easily find it.

Let us know how it goes!

Cheers,

joshuaburkhow · Answer

Hi @mcas1

Anytime you are working with dates and or times and especially if you want to analyze differences in any way then you need to get them in the right date time format for Alteryx (https://help.alteryx.com/current/designer/datetime-tool) because then you can do pretty much anything you want via date time functions (https://help.alteryx.com/current/designer/datetime-functions)

MichalM · Answer

@mcas1

How would you know which Month/Year this is? If we assume it's always the current Month/Year the I'd do the following

* Generate the Date for the First of the Current Month using the below function

DateTimeFirstOfMonth()

* Concatenate the YearMonth with the already available information  - "Day of month_hour:minute:second" in UTC

Left([First Of Current Month/Year], 8)+[Placed]

* Calculate the time in minutes to deliver - difference between Placed and Delivered

DateTimeDiff([Delivered],[Placed], "minutes")

If you need a more accurate decimal number you can calculate seconds and divide by 60

DateTimeDiff([Delivered],[Placed], "seconds")/60

* Subtract 8 hours to get PST

DateTimeAdd([Placed],-8,"hours")

Here's a really good module on DateTime Functions in our Academy. I hope that helps.