7 Replies - 333 Views - Last Post: 19 August 2018 - 04:13 AM

#1 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

How do you update date in rows increment of x second?

Posted 18 August 2018 - 10:52 PM

update test1
set start_date = sysdate
where table_id between 223 and 228;



I want to update date of these in increment of x seconds.

So
table_id 223 has 2018-08-19 09:10:15
table_id 224 has 2018-08-19 09:10:25
table_id 225 has 2018-08-19 09:10:35

something like that. How should I proceed this? I am using sql developer and I have been updating one by one.. :(

Is This A Good Question/Topic? 0
  • +

Replies To: How do you update date in rows increment of x second?

#2 ndc85430   User is offline

  • I think you'll find it's "Dr"
  • member icon

Reputation: 981
  • View blog
  • Posts: 3,868
  • Joined: 13-June 14

Re: How do you update date in rows increment of x second?

Posted 18 August 2018 - 11:06 PM

Write a program in your favourite programming language?
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15306
  • View blog
  • Posts: 61,381
  • Joined: 12-June 08

Re: How do you update date in rows increment of x second?

Posted 18 August 2018 - 11:16 PM

Cursors are an option as well.
Was This Post Helpful? 0
  • +
  • -

#4 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: How do you update date in rows increment of x second?

Posted 18 August 2018 - 11:39 PM

so without using program or procedure, there's no way to do this with sql query alone?
Was This Post Helpful? 0
  • +
  • -

#5 ndc85430   User is offline

  • I think you'll find it's "Dr"
  • member icon

Reputation: 981
  • View blog
  • Posts: 3,868
  • Joined: 13-June 14

Re: How do you update date in rows increment of x second?

Posted 18 August 2018 - 11:54 PM

I suppose there might be (maybe what modi123_1 is suggesting), but I haven't used more advanced SQL features. Writing a program is quite cheap, though, in my mind!

This post has been edited by ndc85430: 18 August 2018 - 11:56 PM

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6819
  • View blog
  • Posts: 28,246
  • Joined: 12-December 12

Re: How do you update date in rows increment of x second?

Posted 19 August 2018 - 12:37 AM

I don't use Oracle but a starting point could be a search of "oracle loop".

Loop statements

As indicated by modi, you might explore a cursor for loop, incrementing a variable by 10 with each fetch (and update).
Was This Post Helpful? 0
  • +
  • -

#7 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: How do you update date in rows increment of x second?

Posted 19 August 2018 - 02:08 AM

Thanks but found answer. :)
Was This Post Helpful? 0
  • +
  • -

#8 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7492
  • View blog
  • Posts: 15,527
  • Joined: 16-October 07

Re: How do you update date in rows increment of x second?

Posted 19 August 2018 - 04:13 AM

View Postshin777, on 19 August 2018 - 04:08 AM, said:

Thanks but found answer.

Unfortunate you chose not to share it.

First, never use a loop in SQL if there is any declarative alternative.

Second, sysdate is actually a float in oracle. So, sysdate gets you right now, sysdate + 1 gets you a day from now with the exact same time of day, sysdate + 0.5 gets you 12 hours from now.

Let's say my starting id has a date I like:
update test1
  set start_date = sysdate
  where table_id = 223



How do I make next five values sequentially follow by some fixed amount? I'll choose an hour, or 1/24 of a day.

The very next one might be:
update test1
  set start_date = (select start_date from test1 where table_id = 223) + (1/24)
  where table_id = 224



The one after that, 2 * (1/24) + base_date, and so on.

Since our ids are sequential, this become pretty trivial:
update test1 a
  set start_date = (select start_date from test1 where table_id = 223) + ((1/24) * (a.table_id - 223))
  where table_id between 224 and 229



Hope this helps. Well, at least someone else who might be looking for an answer to this question in the future.

As an aside, storing such values should be an obvious red flag. If you can use stored data to derive a value, like some time offset, then there is no need to store the derivable value and doing so has a danger of ruining data integrity.

This post has been edited by baavgai: 19 August 2018 - 04:13 AM
Reason for edit:: bloody tags

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1