0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
alexj
  • 139
  • 1
  • 4
  • 15

2 Answers2

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)+"'

"

G1yyK
  • 216
  • 2
  • 7
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

JonH
  • 32,732
  • 12
  • 87
  • 145
  • Is `DATEPART` function generic SQL function or is it RDBMS specific? – Buhake Sindi Dec 12 '12 at 08:12
  • 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... – JonH Dec 12 '12 at 08:13
  • Sorry for net mentioning I am using MySQL – alexj Dec 12 '12 at 08:15
  • mysql has an hour function http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_hour – JonH Dec 12 '12 at 08:18
  • I didn't create this question. I was just asking because there is no tag of the specific RDBMS that the OP used. – Buhake Sindi Dec 12 '12 at 08:18