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
.