Conditionals for time specific data (SQL style)

Question

I was inspired by this question from @AndyRoid to try and come up with some fast and accurate datetime calculations that didn't involve complex if/else statements.

My hammer tool of choice was MySQL, because of its simple syntax and very common use on the web. This could easily be adapted to other DBMS, though.

So, I have one stored procedure that takes two dates, defaulting to NOW() if one argument is empty or null. I couldn't make it to where one of the arguments can be omitted completely, that's not possible with MySQL.

This procedure seems really in need of DRY, but I'm not sure how better to do it.

USE Time;
DELIMITER $$
DROP PROCEDURE IF EXISTS conditionals_for_time_specific_data $$
CREATE PROCEDURE conditionals_for_time_specific_data(
    IN DateTo VARCHAR(100)
  , IN DateFrom VARCHAR(100)
)
BEGIN
-- input dates we will be working with
IF DateTo IS NOT NULL THEN
    SET @DateTo = (SELECT CASE WHEN DateTo = '' THEN NOW() ELSE CAST(DateTo AS DATETIME) END);
ELSE SET @DateTo = NOW();
END IF;
IF DateFrom IS NOT NULL THEN
    SET @DateFrom = (SELECT CASE WHEN DateFrom IS NULL THEN NOW() ELSE CAST(DateFrom AS DATETIME) END);
ELSE SET @DateFrom = NOW();
END IF;

-- different measures we will use
SET @SecondDiff = TIMESTAMPDIFF(SECOND, @DateFrom, @DateTo);
SET @MinuteDiff = TIMESTAMPDIFF(MINUTE, @DateFrom, @DateTo);
SET @HourDiff = TIMESTAMPDIFF(HOUR, @DateFrom, @DateTo);
SET @DayDiff = TIMESTAMPDIFF(DAY, @DateFrom, @DateTo);
SET @WeekDiff = TIMESTAMPDIFF(WEEK, @DateFrom, @DateTo);
SET @MonthDiff = TIMESTAMPDIFF(MONTH, @DateFrom, @DateTo);
SET @YearDiff = TIMESTAMPDIFF(YEAR, @DateFrom, @DateTo);

DROP TEMPORARY TABLE IF EXISTS TimeDifferences;
CREATE TEMPORARY TABLE TimeDifferences (TimeDifferences VARCHAR(1000));

    SELECT 
        'Seconds',
        CASE WHEN @SecondDiff > 0 THEN CONCAT(@SecondDiff, ' seconds from now')
        ELSE CONCAT(ABS(@SecondDiff), ' seconds ago') END AS "Time Difference"
    UNION
    SELECT
        'Minutes',
        CASE WHEN @MinuteDiff > 0 THEN CONCAT(@MinuteDiff, ' minutes from now')
        ELSE CONCAT(ABS(@MinuteDiff), ' minutes ago') END AS "Time Difference"
    UNION
    SELECT
        'Hours',
        CASE WHEN @HourDiff > 0 THEN CONCAT(@HourDiff, ' hours from now')
        ELSE CONCAT(ABS(@HourDiff), ' hours ago') END AS "Time Difference"
    UNION
    SELECT
        'Days',
        CASE WHEN @DayDiff > 0 THEN CONCAT(@DayDiff, ' days from now')
        ELSE CONCAT(ABS(@DayDiff), ' days ago') END AS "Time Difference"
    UNION
    SELECT
        'Weeks',
        CASE WHEN @WeekDiff > 0 THEN CONCAT(@WeekDiff, ' weeks from now')
        ELSE CONCAT(ABS(@WeekDiff), ' weeks ago') END AS "Time Difference"
    UNION
    SELECT
        'Months',
        CASE WHEN @MonthDiff > 0 THEN CONCAT(@MonthDiff, ' months from now')
        ELSE CONCAT(ABS(@MonthDiff), ' months ago') END AS "Time Difference"
    UNION
    SELECT
        'Years',
        CASE WHEN @YearDiff > 0 THEN CONCAT(@YearDiff, ' years from now')
        ELSE CONCAT(ABS(@YearDiff), ' years ago') END AS "Time Difference"
