Best Practice for Date fields

mynameisyannis

Hi All, 

 

I did a quick search on this but could not find much. If I missed something, please feel free to direct me to the appropriate resource. 

 

I work for a Support organization where our data sources have a lot of date fields, think of fields such as Ticket_created_at, Ticket_resolved_at, Ticket_first_response_at. I want to build cards with Time to Resolution, Time to First Response, etc. 

 

Here are my questions and request for advice: 
1. Should these calculations happen on the Dataflow level or in the cards? 

2. If it is to happen on the Dataflow level, how could I best standardize the data format? 

 

Thank you for any help/insight!

swagner

@mynameisyannis I typically do these kinds of aging calcs in beast mode.  Just seems more simple to build the banding the way you want and update quickly.  I would handle the field formatting ahead of the card building layer.  Here's an example:

 

Age Banding:

CASE

When DATEDIFF(CURDATE(),`Order Date`) >=120 then '> 120'
When DATEDIFF(CURDATE(),`Order Date`) >=90 then '90-120'
When DATEDIFF(CURDATE(),`Order Date`) >=60 then '60-90'
When DATEDIFF(CURDATE(),`Order Date`) >=30 then '30-60'
When DATEDIFF(CURDATE(),`Order Date`) < 30 then '< 30'

END

 

Age Banding Sort:

 

CASE

When DATEDIFF(CURDATE(),`Order Date`) >=120 then 5
When DATEDIFF(CURDATE(),`Order Date`) >=90 then 4
When DATEDIFF(CURDATE(),`Order Date`) >=60 then 3
When DATEDIFF(CURDATE(),`Order Date`) >=30 then 2
When DATEDIFF(CURDATE(),`Order Date`) < 30 then 1

END

mynameisyannis

Thanks @swagner!

Will definitely give that a go! And how about the date columns?

 

Currently, CURDATE() and my date columns are in different formatting (think of US/UK format).

 

1. What's the best way to standardize my date columns? In beast mode or when I am building the dataflow? 

2. What SQL function is better for this? 

 

Thank you!

swagner

@mynameisyannis to be honest, not sure on how to handle the date format in SQL for your use case.  You can probably find on stack overflow - or maybe even a google search.

 

There are also ways to manage date formats in Magic ETL (I use often) where you can specify the "MM-DD-YYYY" vs "DD-MM-YYYY" differences.

jaeW_at_Onyx

If at data ingestion, Domo is able to 'interpret' your data as type Date, it will store it as type date and then display it in the correct format (day first or month first) as appropriate.  So your question of CurDate() and date columns being in different formats should not apply.

 

If Domo is presenting your Date columns as type Text, then it suggests that at data ingestion there were values that prevented Domo from interpreting the data as type Column.  Usually that implies invalid values not just a formatting problem.  

 

That said, if you're uploading DateTime, then Domo can be a little tricky to work with and that might require massaging to sort out.

 

In terms of best practices, I usually avoid uploading DateTime columns unless the Time is absolutely necessary, in which case, I usually recommend splitting Date and Time into separate columns because dealing with TimeZones and Datelight Savings is a huge pita, AND cards will be more performant on just a Date column (as opposed to DateTime) due to Adrenaline's ability to compress and index the data more efficiently (fewer cardinal values).

 

If possible, I would actually avoid calculating dateDiffs and other derived measures in ETL because 1) it gives you flexibility if you need to change the math, by implementing in BeastModes (and it gives the benefit of transparency... for data governance you can use the Domo Governance datasets and the beast mode manager to create some sort of data dictionary).

 

2) I believe the dateDiff implementation in MagicETL can be a little janky and therefore requires investigation if the timediff exceeds 24 hours, I think @DataMaven  knows a bit more on this topic (but deffo Unix Timestamp is the best option that works all the time).

 

For calculating time or date diff, I think @GrantSmith  did all the hard work on this one and concluded that it's best to convert the dateTime column into UNIX timestamp first and then  do the diff of UnixTimeStamp because that guarantees to return the number of seconds between the two dateTimes (which you can then math into the desired units).

GrantSmith

@mynameisyannis - To follow up on @jaeW_at_Onyx 's dealing with UNIX_TIMESTAMP I've outlined usage and math to get days, hours, minutes and seconds in another post here: https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Date-Calculations-minutes-and-seconds/m-p/49259/highlight/true#M8465