Unable to DATE_SUB correctly in a double inner join-CodePudding

I'm writing a query to update some chained rowsthat looks like this

SELECT T.Id, 
T.Id   1 AS NewID, 
T.DifferenceTime, 
RAT.AllowedDate, 
RAT.ExpirationDate, 
DATE_SUB(RAT.AllowedDate, INTERVAL T.DifferenceTime MINUTE) AS NewAllowedDate, 
DATE_SUB(RAT.ExpirationDate, INTERVAL T.DifferenceTime MINUTE) AS NewExpiringDate
FROM ROOMS_ACCESS_TOKENS RAT 
INNER JOIN (
 SELECT T2.*,
 ABS(TIMESTAMPDIFF(MINUTE,T2.ExpirationDate,UTC_TIMESTAMP())) AS DifferenceTime 
 FROM ROOMS_ACCESS_TOKENS RAT
 INNER JOIN (
    SELECT * 
    FROM ROOMS_ACCESS_TOKENS 
    WHERE Expired = 0 AND ExpirationDate > UTC_TIMESTAMP()
 ) AS T2 ON T2.IdRoom = RAT.IdRoom AND T2.ChainId = RAT.ChainId
 WHERE RAT.IdRoom = 1 
 AND RAT.AccessToken = '5092FFA0B3721AE5EC757649DE983F8020220104215810' 
 AND RAT.RefId = 'B49F723F2FE0FD38063DA26F3EAEDBED' 
 AND RAT.KeyCode = '2773217uysagkasd8wqejhjkasdsm' 
 AND RAT.ExpirationDate > UTC_TIMESTAMP()
 AND RAT.Expired = 0
) AS T ON T.IdRoom = RAT.IdRoom 
AND T.ChainId = RAT.ChainId 
AND T.AccessToken = RAT.AccessToken 
AND T.RefId = RAT.RefId 
AND T.AllowedDate = RAT.AllowedDate

This suppose to select a row by IdRoom, AccessToken, RefId, KeyCode and be above a certain date and not Expired. Then get all chainded rows by ChainId and calculate time different between UTC_TIMESTAMP() and row ExpirationDate. (I guess it works till this point) With that difference in minute, I want to calculate new datetimes but it doesn't work.

I've attached a screenshot with results

As you can see, it looks like NewAllowedDate and NewExpiringDate get calculated only for the first row.. But NewID gets calculated for all of them..

I can't understand where my query is wrong. Any ideas?

Here the table and some data insert example

