converting timestamp to date in java

Question

This is my database:

Here I have to check the query current date+status=Q information and get the count value.

This is my code:

public class TodayQ {
    public int data() {
         int count=0;
        Date date = new Date(timestamp);
DateFormat dateFormat = new SimpleDateFormat ("yyyy-MM-dd");
System.out.println( dateFormat.format (date));

        // count++;

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

            PreparedStatement statement = con
                    .prepareStatement("select * from 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 I have to edit the date is (yyyy-mm-dd) format. Now I got the output. But I wish to use timestamp on my database. So how is converted timestamp to date in Java. How is use that code in my code?

Answer 1

You can achieve the same using mysql functions. Hope the following query gives you the desired output.

select * 
from orders
where status='Q' AND 
      date_format(from_unixtime(date),'%Y-%m-%d') = current_date;

Answer 2

Looks like the integer value you are seeing is the UNIX's number of seconds since the start of the Epoch ( which is 1970-01-01T00:00:00Z ) -> http://en.wikipedia.org/wiki/Unix_epoch

I don't know if the mysql JDBC driver will take care of converting this field for you. If it does, the preferred way of getting its value is:

java.sql.Timestamp ts = result.getTimestamp( columnNumber );

Timestamp extends java.util.Date, so you can use it where regular java.util.Date is expected.

If, for whatever reason this does not produce desired result, then get the column value as long and adjust the value to milliseconds. You are in luck here because Java's and UNIX's epochs start at the same time ( Java's is just more precise ).

long ts = result.getLong( columnNumber ) * 1000L;

java.util.Date date = new java.util.Date( ts );

Answer 3

Date date = new Date(timestamp);

whereas timestamp is a long variable

Answer 4

Assuming timestamp is time in millis, you can use java.util.Calendar to convert timestamp to date time as follows:

java.util.Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(timestamp);
java.util.Date date = cal.getTime();

Answer 5

to print the timestamp in yyyy-mm-dd:

Date date = new Date(timestamp);
DateFormat dateFormat = new SimpleDateFormat ("yyyy-MM-dd");
System.out.println( dateFormat.format (date));

UPDATE

here is a HINT of how you can proceed:

public static int data() {
    int count = 0;
    Date now = Calendar.getInstance().getTime();
    System.out.println("TODAY IS:"+now.getTime()); //TODAY IS:1344007864862

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

        PreparedStatement statement1 = (PreparedStatement) con.prepareStatement("SELECT * FROM ORDERS WHERE STATUS = 'Q' AND DT= "+now.getTime());
        ResultSet rs1 = statement1.executeQuery();
        while (rs1 .next()) {
            count++; //A HIT IS FOUND
        }
    } catch (Exception exc) {
        System.out.println(exc.getMessage());
    }
    return count;
}

Obviously the now.getTime() returns the actual millisecond the date was captured in the now variable . The mathematics is all up to your implementation from now on.

Remember that the way to get a Calendar object at midnight (10/05/2012 00:00:00) is here Java program to get the current date without timestamp

Answer 6

The date column in the database should be a TIMESTAMP or DATE or TIME.

These are retrieved as java.sql.Timestamp or java.sql.Date or java.sql.Time respectively.

All of these classes extend java.util.Date.

So the data should already be in the format you are asking for.

So there is nothing to do.