I have a table called rainfall
. It has 2 columns (sensorID, area, time, rainfall, redalert)
I want to fill the time column with random times in intervals of 5 minutes (e.g 12:55, 04:30, 07:45) . I managed to get the random times by using this Python code:
import mysql.connector import random import datetime import time MINTIME = datetime.datetime(2020,4,1,0,0,0) MAXTIME = datetime.datetime(2020,7,1,0,0,0) mintime_str = int(time.mktime(MINTIME.timetuple())) #convert date to INT maxtime_str = int(time.mktime(MAXTIME.timetuple())) #convert date to INT no_steps = (maxtime_str - mintime_str)//(5*6) #state the number of minutes interval to 5 minutes sql = "UPDATE rainfall SET date = %s" rand = '' for RECORD in range(108): random_slot = random.randint(0, no_steps) random_ts = mintime_str + 5*60 * random_slot RANDOMTIME = datetime.datetime.fromtimestamp(random_ts) rand = datetime.datetime.strftime(RANDOMTIME, '%H:%M') val = (rand,) mycursor.execute(sql, val) raindb.commit() print(mycursor.rowcount, "record(s) affected")
The problem is that this code fill all rows in time with 1 value only:
I need different values in each row. It fine if some rows have duplicate time values.
Thanks in advance
Advertisement
Answer
You can do it with SQL only:
update rainfall set time = concat( lpad(floor(rand() * 24), 2, '0'), ':', lpad(floor(rand() * 12) * 5, 2, '0') );