TimeSpan formatting function

GrantSmith
GrantSmith Indiana πŸ₯·

There have been a lot of requests on the Dojo about specific formatting for the difference between two dates. It'd be great if there was as simple function we could pass in a time diff (`Date2` - `Date1`) along with a format string to automatically calculate the correct display string.

Β 

For example:

FORMAT_DIFF(`Date2` - `Date1`, '%W weeks, %d days')

Β 

DATEDIFF only gives us a single unit of measure but a lot of times we'll want a combination of units (Week + Days, Years + Months + Days, Hours + Minutes + Seconds)

Β 

Right now the common solution is to utilize UNIX_TIMESTAMP and calculating the difference in seconds between the two and then doing a bunch of math depending on which unit you need which can become very complex and difficult to understand if you're not technical enough.

**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
2
2 votes

Β· Last Updated

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR πŸ”΄

    Does this exist in any other SQL implementation?Β 

    Β 

    i guess on paper it's pretty, but the challenge then is that the output would be a text string.Β  that text string would not (necessarily) be sortable, and relatively difficult to work with. if you wanted for example "where timediff is between 12 and 15 days" but all the values are long strings.Β Β 

    Β 

    Β 

    Jae Wilson
    Check out my πŸŽ₯ Domo Training YouTube Channel πŸ‘¨β€πŸ’»

    **Say "Thanks" by clicking the ❀️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.