Time anomaly in mysql table version 5.5.62-log

Databases
#1

Hello there,

I need your help.

This is part of my table on MySQL database

enter image description here

I realized that in this case more than one hour has passed between the previous and the next row

mysql> SELECT
	TIMEDIFF(
		'2020-04-20 16:00:00',
		'2020-04-20 14:58:56'
	);
+------------------------------------------------------------------+
| TIMEDIFF(
		'2020-04-20 16:00:00',
		'2020-04-20 14:58:56'
	) |
+------------------------------------------------------------------+
| 01:01:04                                                         |
+------------------------------------------------------------------+
1 row in set

this is not possible because the data is downloaded maximum from the source every five minutes

I have tried this query without success because the return is all zero

> SELECT
> 	`sDatetime`,
> 	`sElement`,
> 	`sDescri`,
> 	MAX(
> 		TIME_TO_SEC(
> 			TIMEDIFF(
> 				`sDatetime`,
> 				`sDatetime`
> 			)
> 		)
> 	) AS 'Worst',
> 	MIN(
> 		TIME_TO_SEC(
> 			TIMEDIFF(
> 				`sDatetime`,
> 				`sDatetime`
> 			)
> 		)
> 	) AS 'Best',
> 	AVG(
> 		TIME_TO_SEC(
> 			TIMEDIFF(
> 				`sDatetime`,
> 				`sDatetime`
> 			)
> 		)
> 	) AS 'Average'
> FROM
> 	tbl_2020
> GROUP BY
> 	`sDatetime`,
> 	`sElement`,
> 	`sDescri`;

how can i find this anomaly in mysql table?

my version of MySQL is 5.5.62-log

the name of column is sDatetime the type is Datetime .

I need extract all rows when the datediff from rows is more than an hour… the example is in question

any suggestion, please?

thanks in advance for any help

#2

the only way to solve this is for you to confirm how to choose, for any row, which row is “previous”

is it based only on sequence of those datetime values?

then you need a self-join, where each row is matched to the row with the greatest datetime value that is less than the datetime value of that row

    SELECT ...
      FROM tbl_2020 AS this
    LEFT OUTER
      JOIN tbl_2020 AS prev
        ON prev.sDatetime =
           ( SELECT MAX(sDatetime)
               FROM tbl_2020
              WHERE sDatetime < this.sDatetime )
#3

Hi, thanks for help.

I have tried your suggestion

[Err] 2013 - Lost connection to MySQL server during query

but I don’t understand how it can distinguish when more than an hour has passed between one row and the next row … do you let me know please?

#4 
    SELECT this.sDatetime
         , prev.sDatetime
      FROM tbl_2020 AS this
    LEFT OUTER
      JOIN tbl_2020 AS prev
        ON prev.sDatetime =
           ( SELECT MAX(sDatetime)
               FROM tbl_2020
              WHERE sDatetime < this.sDatetime )  
     WHERE TIMEDIFF(this.sDatetime, prev.sDatetime) > '01:00:00'

IMPORTANT: make sure there’s an index on sDatetime

1 Like
#5

Not to hijack the thread too much, but a question about the choice of MAX.

Is the use of MAX (with index) superior to an ORDER BY… DESC LIMIT 1?

[and if so, is that purely because of the index?]

#6

if you’re asking me (because OP uses MAX in the original post too), the answer is of course

my MAX is inside a correlated subquery – each this row will be joined to only one prev row, which is the one that has the latest datetime that is less than the this row