Return the difference between two datetime expressions

UncleVince · June 21, 2020, 4:43pm

Hi,

The version of MySQL database is 5.5.62

On my MySQL table I need check if the TIMEDIFF() between two rows is greater than the value three minutes

If the TIMEDIFF() is less I update the columns xDisabled with 1 value

This is my table

+------+---------------------+---------+-----------+
| xID  | xDate_xHourMinute   | xLines  | xDisabled |
+------+---------------------+---------+-----------+
| 1405 | 2020-06-21 05:24:19 | 3018122 |           |
| 1424 | 2020-06-21 05:22:53 | 3018122 |           |
| 1462 | 2020-06-21 05:22:03 | 3018122 |           |
| 1473 | 2020-06-21 05:18:59 | 3018122 |           |
| 1481 | 2020-06-21 05:18:03 | 3018122 |           |
+------+---------------------+---------+-----------+
5 rows in set

I have tried without success this sql query because all rows on the table are updated

UPDATE `xMinutesSet` a
JOIN `xMinutesSet` b ON a.xLines = B.xLines
AND TIMEDIFF(
    a.xDate_xHourMinute,
    b.xDate_xHourMinute
) < 3
SET a.xDisabled = 1;


+------+---------------------+---------+-----------+
| xID  | xDate_xHourMinute   | xLines  | xDisabled |
+------+---------------------+---------+-----------+
| 1405 | 2020-06-21 05:24:19 | 3018122 |         1 |
| 1424 | 2020-06-21 05:22:53 | 3018122 |         1 |
| 1462 | 2020-06-21 05:22:03 | 3018122 |         1 |
| 1473 | 2020-06-21 05:18:59 | 3018122 |         1 |
| 1481 | 2020-06-21 05:18:03 | 3018122 |         1 |
+------+---------------------+---------+-----------+
5 rows in set

I need this return

+------+---------------------+---------+-----------+
| xID  | xDate_xHourMinute   | xLines  | xDisabled |
+------+---------------------+---------+-----------+
| 1405 | 2020-06-21 05:24:19 | 3018122 |         1 | 00:01:26
| 1424 | 2020-06-21 05:22:53 | 3018122 |         1 | 00:00:50 
| 1462 | 2020-06-21 05:22:03 | 3018122 |           | 00:03:04
| 1473 | 2020-06-21 05:18:59 | 3018122 |         1 | 00:00:56
| 1481 | 2020-06-21 05:18:03 | 3018122 |           |
+------+---------------------+---------+-----------+
5 rows in set

How to do resolve this? Please, any suggestion

My table below

DROP TABLE IF EXISTS `xminutesset`;
CREATE TABLE `xminutesset` (
  `xID` int(11) NOT NULL AUTO_INCREMENT,
  `xDate_xHourMinute` datetime DEFAULT NULL,
  `xLines` varchar(255) DEFAULT NULL,
  `xDisabled` int(1) DEFAULT NULL,
  PRIMARY KEY (`xID`)
) ENGINE=InnoDB;

-- ----------------------------
-- Records of xminutesset
-- ----------------------------
INSERT INTO `xminutesset` VALUES ('1405', '2020-06-21 05:24:19', '3018122', null);
INSERT INTO `xminutesset` VALUES ('1424', '2020-06-21 05:22:53', '3018122', null);
INSERT INTO `xminutesset` VALUES ('1462', '2020-06-21 05:22:03', '3018122', null);
INSERT INTO `xminutesset` VALUES ('1473', '2020-06-21 05:18:59', '3018122', null);
INSERT INTO `xminutesset` VALUES ('1481', '2020-06-21 05:18:03', '3018122', null);

r937 · June 22, 2020, 12:47am

what a nicely organized post – full explanation, even sample data

i see two problems

first, TIMEDIFF returns a time which consists of hours, minutes and seconds

looks like you’re getting results where the difference is less than 3 hours, not 3 minutes

suggest you use TIMESTAMPDIFF instead

second, you are matching each row of a given xLines with every other row for that same xLines

i rather suspect you want to compare only to the “next” value, i.e. successive rows as determined by xID

please confirm both my observations

UncleVince · June 22, 2020, 2:02pm

thank you

I confirm your observations …

r937 · June 22, 2020, 4:01pm

okay, run this and inspect the results, to assure that it’s returning the right stuff, before changing it from SELECT to UPDATE

