CODEWITHSUNDEEP
sql-serverdatetimesql-server-2005jobs
user1453999
user1453999

Reputation: 120

SQL Job for updating datetime in a table

I have a table with demo data that I want to maintain. The data in the table contains datetime and I would like to create a job to run nightly to update the datetime so that the datetimes are never older than 1 week old and never a future datetime then the current datetime. I would like to maintain the order of the messages when ordering by datetime so that when I update that column one row never leap frogs over the other. I can't come up with a formula to do this without some rows eventually leapfrogging when ordering by datetime. Any suggestions?

My issue is with the formula. Adding one day to all will still make it a future date for the last message if dated Tuesday at 5pm and I add 1 day it will be then wednesday at 5 pm when demoing on Wednesday morning. Adding one day with exception of the most recent may leapfrog the next closest day causing the most recent message to fall back in the order.

Upvotes: 0

Views: 854

Answers (2)

Lee
Lee

Reputation: 13542

Assuming you're using MySQL, since you didn't specify (though the same techniques should be possible pretty much anywhere, the exact syntax and functions used will vary)

Given a table structure like this 

CREATE TABLE T (
  id int auto_increment primary key, 
  detail varchar(20), 
  date datetime
);

INSERT INTO T
  (detail, date)
VALUES
  ('one', NOW()),
  ('two', DATE_SUB(NOW(), INTERVAL 1 day)),
  ('three', DATE_SUB(NOW(), INTERVAL 2 day)),
  ('four', DATE_SUB(NOW(), INTERVAL 3 day)),
  ('five', DATE_SUB(NOW(), INTERVAL 4 day));

You can select all the rows in the proper order, and re-distribute the dates evenly over the preceding week in the process. Like this: 

SELECT 
  T.*, 
  DATE_SUB(NOW(), INTERVAL 6.048e+11*(1-(@row := @row + 1)/@tot) MICROSECOND) as newDate 
FROM 
  T, 
  (SELECT @row := 0, @tot := (select count(*) from T)) r 
ORDER BY 
  date;

Note: 6.048e+11 is the number of microseconds in a week.

Here's an example: http://sqlfiddle.com/#!2/939bc/5

So you could use that projection to create a temporary table, then drop the original table, and re-create it from the values in the temporary table. Something like this:

create temporary table T2 as (
  select 
    T.*, 
    DATE_SUB(NOW(), INTERVAL 6.048e+11*(1-(@row := @row + 1)/@tot) MICROSECOND) as newDate
  from T, (SELECT @row := 0, @tot := (select count(*) from T)) r 
  order by date
);
drop table T;
create table T as (select id, detail, newDate as date from T2);

Update: Changed the above examples to use MICROSECOND granularity, as the previous examples used fractional DAY intervals, which mysql doesn't seem to support. I also updated the sqlfiddle link accordingly.

Also, corrected the formula to sequence dates in the correct order (the previous example assigned dates in the opposite order required).

Upvotes: 0

Ron Smith
Ron Smith

Reputation: 3266

If you currently have a number of dates spanning more than one week, this will not be possible by simply adding time to each date. If the time frame between the records doesn't matter, only the order, min, and max, I would sort the dates with a row_number function and add that sort number in seconds to 1 week ago:

update t
    set t.YourDateColumn = u.NewDate
    from YourTable t
        inner join  (select ID,
                        dateadd(second,
                            row_number() over(order by YourDateColumn),
                            dateadd(week,-1,getdate())) NewDate
                     from YourTable
                     ) u
            on u.ID = t.ID

This will work if you have up to 604.8K records. If you have more than that, you'll have to add milliseconds. That will work for up to 604.8M records.

Upvotes: 0

Related Questions