CREATE TABLE `ROOMS_ACCESS_TOKENS` (   `Id` int(11) NOT NULL,   `IdRoom` int(11) DEFAULT NULL,   `AccessToken` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,   `RefId` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,   `KeyCode` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,   `Allowed` int(1) NOT NULL,   `Used` int(1) NOT NULL,   `Expired` int(1) NOT NULL DEFAULT '0',   `ChainId` int(11) NOT NULL DEFAULT '1',   `StartDate` datetime DEFAULT NULL,   `AllowedDate` datetime NOT NULL,   `ExpirationDate` datetime DEFAULT NULL,   `ResidenceTime` int(5) NOT NULL,   `Url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ROOMS_ACCESS_TOKENS` (`Id`, `IdRoom`, `AccessToken`, `RefId`, `KeyCode`, `Allowed`, `Used`, `Expired`, `ChainId`, `StartDate`, `AllowedDate`, `ExpirationDate`, `ResidenceTime`, `Url`) VALUES (1, 1, '5092FFA0B3721AE5EC757649DE983F8020220104215810', 'B49F723F2FE0FD38063DA26F3EAEDBED', '2773217uysagkasd8wqejhjkasdsm', 1, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-04 22:30:46', '2022-01-04 23:30:46', 60, '/it'), (2, 1, '9CAB5CABB559260D41366E1041B46D0320220104233046', 'B086A4B3F4996C69B4DBB206C2E0D799', '2773217uysagkasd8wqejhjkasdsm', 0, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-04 23:31:46', '2022-01-05 00:31:46', 60, '/it'), (3, 1, '3A2AE9CAC195B9938D25AE654790FAE020220104233046', 'E756AF83048259114F7E0BA04FFA799E', '2773217uysagkasd8wqejhjkasdsm', 0, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-05 00:32:46', '2022-01-05 01:32:46', 60, '/it'), (4, 1, '17267FAC80875252F5F1E2585D078B1D20220104233046', '62143086CF25D276FD4C6D4465BDD41E', '2773217uysagkasd8wqejhjkasdsm', 0, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-05 01:33:46', '2022-01-05 02:33:46', 60, '/it'), (5, 1, '39F47036B85694A231668BB8DFBF3CD120220104233046', 'C2C8F545B9FF7C59D50958C14EE61BF7', '2773217uysagkasd8wqejhjkasdsm', 0, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-05 02:34:46', '2022-01-05 03:34:46', 60, '/it'), (6, 1, '61919A70C0D7AD4FB642BE1A3D6FE10420220104233046', 'EB1F2AF642C779B29F89023260A3AC94', '2773217uysagkasd8wqejhjkasdsm', 0, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-05 03:35:46', '2022-01-05 04:35:46', 60, '/it'), (7, 1, '62BCFCCAC7F2EAFD5B1E06B32A810C1420220104233046', 'DDFB911F2197B50861AABD77005D9946', '2773217uysagkasd8wqejhjkasdsm', 0, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-05 04:36:46', '2022-01-05 05:36:46', 60, '/it'), (8, 1, '6F72CCB6F3DF8F90C1A2911A7F1E097720220104233046', 'CCD116F4D4EFA9AE7D9B8DA780C5A73E', '2773217uysagkasd8wqejhjkasdsm', 0, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-05 05:37:46', '2022-01-05 06:37:46', 60, '/it'), (9, 1, 'E8610F5C7551DA2695360B77431BBB1020220104233046', '49ABCCBDF6FC02FF290357B6A29DB2FC', '2773217uysagkasd8wqejhjkasdsm', 0, 1, 0, 1, '2022-01-04 22:30:46', '2022-01-05 06:38:46', '2022-01-05 07:38:46', 60, '/it'), (10, 1, '78BFEAF3EC52298B9EB5CDA8920168FE20220104233046', '755E481F29003C1EDC3A682D5037EF9F', '2773217uysagkasd8wqejhjkasdsm', 0, 0, 0, 1, '2022-01-04 22:30:46', '2022-01-05 07:39:46', '2022-01-05 08:39:46', 60, '/it');


ALTER TABLE `ROOMS_ACCESS_TOKENS`   ADD PRIMARY KEY (`Id`),   ADD KEY `IX_ACCESS_TOKEN` (`AccessToken`),   ADD KEY `KeyCode` (`KeyCode`),   ADD KEY `RefId` (`RefId`),   ADD KEY `IdRoom` (`IdRoom`),   ADD KEY `Allowed` (`Allowed`),   ADD KEY `Used` (`Used`),   ADD KEY `Expired` (`Expired`);

ALTER TABLE `ROOMS_ACCESS_TOKENS`   MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

CodePudding user response：

After many tries I've noticed that even with a simpler query, Mysql is not able to calculate new datetimes. I'll make another question with the right title.

SELECT
TIMESTAMPDIFF(MINUTE,RAT.ExpirationDate,UTC_TIMESTAMP()) AS T,
TIMESTAMPDIFF(MINUTE,UTC_TIMESTAMP(),RAT.ExpirationDate) AS T2,
RAT.AllowedDate,
RAT.AllowedDate - INTERVAL TIMESTAMPDIFF(MINUTE,UTC_TIMESTAMP(),RAT.ExpirationDate) MINUTE AS NewAllowedDate,
DATE_SUB(RAT.AllowedDate, INTERVAL TIMESTAMPDIFF(MINUTE,UTC_TIMESTAMP(),RAT.ExpirationDate) MINUTE) AS NewAllowedDate2,
DATE_ADD(RAT.AllowedDate, INTERVAL TIMESTAMPDIFF(MINUTE,RAT.ExpirationDate,UTC_TIMESTAMP()) MINUTE) AS NewAllowedDate3,
RAT.AllowedDate - INTERVAL TIMESTAMPDIFF(MINUTE,RAT.ExpirationDate,UTC_TIMESTAMP()) MINUTE AS NewAllowedDate4
FROM ROOMS_ACCESS_TOKENS RAT

CodePudding user response：

These sound redundant. The former one is dynamic; the latter one is potentially not-yet-set.

AND RAT.ExpirationDate > UTC_TIMESTAMP()
AND RAT.Expired = 0

I do not understand your date arithmetic, especially since the columns, being DATETIME, have 'second' resolution, yet the arithmetic involved 'MINUTEs'.

What is the meaning of "ABS" in ABS(TIMESTAMPDIFF(MINUTE,T2.ExpirationDate, UTC_TIMESTAMP())) AS DifferenceTime

Please do not use the same alias twice; it confuses me, and maybe the parser. (I am thinking of RAT, at least.)