Select difference between row dates in MySQL database 8.0.17

comunidadmexicanarom · March 2, 2021, 1:50pm

Hi,

I want to calculate the difference in unique date fields between different rows in the same table.

Small sample.

Given the following query SELECT

SELECT
    sID,
    HHMMSS,
    FileName,
    TIMESTAMPDIFF( MINUTE, ( SELECT MAX( HHMMSS ) FROM `tbl_pds_timer` WHERE HHMMSS < t.HHMMSS ), HHMMSS ) AS min,
    TIMESTAMPDIFF( MINUTE, ( SELECT MAX( HHMMSS ) FROM `tbl_pds_timer` WHERE HHMMSS < t.HHMMSS ), HHMMSS )* 60 AS sec 
FROM
    `tbl_pds_timer` t ORDER BY sID DESC

+-----+---------------------+----------+-----+-------+
| sID | HHMMSS              | FileName | min | sec   |
+-----+---------------------+----------+-----+-------+
|  59 | 2021-03-02 06:43:06 | 20210228 |  12 |   720 |
|  58 | 2021-03-02 06:30:20 | 20210227 | 251 | 15060 |
|  57 | 2021-03-02 02:18:35 | 20210226 |  47 |  2820 |
|  56 | 2021-03-02 01:31:18 | 20210225 |  54 |  3240 |
|  55 | 2021-03-02 00:37:01 | 20210224 |  64 |  3840 |
|  54 | 2021-03-01 23:32:31 | 20210223 |  51 |  3060 |
|  53 | 2021-03-01 22:41:23 | 20210222 |  48 |  2880 |
|  52 | 2021-03-01 21:52:45 | 20210221 |  17 |  1020 |
|  51 | 2021-03-01 21:35:36 | 20210220 |  17 |  1020 |
|  50 | 2021-03-01 21:18:16 | 20210219 |  45 |  2700 |
+-----+---------------------+----------+-----+-------+
10 rows in set (0.04 sec)

I have tried update the fields timer_min and timer_sec on the tbl_pds_timer table using the following query without success because the rows affected of update are zero…

UPDATE `tbl_pds_timer` AS k
JOIN (
    SELECT
        sID,
        HHMMSS,
        FileName,
        TIMESTAMPDIFF( MINUTE, ( SELECT MAX( HHMMSS ) FROM `tbl_pds_timer` WHERE HHMMSS < t.HHMMSS ), HHMMSS ) AS min,
        TIMESTAMPDIFF( MINUTE, ( SELECT MAX( HHMMSS ) FROM `tbl_pds_timer` WHERE HHMMSS < t.HHMMSS ), HHMMSS )* 60 AS sec 
    FROM
        `tbl_pds_timer` t 
    ) AS q 
    SET k.timer_min = q.min,
    k.timer_sec = q.sec;

Query OK, 0 rows affected (0.04 sec)
Rows matched: 59  Changed: 0  Warnings: 0

Any suggestions for what date functions I would use in MySQL, or is there a subselect that would do this?

tbl_pds_timer complete table below

