TimeSpan formatting function

GrantSmith

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.

jaeW_at_Onyx

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.