Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Unanswered: Compare time difference action

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I would have thought something like this

    DECLARE @MyDate DATETIME=GETDATE();
    UPDATE MyTable
    SET Column1 = 1
    WHEREDATEDIFF(minute,Column2,@MyDate)< 10;
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Jan 2012
    Posts
    4
    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

  4. #4
    Join Date
    Jan 2012
    Posts
    4
    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

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jan 2012
    Posts
    4
    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

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    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
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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?
    Last edited by Wim; 01-15-12 at 09:10.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •