How to convert TimeStamp to Date in Java?

Question

How do I convert 'timeStamp' to date after I get the count in Java?

My current code is as follows:

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/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;
    }
}

This is my database:

Here the output I got was 2012-08-07 0, but the equivalent query returns 3. Why do I get 0?

Celibacy

Duplicate of #2319219 – Kessel

Can you show us examples of what the table data looks like? – Chicago

date(1344255451,1344255537,1344312502) and status have(Q,Q,Q) – Celibacy

Duplicate of this Question, How to convert from java.sql.Timestamp to java.util.Date?. – Mingrelian

The title of this Question is a red herring. The real problem is the improper use of a PreparedStatement with incorrect syntax. You cannot embed Java variables in the text of your SQL string. Instead, embed ? in the SQL string, then call the set methods to pass values to the PreparedStatement. See the correct Answer by Sujay and Answer by tenorsax. – Mingrelian

Tip: Never name a column in database with a keyword/reserved word like date. Append a trailing underscore to avoid such collisions. The SQL spec explicitly promises to never use a trailing underscore on any keyword or reserved word. – Mingrelian

Do not re-post your Question repeatedly! This is a repeat of 4 days earlier: https://mcmap.net/q/188760/-converting-timestamp-to-date-in-java-duplicate/642706 – Mingrelian

Do not store your date as a Unix timestamp. I can’t tell whether those timestamps fall on the date you mention, that is, verifying your code is more cumbersome than it had needed to be. Use the MySQL date datatype if you need the date only, a timestamp if you need the time. – Tamelatameless

Your question seems to assume that your unexpected result comes from incorrect conversion from an old-fashioned java.sql.Timestamp to an equally outdated java.sql.Date? This is not the case. Your new java.sql.Date(timeStamp.getTime()) is a correctly working conversion. – Tamelatameless

For new readers to this question I strongly recommend you don’t use java.sql.Timestamp and java.sql.Date. Those classes are poorly designed and long outdated. Instead use OffsetDateTime for a timestamp and LocalDate for a date; both from are java.time, the modern Java date and time API. – Tamelatameless

Answer 1

Just make a new Date object with the stamp's getTime() value as a parameter.

Here's an example (I use an example timestamp of the current time):

Timestamp stamp = new Timestamp(System.currentTimeMillis());
Date date = new Date(stamp.getTime());
System.out.println(date);

Inaccurate

