How to get the number of records of last 24 hour?

C

Cao Yi

Hi,

Assume that there is a table 't_test' which contains two fields named
'column_user_id' and 'column_login_time', and the related Java code
use String and java.sql.Date to map the two fields.

I just want to get the number of someone's login times during last 24
hour, but I'm not sure the canonical SQL statement, just wrote a
pseudo-code followed, please modify it for a right one! Many thanks in
advance!

select count(column_login_time)
from t_test
where column_user_id = 'someone' AND column_login_time < current_time
- 24

Obviously, " column_login_time < current_time - 24" need to correct,
can you help me?
 
C

Cao Yi

Now, I use java.sql.Timestamp to map column_login_time field whose
type is also timestamp in Mysql.
 
G

Guest

Cao said:
Assume that there is a table 't_test' which contains two fields named
'column_user_id' and 'column_login_time', and the related Java code
use String and java.sql.Date to map the two fields.

I just want to get the number of someone's login times during last 24
hour, but I'm not sure the canonical SQL statement, just wrote a
pseudo-code followed, please modify it for a right one! Many thanks in
advance!

select count(column_login_time)
from t_test
where column_user_id = 'someone' AND column_login_time < current_time
- 24

Obviously, " column_login_time < current_time - 24" need to correct,
can you help me?

The following code:

PreparedStatement sel = con.prepareStatement("SELECT * FROM
dtest WHERE d > ?");
Timestamp cut = new
Timestamp(Calendar.getInstance().getTimeInMillis() - 5000);
sel.setTimestamp(1, cut);
ResultSet rs = sel.executeQuery();
while(rs.next()) {

gets all record from table dtest where the datetime field d is within
the last 5 seconds.

You can use the same technique for your problem.

Arne
 
C

Cao Yi

Hi, Arne, I've solved this problem yet, and the method is similar as
yours! What's

I set the column type in database as TIMESTAMP, and use
java.sql.Timestamp in my Java source code.
Here appends my codes(fragment):

String sql = "select count(*) from resume_download where user_id = ?
AND time > ?";
PreparedStatement pstmt = dbc.getPstmt(); //this depends on my other
code.
pstmt.setString(1, userID);
pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis() -
1000*60*60*24));
ResultSet rs = pstmt.executeQuery();
rs.first();
rs.getInt(1); //This value is what I want to get.

Thanks all the same, Arne!

:p
 
C

Cao Yi

Hi, Arne, I've solved this problem yet, and the method is similar as
yours! What's

I set the column type in database as TIMESTAMP, and use
java.sql.Timestamp in my Java source code.
Here appends my codes(fragment):

String sql = "select count(*) from test_table where user_id = ?
AND time > ?";
PreparedStatement pstmt = dbc.getPstmt(); //this depends on my other
code.
pstmt.setString(1, userID);
pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis() -
1000*60*60*24));
ResultSet rs = pstmt.executeQuery();
rs.first();
rs.getInt(1); //This value is what I want to get.

Thanks all the same, Arne!

:p
 
L

Lew

Cao said:
Now, I use java.sql.Timestamp to map column_login_time field whose
type is also timestamp in Mysql. ....
I set the column type in database as TIMESTAMP, and use
java.sql.Timestamp in my Java source code.
Here appends my codes(fragment):

Be aware:
MySQL has a type called TIMESTAMP, but it is quite different from the standard TIMESTAMP: It's a 'magic' data type with side effects in that it's automatically updated to the current date and time if some criteria are fulfilled.

MySQL has a type called DATETIME. Like MySQL's TIMESTAMP type, it stores a combination of date and time without fractional seconds. There are no side effects associated with the DATETIME type—which makes it the closest match to the SQL standard's TIMESTAMP type.

from <http://troels.arvin.dk/db/rdbms/#data_types-date_and_time-timestamp>
 
R

Roedy Green

pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis() -
1000*60*60*24));

That gets the last 24 hours which will include today and part of
yesterday. If you just wanted today's stuff, the starting point
depends on the client's timezone. This gets untenable if somebody is
viewing records of a person in a different timezone. In that case you
might want to use pure dates as an additional field. See
http://mindprod.com/jgloss/bigdate.html
 
C

Cao Yi

That gets the last 24 hours which will include today and part of
yesterday. If you just wanted today's stuff, the starting point
depends on the client's timezone. This gets untenable if somebody is
viewing records of a person in a different timezone. In that case you
might want to use pure dates as an additional field. Seehttp://mindprod.com/jgloss/bigdate.html

Thank you, Roedy! I cared that! Because this program will execute on a
single Server side, I don't think the the different timezone will
impact its correction.
 
C

Cao Yi


Thanks, Lew! But according to my practise, it was not the same as what
you told!
I insert the timestamp value via JDBC, and the timestamp instance is
generated in Java before inserting!
Although MySQL's TIMESTAMP is a 'magic' data type, but it doesn't make
sense.

btw, new Timestamp(System.currentTimeMillis() - 1000*60*60*24)) will
always generate an instance base on current date and time.

Thanks again!
 
L

Lew

Cao said:
Thanks, Lew! But according to my practise, it was not the same as what
you told!

It's not what I told, it's what's so. I merely conveyed the quoted information.
I insert the timestamp value via JDBC, and the timestamp instance is
generated in Java before inserting!

Then that is not the MySQL TIMESTAMP but the Java class Timestamp (not
"timestamp").
Although MySQL's TIMESTAMP is a 'magic' data type, but it doesn't make
sense.

I do not know what you mean by this sentence.
btw, new Timestamp(System.currentTimeMillis() - 1000*60*60*24)) will
always generate an instance base on current date and time.

The Java type is conformant to the SQL standard. The warning is that the
MySQL TIMESTAMP type is not.
 
C

Cao Yi

It's not what I told, it's what's so. I merely conveyed the quoted information.


Then that is not the MySQL TIMESTAMP but the Java class Timestamp (not
"timestamp").


I do not know what you mean by this sentence.


The Java type is conformant to the SQL standard. The warning is that the
MySQL TIMESTAMP type is not.

Hi, Lew, I got it! You're right! Thanks for your telling me the
details of TIMESTAMP.
I noticed that "I merely conveyed the quoted information." below. I
made a mistake before, sorry.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,902
Latest member
Elena68X5

Latest Threads

Top