timestamp to date in java and get the count value

Question

hi i have to convert timestamp to date after check the query and return the count value. my database have date(1344399208,1344399269),status(Q,Q). This is my code:

public class GetCurrentDateTime {
 public int data(){
int count=0;
java.sql.Timestamp timeStamp =new Timestamp(System.currentTimeMillis());

          java.sql.Date      date      = new java.sql.Date(timeStamp.getTime()); 
           System.out.println(date);
//count++;


try{
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xcart-432pro","root","");

    PreparedStatement statement =  con.prepareStatement("select * from xcart_orders where status='Q' AND date=CURDATE()");
     ResultSet result = statement.executeQuery();
     while(result.next()) {
            // Do something with the row returned.
            count++; //if the first col is a count.
        }



}

      catch(Exception exc){
      System.out.println(exc.getMessage());
      }


    return count;
       }

        }

Here the date is saved in timestamp format.but i like to convert date(yyyy-mm-dd) format.its done successfully.ya i got the output is 2012-08-08.but i have to check the query today date+status=Q .so how is that date is save in variable and call that variable in query.so how is wrote query for above condition.after check the condition and display the returns count value on my tomcat console.How is to do.please help me

Varun Achar · Answer 1 · 2012-08-08T05:07:45.787

0

To convert date to the date format specified:

int timestamp = 1231342342342; // replace with timestamp fetched from DB
Date date = new Date(timestamp);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
String dateString = sdf.format(date); //convert to yyyy-mm-dd format

From what I understand from the edit, you want the query to be something like this:

PreparedStatement statement =  con.prepareStatement("select * from xcart_orders where status='Q' AND date='"+dateString+"'");

I'm assuming that the date is stored in string format in the DB since you asked it to be converted into a particular format.

From comments:

To get midnight date:

Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(timestamp);
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);

To get all entries within a 24 period:

"select * from xcart_orders where status='Q' AND date between " + cal.getTimeInMillis() + " and " + (cal.getTimeInMillis() + 86400000l);

edited Aug 08 '12 at 05:07

answered Aug 08 '12 at 04:35

Varun Achar

14,781
7
57
74

in above code i got the output is 08-08-2012 successfully..but i wish to need the query for today date+status=Q check the both condition means it is displayed count value on my console.so here the date is saved in variable and call that query. – user1575906 Aug 08 '12 at 04:41
You want all entries between 08-08-2012 and 09-08-2012. The full 24 hours right? – Varun Achar Aug 08 '12 at 04:57
what are you getting in the output? – Varun Achar Aug 08 '12 at 05:16
displayed 0 only.but matched query is 2 – user1575906 Aug 08 '12 at 05:27

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

Partial Answer to your Question

Date Examples

Examples borrowed from Code Ranch and SO posts

// Get system time

Timestamp SysTime = new Timestamp(System.currentTimeMillis());

java.util.Date UtilDate = new java.util.Date(Systime.getTime());
java.sql.Date SQLDate = new java.sql.Date(Systime.getTime());

// Date + Time + Nano Sec
System.out.println(SysTime); 

// Date + Time
System.out.println(UtilDate); 

// Date
System.out.println(SQLDate);

Formatting Dates

// Apply Format
Date InDate = SQLDate; // or UtilDate
DateFormat DateFormat = new SimpleDateFormat("yyyy MM dd");
String DisplayDate = DateFormat.format(InDate);
System.out.println(DisplayDate);

Please note that I am new to java, hence verify if it works.

Comparing dates

See this SO post:

How to compare dates using Java

edited May 23 '17 at 11:48

Community

1
1

answered Aug 08 '12 at 04:47

Robs Ned

21
1

in my code i got the output 2012-08-08 successfully.but i wish to need that date is save in variable.call that variable in query.so how is wrote query for today date+status=Q condition.after check the condition and display the returns count value on my tomcat console.How is to do.please help me – user1575906 Aug 08 '12 at 04:51
Please check the link to SO post – Robs Ned Aug 08 '12 at 04:58

timestamp to date in java and get the count value

2 Answers2