4
\$\begingroup\$

The code below compares recv.rcpt_dtim (which is a datetime type) against the current date/time. It calculates an elapsed time resulting in hours and minutes formatted like: "04:22". It took me a while to get it functional, which it is, but it just seems sloppy. Does anyone have any tips to clean it up?

TRIM((((CURRENT YEAR TO SECOND - recv.rcpt_dtim)::INTERVAL SECOND(9) to 
SECOND)/3600)::VARCHAR(12) || ':' || CASE WHEN (MOD(MOD(((CURRENT YEAR TO 
MINUTE - recv.rcpt_dtim)::INTERVAL MINUTE(9) to 
MINUTE)::VARCHAR(12)::INT,60),60))<10 THEN "0" ELSE "" END || 
(MOD(MOD(((CURRENT YEAR TO MINUTE - recv.rcpt_dtim)::INTERVAL MINUTE(9) 
to MINUTE)::VARCHAR(12)::INT,60),60))::VARCHAR(12))
Improve this question
\$\endgroup\$
1
  • \$\begingroup\$ Question asked on Stack Overflow too. Essentially the same answer, but less verbose, was given on SO by A.N.Other user than me. \$\endgroup\$
    – Jonathan Leffler
    May 9, 2019 at 22:52

1 Answer 1

Reset to default
4
\$\begingroup\$

When I use your complex expression, I don't get the leading zeros on the hour field that your code suggests that you want. AFAICS, the expression that tries to add a leading zero is fiddling with the minutes field. I'm not sure why you're doing the double MOD operations, either.

I think it is sufficient to use the vastly simpler CAST expression shown in the following SELECT statement. Here, instead of using CURRENT YEAR TO SECOND (or minor variants thereupon), I've created a second table, reference_times, with a single column reftime of type DATETIME YEAR TO SECOND. That allows me to test many different reference times reliably, in a way that using CURRENT simple does not. I use an explicit CROSS JOIN between the reference times and the recv table to compare every row in each table with each of the rows in the other.

SELECT reft.reftime,
       recv.rcpt_dtim,
       (reft.reftime - recv.rcpt_dtim) AS delta_t_1,
       CAST(reft.reftime - recv.rcpt_dtim AS INTERVAL HOUR(4) TO MINUTE) AS delta_t_2,
       TRIM((((reft.reftime - recv.rcpt_dtim)::INTERVAL SECOND(9) TO SECOND)/3600)::VARCHAR(12) ||
           ':' ||
           CASE WHEN (MOD(MOD(((reft.reftime - recv.rcpt_dtim)::INTERVAL MINUTE(9) TO MINUTE)::VARCHAR(12)::INT,60),60))<10
           THEN "0"
           ELSE "" END || 
           (MOD(MOD(((reft.reftime - recv.rcpt_dtim)::INTERVAL MINUTE(9) TO MINUTE)::VARCHAR(12)::INT,60),60))::VARCHAR(12)) AS delta_t_3
  FROM reference_times AS reft JOIN recv ON 1 = 1
 ORDER BY reft.reftime, recv.rcpt_dtim;

Given the setup code:

CREATE TEMP TABLE reference_times
(
    reftime     DATETIME YEAR TO SECOND NOT NULL PRIMARY KEY
);
INSERT INTO reference_times VALUES('2019-05-01 03:01:03');
INSERT INTO reference_times VALUES('2019-05-01 05:32:27');
INSERT INTO reference_times VALUES('2019-05-01 10:22:44');
INSERT INTO reference_times VALUES('2019-05-01 12:49:00');
INSERT INTO reference_times VALUES('2019-05-01 14:59:59');
INSERT INTO reference_times VALUES('2019-05-01 23:59:58');

