timestamp sql query

Question

im try to execute sql query that will check if some time (like 10 sec) past from last timestamp update in my table and chenge other table if yes.

My question is if ther is any time-stamp conditional operator that can check this? For example < , > ,=?

(I know that I can do it in to different query, but I'm try do to it in 1 query)..

Something like this

             UPDATE Person SET isconnected=false 
            where person.email=(select from imalive where timestamp<10).

person:

email: [email protected]

name: dan

age: 20

isAlive:

email: [email protected]

lastseen: 2011-09-04 21:27:00

So at last if the person is last seen will be more then 10 sec he will go to isconnected =false.

Andomar · Accepted Answer · 2011-09-04 19:18:32Z

2

Syntax would change slightly per database. Here's an example for SQL Server using not exists:

update  Person
set     IsConnected = 0
where   IsConnected <> 0
        and not exists
        (
        select  *
        from    IsAlive
        where   Person.email = IsAlive.email
                and IsAlive.timestamp > dateadd(s,-10,getdate())
        )

edited Sep 4, 2011 at 19:18

answered Sep 4, 2011 at 19:13

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dan Over a year ago

first of all thanks. but im using hsql and what i check it's not support dateadd :-(.

Andomar Over a year ago

@dan: Try TIMESTAMPADD(SQL_TSI_SECOND, -10, CURRENT_TIMESTAMP) ?

Collectives™ on Stack Overflow

timestamp sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related