converting timestamp to date in java [duplicate]

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?

Daliladalis

there is TimeStamp class in Java may be sql.timestamp .. check it out this might help you. – Broadnax

Check this link for converting Timestamp to date. [TimeStamp to Date][1] [1]: #2319219 – Lorraine

What SQL type is the date column? – Hypocrite

That's not an answer. What SQL type is the date column? – Hypocrite

@krishnaveni what kind of example do you want? – Bamboozle

Do not re-post your Question repeatedly! This was re-posted days later. There I posted a lengthy Answer using modern java.time classes. – Barns

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;

Underlie

Hi how is get the count value for this month.i have to use below query.select * from orders where status='Q' AND DATE_FORMAT(FROM_UNIXTIME(date),'%Y-%m-%d')=MONTH(date) = MONTH(CURDATE()) AND YEAR(date) = YEAR(CURDATE()).but is not gave me the correct count value.displayed 0 only. – Daliladalis

this query works for you. select count(1) from orders where status='Q' AND DATE_FORMAT(FROM_UNIXTIME(ts),'%Y-%m')= DATE_FORMAT(now(), '%Y-%m') – Underlie

Answer 2

Date date = new Date(timestamp);

whereas timestamp is a long variable

Callie

i got dis exception error:Exception in thread "main" java.lang.IllegalArgumentException at java.util.Date.parse(Date.java:598) at java.util.Date.<init>(Date.java:255) at com.xcart.TodayQ.data(TodayQ.java:16) at com.xcart.Demo.main(Demo.java:5) – Daliladalis

are you sure you're variable is of type "long"? because your stackTrace says it's entering the parse method which can be an indication that you're passing a string into the constructor – Callie

now i got the output is displayed always 1970-01-01 0...i can't understand dis.check the query and give me the count only.how is to do – Daliladalis

Alexander Pogrebnyak's answer looks the most accurate to me. – Sessile

Answer 3

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();

Chane

Answer 4

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 );

Allottee

This is probably the most accurate answer to the question. – Sessile

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

Anneal

it is also getting following exception:Exception in thread "main" java.lang.IllegalArgumentException at java.util.Date.parse(Date.java:598) at java.util.Date.<init>(Date.java:255) at com.xcart.TodayQ.data(TodayQ.java:16) at com.xcart.Demo.main(Demo.java:5) – Daliladalis

are you sure the timestamps are valid? post the timestamp value thar generates the exception – Anneal

now i got the output is displayed always 1970-01-01 0...i can't understand dis.check the query and give me the count only.how is to do – Daliladalis

Alexander Pogrebnyak's answer looks the most accurate to me. – Sessile

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.

Hypocrite