Revisions to Conditionals for time specific data (SQL style)

replaced http://codereview.stackexchange.com/ with https://codereview.stackexchange.com/

Source Link

edited Apr 13, 2017 at 12:40

1

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

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.

deleted 34 characters in body; edited tags

Source Link

edited Aug 3, 2015 at 15:31

Jamal

34.9k
13
133
237

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 ifif/elseelse statements.

So, here is theThis procedure. It seems really in need of DRY, but I'm not sure how better to do it. All advice appreciated!

It's very fast: Execution time: 0:00:0.03870106

Execution time: 0:00:0.03870106

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

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.

So, here is the procedure. It seems really in need of DRY, but I'm not sure how better to do it. All advice appreciated!

It's very fast: Execution time: 0:00:0.03870106

This returns a handy table with calculations in multiple time units. For instance:

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.

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

It's very fast:

Execution time: 0:00:0.03870106

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

sql mysql datetime

added 2 characters in body

Source Link

edited Jun 27, 2015 at 3:21

Phrancis

20.4k
6
68
154

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(@WeekDiff@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

[
  {
      "Unit" : "Seconds",
      "Time Difference" : "1005865714"1005866346 seconds ago"
  },
  {
      "Unit" : "Minutes",
      "Time Difference" : "16764428"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"
  }
]

[
  {
      "Unit" : "Seconds",
      "Time Difference" : "2149894018"2149893541 seconds from now"
  },
  {
      "Unit" : "Minutes",
      "Time Difference" : "35831566"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" : "3554"817 months from now"
  },
  {
      "Unit" : "Years",
      "Time Difference" : "68 years from now"
  }
]

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(@WeekDiff, ' 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 ;

[
  {
      "Unit" : "Seconds",
      "Time Difference" : "1005865714 seconds ago"
  },
  {
      "Unit" : "Minutes",
      "Time Difference" : "16764428 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"
  }
]

[
  {
      "Unit" : "Seconds",
      "Time Difference" : "2149894018 seconds from now"
  },
  {
      "Unit" : "Minutes",
      "Time Difference" : "35831566 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" : "3554 months from now"
  },
  {
      "Unit" : "Years",
      "Time Difference" : "68 years from now"
  }
]

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

[
  {
      "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"
  }
]

[
  {
      "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"
  }
]

Post Undeleted by Phrancis

occurred Jun 27, 2015 at 3:20

Post Deleted by Phrancis

occurred Jun 27, 2015 at 3:18

Source Link

asked Jun 27, 2015 at 3:15

Phrancis

20.4k
6
68
154

Loading

Stack Exchange Network

Return to Question