CREATE TEMP TABLE recv
(
    rcpt_dtim   DATETIME YEAR TO SECOND NOT NULL PRIMARY KEY
);
INSERT INTO recv VALUES('2019-05-01 01:10:11');
INSERT INTO recv VALUES('2019-05-01 02:00:00');
INSERT INTO recv VALUES('2019-04-30 22:19:45');
INSERT INTO recv VALUES('2019-04-30 18:37:21');
INSERT INTO recv VALUES('2019-04-30 03:31:00');
INSERT INTO recv VALUES('2019-04-30 01:19:45');
INSERT INTO recv VALUES('2019-04-30 00:00:00');
INSERT INTO recv VALUES('2019-04-29 20:10:58');
INSERT INTO recv VALUES('2019-04-10 22:09:00');
INSERT INTO recv VALUES('2019-04-01 22:09:00');

I get output such as:

reftime              rcpt_dtim               delta_t_1 delta_t_2  delta_t_3
2019-05-01 03:01:03  2019-04-01 22:09:00   29 04:52:03    700:52  700:52
2019-05-01 03:01:03  2019-04-10 22:09:00   20 04:52:03    484:52  484:52
2019-05-01 03:01:03  2019-04-29 20:10:58    1 06:50:05     30:50  30:50
2019-05-01 03:01:03  2019-04-30 00:00:00    1 03:01:03     27:01  27:01
2019-05-01 03:01:03  2019-04-30 01:19:45    1 01:41:18     25:41  25:41
2019-05-01 03:01:03  2019-04-30 03:31:00    0 23:30:03     23:30  23:30
2019-05-01 03:01:03  2019-04-30 18:37:21    0 08:23:42      8:23  8:23
2019-05-01 03:01:03  2019-04-30 22:19:45    0 04:41:18      4:41  4:41
2019-05-01 03:01:03  2019-05-01 01:10:11    0 01:50:52      1:50  1:50
2019-05-01 03:01:03  2019-05-01 02:00:00    0 01:01:03      1:01  1:01
2019-05-01 05:32:27  2019-04-01 22:09:00   29 07:23:27    703:23  703:23
2019-05-01 05:32:27  2019-04-10 22:09:00   20 07:23:27    487:23  487:23
2019-05-01 05:32:27  2019-04-29 20:10:58    1 09:21:29     33:21  33:21
2019-05-01 05:32:27  2019-04-30 00:00:00    1 05:32:27     29:32  29:32
2019-05-01 05:32:27  2019-04-30 01:19:45    1 04:12:42     28:12  28:12
2019-05-01 05:32:27  2019-04-30 03:31:00    1 02:01:27     26:01  26:01
2019-05-01 05:32:27  2019-04-30 18:37:21    0 10:55:06     10:55  10:55
2019-05-01 05:32:27  2019-04-30 22:19:45    0 07:12:42      7:12  7:12
2019-05-01 05:32:27  2019-05-01 01:10:11    0 04:22:16      4:22  4:22
2019-05-01 05:32:27  2019-05-01 02:00:00    0 03:32:27      3:32  3:32
2019-05-01 10:22:44  2019-04-01 22:09:00   29 12:13:44    708:13  708:13
2019-05-01 10:22:44  2019-04-10 22:09:00   20 12:13:44    492:13  492:13
2019-05-01 10:22:44  2019-04-29 20:10:58    1 14:11:46     38:11  38:11
2019-05-01 10:22:44  2019-04-30 00:00:00    1 10:22:44     34:22  34:22
2019-05-01 10:22:44  2019-04-30 01:19:45    1 09:02:59     33:02  33:02
2019-05-01 10:22:44  2019-04-30 03:31:00    1 06:51:44     30:51  30:51
2019-05-01 10:22:44  2019-04-30 18:37:21    0 15:45:23     15:45  15:45
2019-05-01 10:22:44  2019-04-30 22:19:45    0 12:02:59     12:02  12:02
2019-05-01 10:22:44  2019-05-01 01:10:11    0 09:12:33      9:12  9:12
2019-05-01 10:22:44  2019-05-01 02:00:00    0 08:22:44      8:22  8:22
2019-05-01 12:49:00  2019-04-01 22:09:00   29 14:40:00    710:40  710:40
2019-05-01 12:49:00  2019-04-10 22:09:00   20 14:40:00    494:40  494:40
2019-05-01 12:49:00  2019-04-29 20:10:58    1 16:38:02     40:38  40:38
2019-05-01 12:49:00  2019-04-30 00:00:00    1 12:49:00     36:49  36:49
2019-05-01 12:49:00  2019-04-30 01:19:45    1 11:29:15     35:29  35:29
2019-05-01 12:49:00  2019-04-30 03:31:00    1 09:18:00     33:18  33:18
2019-05-01 12:49:00  2019-04-30 18:37:21    0 18:11:39     18:11  18:11
2019-05-01 12:49:00  2019-04-30 22:19:45    0 14:29:15     14:29  14:29
2019-05-01 12:49:00  2019-05-01 01:10:11    0 11:38:49     11:38  11:38
2019-05-01 12:49:00  2019-05-01 02:00:00    0 10:49:00     10:49  10:49
2019-05-01 14:59:59  2019-04-01 22:09:00   29 16:50:59    712:50  712:50
2019-05-01 14:59:59  2019-04-10 22:09:00   20 16:50:59    496:50  496:50
2019-05-01 14:59:59  2019-04-29 20:10:58    1 18:49:01     42:49  42:49
2019-05-01 14:59:59  2019-04-30 00:00:00    1 14:59:59     38:59  38:59
2019-05-01 14:59:59  2019-04-30 01:19:45    1 13:40:14     37:40  37:40
2019-05-01 14:59:59  2019-04-30 03:31:00    1 11:28:59     35:28  35:28
2019-05-01 14:59:59  2019-04-30 18:37:21    0 20:22:38     20:22  20:22
2019-05-01 14:59:59  2019-04-30 22:19:45    0 16:40:14     16:40  16:40
2019-05-01 14:59:59  2019-05-01 01:10:11    0 13:49:48     13:49  13:49
2019-05-01 14:59:59  2019-05-01 02:00:00    0 12:59:59     12:59  12:59
2019-05-01 23:59:58  2019-04-01 22:09:00   30 01:50:58    721:50  721:50
2019-05-01 23:59:58  2019-04-10 22:09:00   21 01:50:58    505:50  505:50
2019-05-01 23:59:58  2019-04-29 20:10:58    2 03:49:00     51:49  51:49
2019-05-01 23:59:58  2019-04-30 00:00:00    1 23:59:58     47:59  47:59
2019-05-01 23:59:58  2019-04-30 01:19:45    1 22:40:13     46:40  46:40
2019-05-01 23:59:58  2019-04-30 03:31:00    1 20:28:58     44:28  44:28
2019-05-01 23:59:58  2019-04-30 18:37:21    1 05:22:37     29:22  29:22
2019-05-01 23:59:58  2019-04-30 22:19:45    1 01:40:13     25:40  25:40
2019-05-01 23:59:58  2019-05-01 01:10:11    0 22:49:47     22:49  22:49
2019-05-01 23:59:58  2019-05-01 02:00:00    0 21:59:58     21:59  21:59

Column names/types:

reftime      DATETIME YEAR TO SECOND
rcpt_dtim    DATETIME YEAR TO SECOND
delta_t_1    INTERVAL DAY(8) TO SECOND
delta_t_2    INTERVAL HOUR(4) TO MINUTE
delta_t_3    VARCHAR(26)

Observe that the delta_t_2 column produces the same output as the delta_t_3 column — apart from type (interval vs string); the left vs right adjustment is mostly an artefact of the way the program that generated the data futzes with the formatting.

Consequently, your elaborate expression can be simplified to:

CAST(CURRENT YEAR TO SECOND - recv.rcpt_dtim AS INTERVAL HOUR(4) TO MINUTE)

or:

(CURRENT YEAR TO SECOND - recv.rcpt_dtim)::INTERVAL HOUR(4) TO MINUTE

And if your time gaps are small enough, you can change the 4 to 2.

Improve this answer
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.