;
END $$
DELIMITER ;

It's very fast:

Execution time: 0:00:0.03870106

This returns a handy table with calculations in multiple time units, for instance:

CALL conditionals_for_time_specific_data ('1983-08-12', null);

Returns (formatted to JSON):

[
  {
      "Unit" : "Seconds",
      "Time Difference" : "1005866346 seconds ago"
  },
  {
      "Unit" : "Minutes",
      "Time Difference" : "16764439 minutes ago"
  },
  {
      "Unit" : "Hours",
      "Time Difference" : "279407 hours ago"
  },
  {
      "Unit" : "Days",
      "Time Difference" : "11641 days ago"
  },
  {
      "Unit" : "Weeks",
      "Time Difference" : "1663 weeks ago"
  },
  {
      "Unit" : "Months",
      "Time Difference" : "382 months ago"
  },
  {
      "Unit" : "Years",
      "Time Difference" : "31 years ago"
  }
]

Another example with a future date instead:

CALL conditionals_for_time_specific_data ('2083-08-12', NOW());

Output:

[
  {
      "Unit" : "Seconds",
      "Time Difference" : "2149893541 seconds from now"
  },
  {
      "Unit" : "Minutes",
      "Time Difference" : "35831559 minutes from now"
  },
  {
      "Unit" : "Hours",
      "Time Difference" : "597192 hours from now"
  },
  {
      "Unit" : "Days",
      "Time Difference" : "24883 days from now"
  },
  {
      "Unit" : "Weeks",
      "Time Difference" : "3554 weeks from now"
  },
  {
      "Unit" : "Months",
      "Time Difference" : "817 months from now"
  },
  {
      "Unit" : "Years",
      "Time Difference" : "68 years from now"
  }
]

Answer 1

With respect to the original question, you're ignoring the funny stuff: the conditionals.

---

IF DateTo IS NOT NULL THEN
    SET @DateTo = (SELECT CASE WHEN DateTo = '' THEN NOW() ELSE CAST(DateTo AS DATETIME) END);
ELSE SET @DateTo = NOW();

I'd go for something like

SET DateTo = COALESCE(DateTo, '');
SET @DateTo = (SELECT CASE WHEN DateTo = '' THEN NOW() ELSE CAST(DateTo AS DATETIME) END);

However, passing date as string seems broken to me, at least when you want to call it from program. It puts you on the mercy of formatting/parsing issues and locales and timezones (unless your server is collocated and properly set up) and what else.

---

That all said, I gladly repeat myself:

I find the idea perverted, because of - the need to have some DB around - inefficiency (round trip to DB) - exposing yourself to various DB quirks

Answer 2

conditionals_for_time_specific_data is a poor name for a stored procedure that returns a selection of the time difference between two dates in various units. How about something like get_time_difference_matrix.

Before the big SELECT at the end, you create a temporary table TimeDifferences but never use it. Perhaps leftover code from an earlier test?

I don't see much practical use for getting all the time differences in various units. A more typical use case would be getting the time difference in a specific unit. But the most typical use case would be as in the original question: get the time difference in the largest meaningful unit. So for example "120 minutes ago" among humans is more commonly knowns as "2 hours ago". Now that would be a handy stored proc.

I don't have a MySQL with me now to test, but it seems to me that this can be written simpler:

IF DateFrom IS NOT NULL THEN
    SET @DateFrom = (SELECT CASE WHEN DateFrom IS NULL THEN NOW() ELSE CAST(DateFrom AS DATETIME) END);
ELSE SET @DateFrom = NOW();
END IF;

Wouldn't this be the same?

SET @DateFrom = (SELECT CASE WHEN DateFrom IS NULL THEN NOW() ELSE CAST(DateFrom AS DATETIME) END);

I cannot test this now, but I'm wondering if this even simpler version would work:

SET @DateFrom = IFNULL(CAST(DateFrom AS DATETIME), NOW())