Datediff function

[vaijns]

I've thought about adding a datediff function to calculate the difference between two dates.

Why

A use-case for this might be a scenario where you want your users to change their passwords every 12 months, so you occasionally run a check for users whose last password change approaches 12 months and remind them to change it by e-mail.
So you'd have a statement like SELECT * FROM user WHERE TIMESTAMPDIFF(MONTH, user.password_change_date, CURRENT_TIMESTAMP) >= 11 (haven't tested it) to fetch those users and iterate through the result-set to send the e-mail.

An alternative that could be used today might be taking todays date with std::chrono, subtracting X months from it and then just comparing those two dates in the query (SELECT * FROM user WHERE user.password_change_date < my_chrono_calculated_date).

It's debatable which of those two approaches would be better.

Design

Now for the question on how to design such a function there's a lot of options, especially because you could have differences in hours, days, months or what not.

Separate functions

Probably easiest but requiring a lot of duplicate code would be to just have completely separate functions for all difference types: year_diff(date_a, date_b), day_diff(date_a, date_b), hour_diff(date_a, date_b), ...

Personally, I wouldn't really like having so many functions though.

Templated difference type

The approach of having a difference type as template parameter would feel mostly like C++ I guess, could be a actual type parameter or an enum value, while an enum would be more restricted for extensibility:

// with enum
enum struct datediff_type{ year, month, day, hour, minute, second };
template<datediff_type Type, typename TA, typename TB> auto datediff(TA a, TB b);
// usage:
datediff<datediff_type::month>(date_a, date_b);

// with type
template<typename TDateDiff, typename TA, typename TB> auto datediff(TA a, TB b);
struct month_diff_t{};
// usage:
datediff<month_diff_t>(date_a, date_b);

Difference type as parameter

This approach would have most similarity in usage with MySQL and Microsofts T-SQL (of course only the former having a sqlpp connector):

// with enum
namespace detail{
    enum struct datediff_type{ year, month, day, hour, minute, second };
    template<datediff_type Type> datediff_type_t{ static constexpr datediff_type value = Type; };
}
namespace datediff_type{
    inline constexpr detail::datediff_type_t<detail::datediff_type::month> month{};
}
template<detail::datediff_type Type, typename TA, typename TB>
auto datediff(detail::datediff_type_t<Type>, TA a, TB b);
// usage:
datediff(datediff_type::month, date_a, date_b);

// with type
namespace datediff_type{
    struct month_diff_t{};
}
template<typename TDateDiff, typename TA, typename TB>
auto datediff(TDateDiff, TA a, TB b);
// usage:
datediff(datediff_type::month, date_a, date_b);

I really like this last approach from the users perspective due to its similarities with MySQL but this might be very subjective.
Feel free to share if you have completely different ideas which you prefer.

Compatibility

Unfortunately a problem with such a datediff function is that it isn't standardized SQL, instead all DBMS handle it very differently.
MySQL has a TIMESTAMPDIFF(unit, datetime_expr_a, datetime_expr_b) function, which is pretty much like T-SQLs DATEDIFF(datepart, startdate, enddate).
From what I've seen PostgreSQL doesn't have such a function and instead you just subtract those two dates and need to do conversions for a different precision difference, same with SQLite3 from what I can tell.

---

Actually I don't have a very strong opinion myself on whether such a function should exist in the library or if you should do the calculations in you application code.
So please let me know what you think, if you have an opinion on why one is better than the other and what a good design for such a function would be.
If it is something you or others would like to see in the library, I'd be willing to give implementing it a try. 🙂

-Jonas

[rbock]

Given that I typically use bigint to represent timestamps, I don't really have much of an opinion here.

Based on the availability in various SQL dialects, this would likely be implemented for the MySQL connector, only.

I probably wouldn't implement it myself, but I'd be OK with merging this into the library.

However, I would ask you to wait until the optional-no-dynamic branch is a bit more advanced and then add your code there if you want to give it a try.