CODEWITHSUNDEEP
javasql
MSingh
MSingh

Reputation: 627

Java Timestamp conversion error

Here is my problem with time stamp:

in MySql data base there is a column name creation_date with data type timestamp

the value in the column is 2014-07-04 17:35:07.0 when I am trying to convert it to millisecond using java code, it is showing different behavior

For example

if I fetch it using hibernate and print timestamp.getTime() it is showing 1404484507000

but while doing 

Timestamp t=new Timestamp(2014, 7, 4, 17, 35, 7, 0);
System.out.println("t.getTime() - "+t.getTime());

it is showing 61365297907000

What's going wrong here.

Upvotes: 1

Views: 3159

Answers (3)

Basil Bourque
Basil Bourque

Reputation: 338211

tl;dr

From database.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;  // Most databases including MySQL store a moment in a column of a data type akin to the SQL-standard `TIMESTAMP WITH TIME ZONE` as UTC. So retrieve as a `OffsetDateTime` object, expecting its zone to be set to UTC. 
ZonedDateTime zdt = odt.atZoneSameInstant( ZoneId.of( "Pacific/Auckland" ) ) ;  // Adjust into any time zone you desire. Same moment, different wall-clock time.

To database.

myPreparedStatement.setObject(       // As of JDBC 4.2, exchange java.time objects with your database, not mere integers or strings.
    … , 
    ZonedDateTime.of(
        2014 , 7 , 4 ,
        17 , 35 , 7 , 0 ,
        ZoneId.of( "Africa/Tunis" )  // Specify the time zone giving context to that date and time – where on earth did you mean 5 PM?
    )
    .toOffsetDateTime()              // Adjust from that zone to UTC.
    .withOffsetSameInstant(
        ZoneOffset.UTC
    ) 
) ;

java.time

The modern solution uses java.time classes.

LocalDate ld = LocalDate.of( 2014 , Month.JULY , 4 ) ;  // Or use integer `7` for July, 1-12 for January-December.
LocalTime lt = LocalTime.of( 17 , 35 , 7 );

To determine a moment, you need more than a date and time-of-day. You need a time zone to provide context. Do you mean 5 PM in Japan, or 5 PM in France, or 5 PM in Québec? Those would be any of three different moments.

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 during runtime(!), so your results may vary. Better to specify your desired/expected time zone explicitly as an argument.

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.

Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 2-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" ) ;  
ZonedDateTime zdt = ZonedDateTime.of( ld , lt , z ) ;

Adjust from that zone to UTC. 

OffsetDateTime odt = zdt.toOffsetDateTime().withOffsetSameInstant( ZoneOffset.UTC ) ;

As of JDBC 4.2 we can directly exchange java.time objects with a database. 

myPreparedStatement.setObject( … , odt ) ;

And retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

Notice that at no point did we require the integer count from epoch reference. We used smart objects rather than dumb integers or strings.

As for Hibernate, I an not a user, but I do know it has been updated to work with the java.time classes.

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.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

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.

Upvotes: 1

Pablo Albaladejo
Pablo Albaladejo

Reputation: 3249

MySQL uses a different Date format than Java does.

You can conver it from the ResultSet, using the getTime and getDate functions:

String query = "SELECT date FROM bean";
[...]
bean.setDate(toJavaDate(resultSet.getDate(1), resultSet.getTime(1)));

Where the toJavaDate code is:

public java.util.Date toJavaDate(java.sql.Date sqlDate, java.sql.Time sqlTime){
    Calendar calendar = new GregorianCalendar();

    calendar.set(Calendar.YEAR, sqlDate.getYear() + 1900);
    calendar.set(Calendar.MONTH, sqlDate.getMonth());
    calendar.set(Calendar.DAY_OF_MONTH, sqlDate.getDate());
    calendar.set(Calendar.HOUR_OF_DAY, sqlTime.getHours());
    calendar.set(Calendar.MINUTE, sqlTime.getMinutes());
    calendar.set(Calendar.SECOND, sqlTime.getSeconds());

    return calendar.getTime();        
}

The reverse operation, in order to save a new Date at the MySQL table:

String query = "UPDATE bean SET date = '" + toSqlDate(bean.getDate());

Where toSqlDate is:

public String toSqlDate(java.util.Date javaDate){
        String sqlDate = (javaDate.getYear()+1900)+"-"+javaDate.getMonth()+"-"+javaDate.getDate()+" "
                     +javaDate.getHours()+":"+javaDate.getMinutes()+":"+javaDate.getSeconds();
    return sqlDate;        
}

Now you can recheck the milliseconds:

long milliseconds = date.getTime();

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1499860

Did you read the documentation for the (deprecated) constructor you're calling? In particular:

year - the year minus 1900
month - 0 to 11

I'd strongly advise you not to call that constructor to start with. If you're using Java 8, use java.time.Instant and then java.sql.Timestamp.fromInstant.

Otherwise, you could call the constructor taking a long (number of milliseconds) and then set the nanos part separately.

Note that a value of 1404484507000 represents 14:35:07 UTC, so presumably your database is performing a time zone conversion.

Upvotes: 1

Related Questions