Update datetime field randomly according known datetime

Question

Is it possible to query a database row and according from one field (DATETIME) randomly add 1-3 days(better business days) into another field in the same row?

So query for known DATETIME field from which need to get time and then randomly add 1-3 days:

SELECT complete_date 
FROM training 
WHERE tr_id = 18;

AND query for the field which is needed to be updated..

UPDATE training 
SET finalized = (rand + (1-3 days)) 
WHERE tr_id = 18

MySQL Workbench software.

random numbers are database specific -- what database software are you using? — craiglm, Nov 27 '13 at 07:47
if i understand correctly, currently you have a field complete date. and you want to have another field finalised which will be 1-3 days after the completed date. am i right? — Shann, Nov 27 '13 at 07:47

score 3 · Accepted Answer · edited May 23 '17 at 12:19

3

Yes, you need the INTERVAL keyword.

UPDATE training SET finalized = complete_date + INTERVAL FLOOR(1 + (RAND() * 3)) DAY

Adding working days is a little more difficult. Have a look at this stored procedue.

edited May 23 '17 at 12:19

Community

1
1

answered Nov 27 '13 at 08:01

Arnold Daniels

16,516
4
53
82

Great, its that I wanted thank you, but is it possible to add only bussiness days, so days which are not saturdays and sundays? :) – Sangsom Nov 27 '13 at 08:08
Adding working days is a little more difficult. Have a look at this [stored procedue](http://stackoverflow.com/questions/17731260/mysql-function-to-add-a-number-of-working-days-to-a-datetime#17731608). – Arnold Daniels Nov 27 '13 at 08:10

score 0 · Answer 2 · answered Nov 27 '13 at 08:00

create table training (tr_id integer, complete_date datetime, finalized datetime);

insert into training values(18, NOW(), null);
insert into training values(18, NOW(), null);
insert into training values(18, NOW(), null);
insert into training values(18, NOW(), null);

update training set finalized = DATE_ADD(complete_date, Interval RAND()*24*3 HOUR) where tr_id = 18;

So you should add random number to some date to get new "random date". In my example I add random from 0 to 72 hours to initial date 'complete_date', you can use SECONDS or what you need.

It is not so easy to support only buisness days. (what about NY hollidays in Russia - around 10 days :)

Update datetime field randomly according known datetime

2 Answers2