samedi 3 juin 2017

How to add random interval to timestamp in MySQL?

We got the following table mytable:

+----+------------+------------+
| id | created    | expired    |
+----+------------+------------+
|  1 | 1496476314 | NULL |
|  6 | 1496477511 | NULL |
|  7 | 1496477518 | NULL |
| 12 | 1496477534 | NULL |
| 13 | 1496477536 | NULL |
| 15 | 1496477541 | NULL |
| 21 | 1496477548 | NULL |
| 22 | 1496477550 | NULL |
| 26 | 1496477565 | NULL |
| 28 | 1496477566 | NULL |
| 29 | 1496477583 | NULL |
+----+------------+------------+

We'd like to do the following:

set expired = created + random(15 - 30 minutes) as unix_timestamp where expired is null;

I currently have no idea to done it. If u just can give me some ideas it would save my day.

I tried to convert the created timestamp to date_time and add to that date_time the wanted 15 - 30 minutes and finally convert the new_date_time back to unix_timestamp, but there should be an easier way.




Aucun commentaire:

Enregistrer un commentaire