 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / July 2007How to get the number of records of last 24 hour?
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? Now, I use java.sql.Timestamp to map column_login_time field whose type is also timestamp in Mysql. > Hi, > [quoted text clipped - 14 lines] > Obviously, " column_login_time < current_time - 24" need to correct, > can you help me? By the way, if you use Oracle, the type should be DATE. > 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 [quoted text clipped - 12 lines] > 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 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 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 > 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>  SignatureLew >> Now, I use java.sql.Timestamp to map column_login_time field whose >> type is also timestamp in Mysql. [quoted text clipped - 15 lines] > > from <http://troels.arvin.dk/db/rdbms/#data_types-date_and_time-timestamp> Good catch ! Arne > > Now, I use java.sql.Timestamp to map column_login_time field whose > > type is also timestamp in Mysql. [quoted text clipped - 13 lines] > -- > Lew 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! >>> 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): Lew wrote: >> 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> > 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. SignatureLew > >>> Now, I use java.sql.Timestamp to map column_login_time field whose > >>> type is also timestamp in Mysql. [quoted text clipped - 32 lines] > -- > Lew 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. >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 -- Roedy Green Canadian Mind Products The Java Glossary http://mindprod.com On Jul 7, 11:27 pm, Roedy Green <see_webs...@mindprod.com.invalid> wrote: > >pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis() - > >1000*60*60*24)); [quoted text clipped - 8 lines] > Roedy Green Canadian Mind Products > The Java Glossaryhttp://mindprod.com 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. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.