SQL timestamp on Update read in Java

Question

I am writing a Client Server Application now when the Client sends a command to the Server the Record in the SQL Database is updated and On Update a Timestamp is set on a field. But now I want to read all the Users in the SQL Database that where online in the last hour with the Timestamp.

So this is how far I am:

SELECT username FROM users WHERE lastonline...

lastonline is the field with the timestamp of the users last update. I now have no Idea how to check if he was online in the last hour.

I thank you for you help.

G1yyK · Accepted Answer · 2012-12-12 08:21:25Z

1

Select username FROM user WHERE lastoinline>"date now - 1 hour" AND lastoinline<="date now"

Date dеNow = new Date();
Date веBefore = tdNow; tdBefore.setHours(tdBefore.getHours()-1);
String query = "SELECT username FROM user WHERE lastonline>='"+(dtBefore.getTime/1000)+"' AND lastonline<='"+(dtNow.getTime/1000)+"'

"

edited Dec 12, 2012 at 8:21

answered Dec 12, 2012 at 8:14

G1yyK

2162 silver badges7 bronze badges

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

Comments

JonH · Accepted Answer · 2012-12-12 08:08:53Z

0

Compare the current hour with the hour on the timestamp's record.

If you are using sql server:

SELECT DATEPART(hh, GETDATE()) --current hour

SELECT DATEOART(hh, ColumnStoredInDB) --stored hour

Spoiler: Simply take the ColumnStoredInDB from the current hour and if the difference is < 1 that user has been in the system for the last hour. If the difference >=1 you can skip that record

answered Dec 12, 2012 at 8:08

JonH

33.2k13 gold badges94 silver badges149 bronze badges

5 Comments

Buhake Sindi Over a year ago

Is DATEPART function generic SQL function or is it RDBMS specific?

JonH Over a year ago

You didnt tag your question with the RDBMS, I was assuming SQL Server, if not Oracle and other vendors have something similiar. So please consult the documentation based on what you are using...

alexj Over a year ago

Sorry for net mentioning I am using MySQL

JonH Over a year ago

mysql has an hour function dev.mysql.com/doc/refman/5.0/en/…

Buhake Sindi Over a year ago

I didn't create this question. I was just asking because there is no tag of the specific RDBMS that the OP used.

Collectives™ on Stack Overflow

SQL timestamp on Update read in Java

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related