Compare time difference action

GrantMcCormack · #1 (**permalink**) 01-13-12, 07:47

Hi

Can anybody explain if its possible if its possible to send a sql statement to update column 1 where the current time is 10 mins older than the time in col 2.

Column 1 is holding a true statement that we need to update to false if over 10 mins old

any help would be appreciated

many thanks

Grant

GWilliy · #2 (**permalink**) 01-13-12, 08:17

I would have thought something like this

DECLARE @MyDate DATETIME=GETDATE();
UPDATE MyTable
SET Column1 = 1
WHEREDATEDIFF(minute,Column2,@MyDate)< 10;

GrantMcCormack · #3 (**permalink**) 01-13-12, 09:27

Hi there

Many thanks for the response.

Is it possible to expand a little more on the sql statement you have provided.

We have two columns 1 will contain a true or false statement and the second a time stamp.

We would need the statement to run only on rows where column 1 is true
so if col 1 is true then compare time if over 10 mins then update col2 to false

GrantMcCormack · #4 (**permalink**) 01-13-12, 10:17

I have resolved

DECLARE @MyDate DATETIME=GETDATE()
UPDATE InuseTable
SET Inuse = 'True'
WHERE DATEDIFF(minute,TimeStamp,@MyDate)> 10 and InUse = 'True'

Thanks again for your help

Grant

Wim · #5 (**permalink**) 01-13-12, 11:07

I came up with.

Code:

UPDATE #DaTable
SET Column1 = 'False'
WHERE Column1 = 'True' AND
	DATEDIFF(second, Column2, GETDATE()) >= 6000

Even when the current time = Column2, a split nanosecond later the record should be updated. So I would not use ">" , but ">=". (Isn't the " < 10" in the code a bug?)

By using minute in the datediff function, you will only update after 11 minutes. I don't know what the granularity is: set to False 1 minute or 1 second or 1 microsecond or ... after the 10 minutes have passed.

GrantMcCormack · #6 (**permalink**) 01-13-12, 11:23

Many thanks for this Wim

I see what you are saying and this does seem the more sensible choice to follow.

I am quite new to sql and forums like are a great help in learning and finding out what is possible

Many thanks again

Grant

Wim · #7 (**permalink**) 01-13-12, 11:34

Update:

Code:

UPDATE #DaTable
SET Column1 = 'False'
WHERE Column1 = 'True' AND
	DATEDIFF(minute, Column2, GETDATE()) >= 10

The ">=" makes this thing work, without resorting to seconds or so.
Only when using ">", would it become necessary to resort to seconds or milliseconds, ...

">=" FTW

GWilliy · #8 (**permalink**) 01-14-12, 03:41

Glad it's sorted, just a couple of points

Is it more performant to assign the GETDATE() to a @variable as in my example and use this in the filter.

Have you considered using a calculated field for Column 1, negating the need to do any updates at all, not sure about any performance impact

You could do some local performance comparisons if you have time

GW

Wim · #9 (**permalink**) 01-14-12, 05:19

Quote:

Originally Posted by GWilliy

Is it more performant to assign the GETDATE() to a @variable as in my example and use this in the filter.

Creating a @variable and assigning GetDate() to it also takes time. So if @variable would only be used once, I'd say using GetDate() will be faster.

Only if the current time is needed many times, you might notice a difference.

But then something else kicks in that is a lot more important than a few microseconds: If the code contains a number of scripts that use the current time, the value GetDate() will return between the first call and the last call will be different. Most likely that is undesired, unless you want to measure execution times.
Use a @variable when it is important that the GetDate() value is the same between multiple queries.

Quote:

Originally Posted by GWilliy

Have you considered using a calculated field for Column 1, negating the need to do any updates at all, not sure about any performance impact

How would that work? What would be the gain?