We help IT Professionals succeed at work.

update error

hello expert
I want to update column time_1 in the experiment table.
I want to update time_1 column between 1-10 minutes.
I want to update the current time randomly 1 to 10 minutes ago
I have written the sample data below
create table deneme
(
time_1 date
)
insert into deneme values(to_date('16.01.2020 17:17:27','DD.MM.YYYY hh24:mi:ss'))

insert into deneme values(to_date('16.01.2020 12:12:27','DD.MM.YYYY hh24:mi:ss'))

insert into deneme values(to_date('16.01.2020 12:07:27','DD.MM.YYYY hh24:mi:ss'))

insert into deneme values(to_date('16.01.2020 12:13:44','DD.MM.YYYY hh24:mi:ss'))

insert into deneme values(to_date('16.01.2020 12:33:44','DD.MM.YYYY hh24:mi:ss'))

Open in new window


before update=16.01.2020 17:17:27  && ı want  after update 16.01.2020 17:10:27  --There's a 7-minute time difference between the two dates.
before update=16.01.2020 12:12:27  && ı want  after update  16.01.2020 12:09:27--There's a 3-minute time difference between the two dates.
before update=16.01.2020 12:07:27 && ı want  after update  16.01.2020 12:02:27 --There's a 5-minute time difference between the two dates.
before update=16.01.2020 12:13:44 && ı want  after update 16.01.2020 12:11:44 --There's a 2-minute time difference between the two dates.
before update=16.01.2020 12:33:44 && ı want  after update 16.01.2020 12:24:44 -- There's a 9-minute time difference between the two dates.
I've written an update like the following but it gives the error.
update deneme xx
set
 xx.time_1 = xx.time_1 - (interval (round(DBMS_RANDOM.VALUE (1, 10))) minute ) 

Open in new window

How can I fix where I'm making the error.
thanks.
Comment
Watch Question

Senior Oracle DBA
Commented:
Without trying it, and without knowing the error message, my guess is you are trying to mix INTERVALs and DATEs, that doesn't work.

For pure DATE math, try this:
update deneme xx
set
 xx.time_1 = xx.time_1 - (round(DBMS_RANDOM.VALUE (1, 10))/1440)

Open in new window


DATE math works with fractions of a day.  1440 is the number of minutes in a day.

INTERVAL types are to be used with TIMESTAMP datatypes.

Author

Commented:
thank you so much.
 it  works.