CODEWITHSUNDEEP
sqlsql-server
agarwal_achhnera
agarwal_achhnera

Reputation: 2456

How to update a table on the basis of difference of current time and column time

I have a table called node_status which has three columns:

  1. id (int)
  2. status (bit)
  3. modifiedtime (smalldatetime)

I want to run a query on it such that if any row in the table contains a modifiedtime value which is more than 5 minutes before the current time then the status column set to 0 (false).

Can any body tell me how to do this? (using SQL Server)

Thanks

Upvotes: 0

Views: 100

Answers (3)

StuartLC
StuartLC

Reputation: 107237

The query 

UPDATE node_status
SET [Status] = 0
WHERE DATEDIFF(n, ModifiedTime, CURRENT_TIMETAMP) >= 5

will do the job, however, if your node_status table is large, then the performance will be lousy due to table / index scans. If you have an index on ModifiedTime:

DECLARE @ThresholdTime DATETIME
SET @ThresholdTime = DATEADD(n, -5, CURRENT_TIMESTAMP)

UPDATE node_status
SET [Status] = 0
WHERE ModifiedTime <= @ThresholdTime

or even better, if the majority of records have node_status = 0 (i.e. selectivity)

UPDATE node_status
SET [Status] = 0
WHERE ModifiedTime <= @ThresholdTime
    AND [Status] = 1 -- You've stated that status is a bit - I've assumed not nullable.

Upvotes: 3

Matej
Matej

Reputation: 6199

You can use DATEADD function to compute the date/time before five minutes and then compare the modifiedtime column to this value:

UPDATE node_status SET status = 0 WHERE modifiedtime <= dateadd(minute, -5, CURRENT_TIMESTAMP)

Upvotes: 1

Timbo
Timbo

Reputation: 4533

When you want to do a query based on the current time you can use getdate(), and use the dateadd function to add or remove amounts of time from a date. As such:

update 
     node_status 
set 
     status = 0 
where 
     modifiedtime < dateadd(minute, -5, getdate())

Upvotes: 2

Related Questions