SELECT curr.xDate_xHourMinute AS curr_time
     , next.xDate_xHourMinute AS next_time   
     , TIMESTAMPDIFF( MINUTE
                    , curr.xDate_xHourMinute
                    , next.xDate_xHourMinute ) AS minutes
  FROM xMinutesSet AS curr
LEFT OUTER
  JOIN xMinutesSet AS next
    ON next.xLines = curr.xLines
   AND next.xID =
       ( SELECT MIN(xID)
           FROM xMinutesSet
          WHERE xLines = curr.xLines
            AND xID > curr.xID )

UncleVince · June 23, 2020, 1:30pm

Many thanks for help

the return…

mysql> SELECT
	curr.xDate_xHourMinute AS curr_time,
	next.xDate_xHourMinute AS next_time,
	TIMESTAMPDIFF(
		MINUTE,
		curr.xDate_xHourMinute,
		next.xDate_xHourMinute
	) AS minutes
FROM
	xMinutesSet AS curr
LEFT OUTER JOIN xMinutesSet AS next ON next.xLines = curr.xLines
AND next.xID = (
	SELECT
		MIN(xID)
	FROM
		xMinutesSet
	WHERE
		xLines = curr.xLines
	AND xID > curr.xID
);
+---------------------+---------------------+---------+
| curr_time           | next_time           | minutes |
+---------------------+---------------------+---------+
| 2020-06-21 05:24:19 | 2020-06-21 05:22:53 |      -1 |
| 2020-06-21 05:22:53 | 2020-06-21 05:22:03 |       0 |
| 2020-06-21 05:22:03 | 2020-06-21 05:18:59 |      -3 |
| 2020-06-21 05:18:59 | 2020-06-21 05:18:03 |       0 |
| 2020-06-21 05:18:03 | NULL                | NULL    |
+---------------------+---------------------+---------+
5 rows in set

r937 · June 23, 2020, 2:28pm

so it appears your timestamps go down and your ids go up

may i just say, that’s weird

okay, do you know what to do next?

UncleVince · June 24, 2020, 2:44pm

no…
I really don’t know… sorry

r937 · June 25, 2020, 3:02pm

for starters, flip the inequality in the subquery and change MIN to MAX

UncleVince · June 25, 2020, 5:21pm

Ok

you mean this?

mysql> SELECT
	curr.xDate_xHourMinute AS curr_time,
	next.xDate_xHourMinute AS next_time,
	TIMESTAMPDIFF(
		MINUTE,
		curr.xDate_xHourMinute,
		next.xDate_xHourMinute
	) AS minutes
FROM
	xMinutesSet AS curr
LEFT OUTER JOIN xMinutesSet AS next ON next.xLines = curr.xLines
AND next.xID = (
	SELECT
		MAX(xID)
	FROM
		xMinutesSet
	WHERE
		xLines = curr.xLines
	AND xID < curr.xID
);
+---------------------+---------------------+---------+
| curr_time           | next_time           | minutes |
+---------------------+---------------------+---------+
| 2020-06-21 05:24:19 | NULL                | NULL    |
| 2020-06-21 05:22:53 | 2020-06-21 05:24:19 |       1 |
| 2020-06-21 05:22:03 | 2020-06-21 05:22:53 |       0 |
| 2020-06-21 05:18:59 | 2020-06-21 05:22:03 |       3 |
| 2020-06-21 05:18:03 | 2020-06-21 05:18:59 |       0 |
+---------------------+---------------------+---------+
5 rows in set

r937 · June 25, 2020, 6:45pm

yeah, that

(except i guess “curr” could be named “prev” and “next” should be named “curr”)

do you see now how this can give what you want – “if the TIMEDIFF() between two rows is greater than the value three minutes”

system · September 25, 2020, 1:45am

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

Topic		Replies	Views
Date Diff from Mysql DateTimer PHP	2	307	July 19, 2010
Select difference between row dates in MySQL database 8.0.17 Databases mysql	5	1079	June 2, 2021
Difference in minutes between two dates Databases	4	16995	October 8, 2014
Difference between two times in hours with MySQL and PHP PHP	9	16988	October 8, 2014
Difference between 2 datetime columns in phpmyadmin PHP	10	13072	May 10, 2017

Return the difference between two datetime expressions

Related topics