@vs06 What makes you think it is deprecated? The latest java 7 and 8 docs both say otherwise (docs.oracle.com/javase/7/docs/api/java/util/Date.html#getTime() and docs.oracle.com/javase/8/docs/api/java/util/Date.html#getTime--) – Inaccurate

This will produce an incorrect result if the Timestamp time zone and the JVM timezone are different. – Stevana

How to get this formatted to 14.03.2014 for example? – Koodoo

I hate when ppl use libraries in their answers and expect everyone to know how to import them :/ – Leakage

Is the Timestamp class required? You can just do Date date = new Date(System.currentTimeMillis()); – Caesarean

Answer 2

// timestamp to Date
long timestamp = 5607059900000; //Example -> in ms
Date d = new Date(timestamp );

// Date to timestamp
long timestamp = d.getTime();

//If you want the current timestamp :
Calendar c = Calendar.getInstance();
long timestamp = c.getTimeInMillis();

Huygens

Why did you multiply it by 1000? – Daggett

Beacause the initialization of the date is in milliseconds and the timestamp is in seconds ! Same think for all other convertion ! It's good ? – Huygens

Answer 3

Just:

Timestamp timestamp = new Timestamp(long);
Date date = new Date(timestamp.getTime());

Favin

Answer 4

tl;dr

LocalDate.now( ZoneId.of( "Africa/Tunis" ) )
    .atStartOfDay( ZoneId.of( "Africa/Tunis" ) )
    .toEpochSecond()

…

LocalDate.now( ZoneId.of( "Africa/Tunis" ) )
    .plusDays( 1 )
    .atStartOfDay( ZoneId.of( "Africa/Tunis" ) )
    .toEpochSecond()

…

"SELECT * FROM orders WHERE placed >= ? AND placed < ? ; "

…

myPreparedStatement.setObject( 1 , start )
myPreparedStatement.setObject( 2 , stop )

java.time

You are using troublesome old date-time classes that are now legacy, supplanted by the modern java.time classes.

Apparently you are storing a moment in your database in a column of some integer type. That is unfortunate. You should instead be using a column of a type such as the SQL-standard TIMESTAMP WITH TIME ZONE. But, for this Answer, we will work with what we have.

If your records represent moments with a resolution of milliseconds, and you want all the records for an entire day, then we need a time range. We must have a start moment and a stop moment. Your query has only a single date-time criterion where it should have had a pair.

The LocalDate class represents a date-only value without time-of-day and without time zone.

A time zone is crucial in determining a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.

If no time zone is specified, the JVM implicitly applies its current default time zone. That default may change at any moment, so your results may vary. Better to specify your desired/expected time zone explicitly as an argument.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId z = ZoneId.of( "America/Montreal" ) ;  
LocalDate today = LocalDate.now( z ) ;

If you want to use the JVM’s current default time zone, ask for it and pass as an argument. If omitted, the JVM’s current default is applied implicitly. Better to be explicit, as the default may be changed at any moment during runtime by any code in any thread of any app within the JVM.

ZoneId z = ZoneId.systemDefault() ;  // Get JVM’s current default time zone.

Or specify a date. You may set the month by a number, with sane numbering 1-12 for January-December.

LocalDate ld = LocalDate.of( 1986 , 2 , 23 ) ;  // Years use sane direct numbering (1986 means year 1986). Months use sane numbering, 1-12 for January-December.

Or, better, use the Month enum objects pre-defined, one for each month of the year. Tip: Use these Month objects throughout your codebase rather than a mere integer number to make your code more self-documenting, ensure valid values, and provide type-safety.

LocalDate ld = LocalDate.of( 1986 , Month.FEBRUARY , 23 ) ;

With a LocalDate in hand, we next need to transform that into a pair of moment, the start and stop of the day. Do not assume the day starts at 00:00:00 time-of-day. Because of anomalies such as Daylight Saving Time (DST), the day may start at another time such as 01:00:00. So let java.time determine the first moment of the day. We pass a ZoneId argument to LocalDate::atStartOfDay to look up any such anomalies. The result is a ZonedDateTime.

ZonedDateTime zdtStart = ld.atStartOfDay( z ) ;

Generally the best approach to defining a span of time is the Half-Open approach where the beginning is inclusive while the ending is exclusive. So a day starts with its first moment and runs up to, but not including, the first moment of the next day.

ZonedDateTime zdtStop = ld.plusDays( 1 ).atStartOfDay( z ) ;  // Determine the following date, and ask for the first moment of that day.

Our query for an entire day cannot make use of SQL command BETWEEN. That command is Fully-Closed ([]) (both beginning and ending are inclusive) where as we want Half-Open ([)). We use a pair of criteria >= and <.

Your column is poorly named. Avoid any of the thousand words reserved by various databases. Let’s use placed in this example.

Your code should have used ? placeholders in which to specify our moments.

String sql = "SELECT * FROM orders WHERE placed >= ? AND placed < ? ; " ;

But we have ZonedDateTime objects in hand, while your database apparently is storing integers as discussed above. If you had defined your column properly we could simply pass the ZonedDateTime objects with any JDBC driver supporting JDBC 4.2 or later.

But instead we need to get a count-from-epoch in whole seconds. I will assume your epoch reference date is the first moment of 1970 in UTC. Beware of possible data loss, as the ZonedDateTime class is capable of nanosecond resolution. Any fractional second will be truncated in the following lines.

long start = zdtStart().toEpochSecond() ;  // Transform to a count of whole seconds since 1970-01-01T00:00:00Z.
long stop = zdtStop().toEpochSecond() ; 

Now we are ready to pass those integers to our SQL code defined above.

PreparedStatement ps = con.prepareStatement( sql );
ps.setObject( 1 , start ) ;
ps.setObject( 2 , stop ) ;
ResultSet rs = ps.executeQuery();

When you retrieve your integer values from the ResultSet, you can transform into Instant objects (always in UTC), or into ZonedDateTime objects (with an assigned time zone).

Instant instant = rs.getObject( … , Instant.class ) ;
ZonedDateTime zdt = instant.atZone( z ) ;

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Mingrelian

Answer 5

I have been looking for this since a long time, turns out that Eposh converter does it easily:

long epoch = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse("01/01/1970 01:00:00").getTime() / 1000;

Or the opposite:

String date = new java.text.SimpleDateFormat("MM/dd/yyyy HH:mm:ss").format(new java.util.Date (epoch*1000));

Junie

Answer 6

try to use this java code :

Timestamp stamp = new Timestamp(System.currentTimeMillis());
Date date = new Date(stamp.getTime());
DateFormat f = new SimpleDateFormat("yyyy-MM-dd");
DateFormat f1 = new SimpleDateFormat("yyyy/MM/dd");
String d = f.format(date);
String d1 = f1.format(date);
System.out.println(d);
System.out.println(d1);

Appurtenance

Answer 7

First of all, you're not leveraging the advantage of using a PreparedStatement. I would first suggest that you modify your PreparedStatement as follows:

PreparedStatement statement = con.prepareStatement("select * from orders where status=? AND date=?")

You can then use statement.setXX(param_index, param_value) to set the respective values. For conversion to timestamp, have a look at the following javadocs:

PreparedStatement.setTimeStamp()
Timestamp

Hope this helps!

Fingerboard

Answer 8

In Android its very Simple .Just use the Calender class to get currentTimeMillis.

Timestamp stamp = new Timestamp(Calendar.getInstance().getTimeInMillis());
Date date = new Date(stamp.getTime());
Log.d("Current date Time is   "  +date.toString());

In Java just Use System.currentTimeMillis() to get current timestamp

Box

Answer 9

Not sure what you're trying to select in the query, but keep in mind that UNIX_TIMESTAMP() without arguments returns the time now. You should probably provide a valid time as argument, or change the condition.

EDIT:

Here is an example of a time bound query based on the question:

PreparedStatement statement = con
        .prepareStatement("select * from orders where status='Q' AND date > ?");
Date date = new SimpleDateFormat("dd/MM/yyyy").parse("01/01/2000");
statement.setDate(1, new java.sql.Date(date.getTime()));

EDIT: timestamp column

In case of timestamp use java.sql.Timestamp and PreparedStatement.setTimestamp(), ie:

PreparedStatement statement = con
        .prepareStatement("select * from orders where status='Q' AND date > ?");
Date date = new SimpleDateFormat("dd/MM/yyyy").parse("01/01/2000");
Timestamp timestamp = new Timestamp(date.getTime());
statement.setTimestamp(1, timestamp);

Sesquioxide

now my condition is select * from xcart_orders where status='Q' AND date=CURDATE().now also displayed zero only – Celibacy

@krishnaveni what records are you trying to select? – Sesquioxide

date(11111111,123456767,122333344) and status=(Q,Q,Q)..all are saved in my database.now i wish to need check the query and get the count value and display on my console(ie)how many datas are matched after return the count value – Celibacy

@krishnaveni If you want the resultset to be bound by a certain date then use that date in the query, otherwise remove the date condition. – Sesquioxide

in my code is works successfully when date is in yyyy-mm-dd format saved in my database . But now my date is saved in a timestamp in this 1343469690 format. How is get the count value?how is i wrote a code here – Celibacy

@krishnaveni use java.sql.Timestamp for timestamp column, see my last edit. – Sesquioxide

Answer 10

new Date(timestamp.getTime()) should work, but the new fancy Java 8 way (which may be more elegant and more type safe, as well as help lead you to use the new time classes) is to call Date.from(timestamp.toInstant()).

(Do not rely on the fact that Timestamp itself is an instance of Date; see explanation in the comments of another answer .)

Jelle

Answer 11

    Timestamp tsp = new Timestamp(System.currentTimeMillis());
   java.util.Date dateformat = new java.util.Date(tsp.getTime());

Aligarh

Answer 12

I feel obliged to respond since other answers seem to be time zone agnostic which a real world application cannot afford to be. To make timestamp-to-date conversion correct when the timestamp and the JVM are using different time zones you can use Joda Time's LocalDateTime (or LocalDateTime in Java8) like this:

Timestamp timestamp = resultSet.getTimestamp("time_column");
LocalDateTime localDateTime = new LocalDateTime(timestamp);
Date trueDate = localDateTime.toDate(DateTimeZone.UTC.toTimeZone());

The example below assumes that the timestamp is UTC (as is usually the case with databases). In case your timestamps are in different timezone, change the timezone parameter of the toDatestatement.

Stevana

Answer 13

import java.sql.Timestamp

new Timestamp(1234567890123L)
// java.sql.Timestamp = 2009-02-14 10:31:30.123

(new Timestamp(1234567890123L)).toLocalDateTime().toLocalDate().toString()
// 2009-02-14

What is a different result from one of the previous most voted answers:

import java.time.format.DateTimeFormatter
import java.time.ZoneId
import java.time.ZoneOffset

(DateTimeFormatter.ISO_LOCAL_DATE).withZone(ZoneId.from(ZoneOffset.UTC)).format(new Timestamp(1234567890123L).toInstant())
// String = 2009-02-13

In my case, I was expecting the "2009-02-14" instead of the "2009-02-13". So the top result worked better for me.

Schwa

First I recommend you don’t use Timestamp. Next, the different results come from different default time zones. Rather than writing code that happens to work with your default time zone I recommend specifying time zone explicitly. – Tamelatameless

That is the point: The solution using DateTimeFormatter is considering my Timezone and fixing something that was not broken. About using or not Timestamp, that s not an option for me. Also, that is exactly the question title, right? – Schwa

Sorry, You are correct. The preview from the Timestamp shows the timestamp to DateTime, considering my current time zone. That is why it does not match the UTC version. That particular timestamp 1234567890123L in UTC should be Friday, 13 February 2009 23:31:30, as we can see running new Timestamp(1234567890123L).toGMTString() . Therefore the correct answer should be "2009-02-13" – Schwa

Answer 14

String timestamp="";
Date temp=null;
try {
    temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(getDateCurrentTimeZone(Long.parseLong(timestamp)));
} catch (ParseException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}
int dayMonth=temp.getDate();
int dayWeek=temp.getDay();
int hour=temp.getHours();
int minute=temp.getMinutes();
int month=temp.getMonth()+1;
int year=temp.getYear()+1900;

Colossal

Answer 15

Assuming you have a pre-existing java.util.Date date:

Timestamp timestamp = new Timestamp(long);
date.setTime( l_timestamp.getTime() );

Fragonard

Answer 16

You can use this method to get Date from Timestamp and Time-zone of particular area.

public String getDayOfTimestamp(long yourLinuxTimestamp, String timeZone) {
    Calendar cal = Calendar.getInstance();
    cal.setTimeInMillis(yourLinuxTimestamp * 1000);
    cal.setTimeZone(TimeZone.getTimeZone(timeZone));
    Date date = cal.getTime();
}

Micrometer

Answer 17

Date updated = timestamp.toDate();

Telescopy

No. The outdated java.sql.Timestamp did not have a toDate method. And the question already shows a working conversion. – Tamelatameless

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Meaning

Answer 18

DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Date fecha = getDateInTimestamp(); 

Chophouse