PostgreSQL Time without timezone

user_13102622 0
Viewed 37

I have a 3k data in my database and i would like to generate a random time for the column of operation_start and operation_end.

the code below is the one I'm using manually update each operation_start and operation_end

UPDATE dashboard.inventory
SET operation_start = '1:00:43',
operation_end = '2:00:43',
update_date = NOW()
WHERE terminal_id = '01231238';

but updating all 3k data is a pain. I tried googling but there seems to be no answer except for with datestamp

postgresql sql
1 Answers

You can use time arithmetic. However, this might be simpler in two steps because you seem to want the end time to be one hour after the start:

UPDATE dashboard.inventory
    SET operation_start = '00:00:00' + floor(random() * 23*60*60) * interval '1 second',
        update_date = NOW()
    WHERE terminal_id = '01231238';

UPDATE dashboard.inventory
    SET operation_end = operation_start + interval '1 hour'
    WHERE terminal_id = '01231238';
user_11440350
Related