-- ----------------------------
-- Table structure for tbl_pds_timer
-- ----------------------------
DROP TABLE IF EXISTS `tbl_pds_timer`;
CREATE TABLE `tbl_pds_timer`  (
  `FileName` varchar(255) DEFAULT NULL,
  `timer_min` varchar(255) DEFAULT NULL,
  `timer_sec` decimal(10, 2) NULL DEFAULT NULL,
  `HHMMSS` datetime(0) NULL DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`) USING BTREE,
  UNIQUE INDEX `FileName`(`FileName`) USING BTREE
) ENGINE = MyISAM;

-- ----------------------------
-- Records of tbl_pds_timer
-- ----------------------------
INSERT INTO `tbl_pds_timer` VALUES ('20210101', NULL, NULL, '2021-02-28 19:46:08', 1);
INSERT INTO `tbl_pds_timer` VALUES ('20210102', NULL, NULL, '2021-02-28 19:58:14', 2);
INSERT INTO `tbl_pds_timer` VALUES ('20210103', NULL, NULL, '2021-02-28 20:11:10', 3);
INSERT INTO `tbl_pds_timer` VALUES ('20210104', NULL, NULL, '2021-02-28 21:11:19', 4);
INSERT INTO `tbl_pds_timer` VALUES ('20210105', NULL, NULL, '2021-02-28 22:43:17', 5);
INSERT INTO `tbl_pds_timer` VALUES ('20210106', NULL, NULL, '2021-02-28 23:31:07', 6);
INSERT INTO `tbl_pds_timer` VALUES ('20210107', NULL, NULL, '2021-03-01 00:02:41', 7);
INSERT INTO `tbl_pds_timer` VALUES ('20210108', NULL, NULL, '2021-03-01 00:16:40', 8);
INSERT INTO `tbl_pds_timer` VALUES ('20210109', NULL, NULL, '2021-03-01 00:28:46', 9);
INSERT INTO `tbl_pds_timer` VALUES ('20210110', NULL, NULL, '2021-03-01 00:41:16', 10);
INSERT INTO `tbl_pds_timer` VALUES ('20210111', NULL, NULL, '2021-03-01 01:00:39', 11);
INSERT INTO `tbl_pds_timer` VALUES ('20210112', NULL, NULL, '2021-03-01 01:23:54', 12);
INSERT INTO `tbl_pds_timer` VALUES ('20210113', NULL, NULL, '2021-03-01 01:41:52', 13);
INSERT INTO `tbl_pds_timer` VALUES ('20210114', NULL, NULL, '2021-03-01 02:06:37', 14);
INSERT INTO `tbl_pds_timer` VALUES ('20210115', NULL, NULL, '2021-03-01 02:26:49', 15);
INSERT INTO `tbl_pds_timer` VALUES ('20210116', NULL, NULL, '2021-03-01 02:39:00', 16);
INSERT INTO `tbl_pds_timer` VALUES ('20210117', NULL, NULL, '2021-03-01 02:50:55', 17);
INSERT INTO `tbl_pds_timer` VALUES ('20210118', NULL, NULL, '2021-03-01 03:18:14', 18);
INSERT INTO `tbl_pds_timer` VALUES ('20210119', NULL, NULL, '2021-03-01 03:40:22', 19);
INSERT INTO `tbl_pds_timer` VALUES ('20210120', NULL, NULL, '2021-03-01 03:58:19', 20);
INSERT INTO `tbl_pds_timer` VALUES ('20210121', NULL, NULL, '2021-03-01 04:14:01', 21);
INSERT INTO `tbl_pds_timer` VALUES ('20210122', NULL, NULL, '2021-03-01 04:40:22', 22);
INSERT INTO `tbl_pds_timer` VALUES ('20210123', NULL, NULL, '2021-03-01 05:10:23', 23);
INSERT INTO `tbl_pds_timer` VALUES ('20210124', NULL, NULL, '2021-03-01 05:30:37', 24);
INSERT INTO `tbl_pds_timer` VALUES ('20210125', NULL, NULL, '2021-03-01 06:02:43', 25);
INSERT INTO `tbl_pds_timer` VALUES ('20210126', NULL, NULL, '2021-03-01 06:49:15', 26);
INSERT INTO `tbl_pds_timer` VALUES ('20210127', NULL, NULL, '2021-03-01 07:20:08', 27);
INSERT INTO `tbl_pds_timer` VALUES ('20210128', NULL, NULL, '2021-03-01 07:49:21', 28);
INSERT INTO `tbl_pds_timer` VALUES ('20210129', NULL, NULL, '2021-03-01 08:21:58', 29);
INSERT INTO `tbl_pds_timer` VALUES ('20210130', NULL, NULL, '2021-03-01 08:36:03', 30);
INSERT INTO `tbl_pds_timer` VALUES ('20210131', NULL, NULL, '2021-03-01 08:49:58', 31);
INSERT INTO `tbl_pds_timer` VALUES ('20210201', NULL, NULL, '2021-03-01 09:20:30', 32);
INSERT INTO `tbl_pds_timer` VALUES ('20210202', NULL, NULL, '2021-03-01 09:53:16', 33);
INSERT INTO `tbl_pds_timer` VALUES ('20210203', NULL, NULL, '2021-03-01 10:37:21', 34);
INSERT INTO `tbl_pds_timer` VALUES ('20210204', NULL, NULL, '2021-03-01 11:32:05', 35);
INSERT INTO `tbl_pds_timer` VALUES ('20210205', NULL, NULL, '2021-03-01 12:06:37', 36);
INSERT INTO `tbl_pds_timer` VALUES ('20210206', NULL, NULL, '2021-03-01 12:19:50', 37);
INSERT INTO `tbl_pds_timer` VALUES ('20210207', NULL, NULL, '2021-03-01 12:32:58', 38);
INSERT INTO `tbl_pds_timer` VALUES ('20210208', NULL, NULL, '2021-03-01 13:10:33', 39);
INSERT INTO `tbl_pds_timer` VALUES ('20210209', NULL, NULL, '2021-03-01 13:57:31', 40);
INSERT INTO `tbl_pds_timer` VALUES ('20210210', NULL, NULL, '2021-03-01 14:48:57', 41);
INSERT INTO `tbl_pds_timer` VALUES ('20210211', NULL, NULL, '2021-03-01 15:21:01', 42);
INSERT INTO `tbl_pds_timer` VALUES ('20210212', NULL, NULL, '2021-03-01 15:50:30', 43);
INSERT INTO `tbl_pds_timer` VALUES ('20210213', NULL, NULL, '2021-03-01 16:37:51', 44);
INSERT INTO `tbl_pds_timer` VALUES ('20210214', NULL, NULL, '2021-03-01 17:21:09', 45);
INSERT INTO `tbl_pds_timer` VALUES ('20210215', NULL, NULL, '2021-03-01 18:09:29', 46);
INSERT INTO `tbl_pds_timer` VALUES ('20210216', NULL, NULL, '2021-03-01 18:55:31', 47);
INSERT INTO `tbl_pds_timer` VALUES ('20210217', NULL, NULL, '2021-03-01 19:42:30', 48);
INSERT INTO `tbl_pds_timer` VALUES ('20210218', NULL, NULL, '2021-03-01 20:32:32', 49);
INSERT INTO `tbl_pds_timer` VALUES ('20210219', NULL, NULL, '2021-03-01 21:18:16', 50);
INSERT INTO `tbl_pds_timer` VALUES ('20210220', NULL, NULL, '2021-03-01 21:35:36', 51);
INSERT INTO `tbl_pds_timer` VALUES ('20210221', NULL, NULL, '2021-03-01 21:52:45', 52);
INSERT INTO `tbl_pds_timer` VALUES ('20210222', NULL, NULL, '2021-03-01 22:41:23', 53);
INSERT INTO `tbl_pds_timer` VALUES ('20210223', NULL, NULL, '2021-03-01 23:32:31', 54);
INSERT INTO `tbl_pds_timer` VALUES ('20210224', NULL, NULL, '2021-03-02 00:37:01', 55);
INSERT INTO `tbl_pds_timer` VALUES ('20210225', NULL, NULL, '2021-03-02 01:31:18', 56);
INSERT INTO `tbl_pds_timer` VALUES ('20210226', NULL, NULL, '2021-03-02 02:18:35', 57);
INSERT INTO `tbl_pds_timer` VALUES ('20210227', NULL, NULL, '2021-03-02 06:30:20', 58);
INSERT INTO `tbl_pds_timer` VALUES ('20210228', NULL, NULL, '2021-03-02 06:43:06', 59);

m_hutley · March 2, 2021, 2:39pm

First of all… look at that command for a moment. Just… exhale, look at that one line, and think about how that could be better written.

Okay. Now that that’s over, lets recenter.
What’s the join condition on your join? (And when you come up with that answer, what does that mean you’ve actually done?)

comunidadmexicanarom · March 2, 2021, 3:02pm

thanks

i have tried to could be better written the query but the update not working…

UPDATE `tbl_pds_timer` AS k
JOIN (
    SELECT
        sid,
        hhmmss,
        filename,
        TIMESTAMPDIFF( MINUTE, LAG( hhmmss ) OVER ( ORDER BY hhmmss ), hhmmss ) delta 
    FROM
        tbl_pds_timer 
    ORDER BY
        hhmmss 
    ) AS q 
    SET k.timer_min = q.delta

> Affected rows: 0
> Time: 0,003s

m_hutley · March 2, 2021, 3:08pm

Didn’t answer the question, either.

comunidadmexicanarom · March 3, 2021, 9:14am

Solved, thank you

UPDATE tbl_pds_timer
JOIN (
	SELECT
		sid,
		hhmmss,
		filename,
		TIMESTAMPDIFF( MINUTE, LAG( hhmmss ) OVER ( ORDER BY hhmmss ), hhmmss ) timer_min,
		TIMESTAMPDIFF( SECOND, LAG( hhmmss ) OVER ( ORDER BY hhmmss ), hhmmss ) timer_sec 
	FROM
		tbl_pds_timer 
	) AS data_for_update USING ( sid, hhmmss, filename ) 
	SET tbl_pds_timer.timer_min = data_for_update.timer_min,
	tbl_pds_timer.timer_sec = data_for_update.timer_sec;

system · June 2, 2021, 4:15pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Return the difference between two datetime expressions Databases mysql	10	1472	September 25, 2020
How to find difference between two timestamp using mysql Databases	2	2052	September 23, 2014
Calculate difference of values for records on outer ends of timeframe Databases	2	625	October 8, 2014
Subtracting 2 time fields Databases	4	560	September 20, 2014
Compare time between consequent rows using MySQL 5.5 Databases mysql	7	1976	April 10, 2022

Select difference between row dates in MySQL database 8.0.17

Related topics