different dates give same TIMEDIFF-CodePudding

I am trying to calculate the time between two different dates. I calculate it to seconds with this sql command FLOOR(TIME_TO_SEC(TIMEDIFF(CURRENT_TIMESTAMP, post.created)) / 60) but it returns the same value for different dates like the date 2021-11-05 14:49:13 and the date 2021-12-16 21:31:09 both return the value 50339for the newer dates this method works just fine but for some reasen it does not work for those dates.

here is the data from the database in JSON format. left the difference from the Current timstamp to the date the item was created

{"Difference":"50339","created":"2021-11-05 14:49:13","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-11-05 14:49:13","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-09 18:12:47","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-09 18:13:16","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-09 18:13:44","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-14 21:29:58","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-14 21:41:33","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-14 21:42:09","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-15 18:58:22","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-15 19:00:46","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 18:26:55","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 21:31:07","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 21:31:08","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 21:31:09","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"50339","created":"2021-12-16 21:31:09","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"49002","created":"2021-12-18 17:01:37","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"48831","created":"2021-12-18 19:53:09","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43467","created":"2021-12-22 13:17:00","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43467","created":"2021-12-22 13:17:01","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43467","created":"2021-12-22 13:17:02","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43467","created":"2021-12-22 13:17:02","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43466","created":"2021-12-22 13:17:22","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43464","created":"2021-12-22 13:19:57","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"43464","created":"2021-12-22 13:20:07","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"40784","created":"2021-12-24 09:59:46","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"38857","created":"2021-12-25 18:06:24","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"38827","created":"2021-12-25 18:37:17","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"38826","created":"2021-12-25 18:37:37","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"37542","created":"2021-12-26 16:02:13","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"37540","created":"2021-12-26 16:03:24","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"35918","created":"2021-12-27 19:05:51","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"35917","created":"2021-12-27 19:06:35","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"18531","created":"2022-01-08 20:52:38","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"18531","created":"2022-01-08 20:52:39","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:07","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:11","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:12","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:13","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:14","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:16","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:17","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:17","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"15640","created":"2022-01-10 21:04:18","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"13193","created":"2022-01-12 13:51:15","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"},
{"Difference":"29","created":"2022-01-21 17:14:50","CURRENT_TIMESTAMP":"2022-01-21 17:44:20"}
]

CodePudding user response：

The TIMEDIFF function in MySQL returns a TIME value which has a range of '-838:59:59' to '838:59:59'.

TIME values may range from '-838:59:59' to '838:59:59'.

The time difference of 2021-11-05 14:49:13 and 2021-12-16 21:31:09 from 2022-01-21 17:44:20 will be 1850:55:07 and 860:13:11 respectively. Both of these are outside the defined range of TIME values so they will be trimmed to the nearest value i.e. 838:59:59.

By default, values that lie outside the TIME range but are otherwise valid are clipped to the closest endpoint of the range. For example, '-850:00:00' and '850:00:00' are converted to '-838:59:59' and '838:59:59'.

838:59:59 => 3020399 seconds => 50339 minutes (approx.)

To overcome this issue, you can use the TIMESTAMPDIFF function or UNIX_TIMESTAMP function (Convert the values to seconds first and then subtract).