jeudi 29 avril 2021

MySQL Generate random timestamps from range, order it by date asc, then use it to update missing values from another table

I have a table "ips", where I store my download logs. Accidentally, I forgot to add timestamp for it (yea, stupid mistake)... Now, I have fixed it, but there are already 65.5k entries without timestamp.. Is there a way, how to add random timestamp from date range to fill NULL timestamps?

I was able to generate timestamps list using this queries:

SET @MIN = '2020-04-05 18:30:00';
SET @MAX = NOW();
SELECT TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN) as dldate FROM ips WHERE name="filename1" ORDER BY dldate ASC;

It generated the exact count of entries I need for specific filename, but I have absolutely no idea, how to use this list to update already existing entries in my "ips" table and KEEP IT ORDERED by "dldate"...

When I was testing it, I was close, when I used this query (I was afraid to use UPDATE to not mess my data up, so I used just SELECT):

SELECT ips.id, ips.name, t1.dldate FROM (SELECT id, name FROM ips WHERE name="filename1") ips INNER JOIN (SELECT ips.id as id, TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN) as dldate FROM ips WHERE name="filename1" ORDER BY dldate ASC) t1 ON (ips.id=t1.id) ORDER BY ips.id ASC;

That worked, but timestaps are purely random (obviously :D), and I need them to "respect" id from "ips" table (starting with lower timestamp for lowest id, and then continuously higher timestamps for higher ids).

I'm getting this:

+------+-----------+---------------------+
| id   | name      | dldate              |
+------+-----------+---------------------+
|   15 | filename1 | 2020-12-18 21:35:03 |
| 1118 | filename1 | 2020-12-18 13:34:47 |
| 1141 | filename1 | 2020-08-07 12:49:46 |
| 1142 | filename1 | 2020-11-29 00:43:31 |
| 1143 | filename1 | 2020-05-13 03:00:16 |
| 1286 | filename1 | 2020-12-14 09:58:50 |
| 1393 | filename1 | 2021-04-14 06:45:23 |
| 1394 | filename1 | 2021-03-03 17:42:25 |
| 1395 | filename1 | 2020-09-03 05:56:56 |
| .... |
|62801 | filename1 | 2021-01-05 21:21:29 |
+------+-----------+---------------------+

And I would like to get this:

+------+-----------+---------------------+
| id   | name      | dldate              |
+------+-----------+---------------------+
|   15 | filename1 | 2020-04-05 21:35:03 |
| 1118 | filename1 | 2020-04-18 13:34:47 |
| 1141 | filename1 | 2020-05-07 12:49:46 |
| 1142 | filename1 | 2020-06-29 00:43:31 |
| 1143 | filename1 | 2020-08-13 03:00:16 |
| 1286 | filename1 | 2020-10-14 09:58:50 |
| 1393 | filename1 | 2020-12-14 06:45:23 |
| 1394 | filename1 | 2021-01-03 17:42:25 |
| 1395 | filename1 | 2021-03-03 05:56:56 |
| .... |
|62801 | filename1 | 2021-04-29 14:21:29 |
+------+-----------+---------------------+

Is there any way, how to achieve this output and how to use it with UPDATE statement instead of SELECT with INNER JOIN?

Thank you for help!




Aucun commentaire:

Enregistrer un commentaire