add relative datediff macro (without tests)

[jpmmcneill]

Hey @clausherther

As discussed on slack - this is a macro I built that I found very helpful for things like customer lifetime analysis.

Let me know what you think!!!

[jpmmcneill]

I'd prefer for this to not be merged until there is a macro for cross db date parts. I might submit a PR for that over the weekend :)

[clausherther]

Hey @clausherther

As discussed on slack - this is a macro I built that I found very helpful for things like customer lifetime analysis.

Let me know what you think!!!

Looks interesting, thanks! I need to think about this works some more after coffee!

[jpmmcneill]

Marking as ready for review as I've put in the dbt_date.date_part, thanks for letting me know if existed 🤦

[clausherther]

Hi @jpmmcneill, thanks for this! I think we will also need an integration test for this. Let me know if you need help getting that set up. Before we get to that though, I have to admit, I don't fully understand what this macro does. Even the doc example did not clarify that for me. Couple of thoughts:

• is relative_datediff the best name to signify what this does? Relative to what?
• you mentioned you're using this for lifetime analysis, what is the exact application there for this macro? (the months_since_acquisition example makes it seem like the benefit is this is 0-based, which doesn't seem the main benefit).
• could we add another example to the docs?
• how does this work when datepart is less than a day, e.g. hour?
• how does this work when datepart is week, or worse, isoweek?

[clausherther]

Hi @jpmmcneill just checking in if you had thoughts on my comments above?

[jpmmcneill]

Hey @clausherther - I haven't yet to be honest, but I'll get back to you soon!!!

Initial reaction - it could definitely be named better 😛

[jpmmcneill]

Hey @clausherther - I'm happy to make an integration test. I should be able to set it up myself, but if I run into difficulty I'll ask for help 😅

Answers:

• is relative_datediff the best name to signify what this does? Relative to what?

It's basically relative to the original date. In some sense it could be called "calendar less". It's designet to have a date part agnostic method for modular arithmetic on the two dates. For example, if you're using years it would roughly work as: datediff (days)% 365.

• you mentioned you're using this for lifetime analysis, what is the exact application there for this macro? (the months_since_acquisition example makes it seem like the benefit is this is 0-based, which doesn't seem the main benefit).

I'd consider it quite useful for anything subscription based etc. It's basically just a tool that business analysts might use as a different lens on customers.. ie X calendar weeks old Vs X non calendar weeks old.

• could we add another example to the docs?

absolutely

• how does this work when datepart is less than a day, e.g. hour?
• how does this work when datepart is week, or worse, isoweek?

I don't think this macro makes sense with < 1 Day datepart. For week or isoweek, I'd expect roughly the same behaviour as there are 7 days per unit in both of those

[clausherther]

Hi @jpmmcneill! I'm going to close this one since it's a bit stale by now, but feel free to reopen if you want to push this one over the finish line. Thanks!