Comparing MySQL and Java Time

Question

I have a datetime field in MySQL which I access through calling result.getString('date'), now I would like to check weather the current date and time in Java has exceeded the MySQL time or is before the MySQL time to check weather a result is activated or not.

Datetime from MySQL has the form: 2011-12-30 17:10:00, how can I compare this string to the Java time?

You could use getTimestamp('date') and then use the getTime methods on the Date and Timestamp classes for comparison. If it's truly a string in the DB then I'd use a date formatter to turn into a Date and use getTime — karakuricoder, Jun 01 '11 at 15:09
@karakuricoder - this an answer (correct, IMO), why post it as a comment? — user85421, Jun 01 '11 at 15:48

score 4 · Answer 1 · edited May 23 '17 at 12:02

4

In Java, you can construct a Date from a string, using a SimpleDateFormat:

String text = "2011-12-30 17:10:00";
Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(text);
Date now = new Date();

if (date.after(now))
{
    // do stuff
}

You can instead perform similar operations in the database, using basic arithmetic and (I think) equality operators: +, -, >, <, etc., as well as MySQL's date and time functions.

...though I'm curious to know why you are using getString() instead of ResultSet#getTimestamp().

One other word of advice: consider using Joda Time instead of java.util.Date and java.util.Calendar. See Should I use Java date and time classes or go with a 3rd party library like Joda Time?

edited May 23 '17 at 12:02

Community

1
1

answered Jun 01 '11 at 15:10

Matt Ball

354,903
100
647
710

1

Also curious, but would use getTimestamp() not getDate(). Since OP is using getString() good answer. – Mark Jun 01 '11 at 20:30
@Mark you're definitely right about that, edited accordingly. – Matt Ball Jun 01 '11 at 20:32

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

Prior to JDK 8

You can use ResultSet.getDate('date') to retreive a Date object. Then use the method Date.before() or Date.after() to check.

JDK 8 or later

Refer to Basil Bourque's answer below.

edited May 23 '17 at 10:33

Community

1
1

answered Jun 01 '11 at 15:11

Genzer

2,921
2
25
38

3

`getDate` returns a `java.sql.Date` which should be normalized so that the time is 0:00:00. Correct is to use `getTimestamp`. – user85421 Jun 01 '11 at 15:46
Why do we have to normalize the time to 00:00:00. In the question, he wanted to _"check whether the current date and time"_. – Genzer Jun 01 '11 at 16:19
1

We? We don't have to. It is normalized by the driver, and that is the problem. – user85421 Jun 01 '11 at 18:15
`java.sql.Date` pretends to represent a date-only without a time-of-day or a time zone. But in fact, because of its poor design of inheriting from `java.util.Date`, `java.sql.Date` does indeed have a time-of-day stored within. As a hack it sets its time-of-day to `00:00:00.0`. To get a date with time-of-day from database use `java.sql.Timestamp`. Confusing? Yes. Try to avoid all these classes as much as possible, using [java.time](http://docs.oracle.com/javase/8/docs/api/java/time/package-summary.html) classes instead. See [my Answer](http://stackoverflow.com/a/36273188/642706) for more info. – Basil Bourque Mar 29 '16 at 00:01

Basil Bourque · Answer 3 · 2016-09-13T03:10:23.367

tl;dr

JDBC 4.2 and later:

Boolean isFutureTask = myResultSet.getObject( "myDateColumn" , Instant.class )
                                  .isAfter( Instant.now() ) ;

Older JDBC:

Boolean isFutureTask = myResultSet.getTimestamp( … )
                                  .toInstant()
                                  .isAfter( Instant.now() ) ;

java.time

The other answers were correct but use outmoded classes. The java.util.Date/.Calendar classes have been supplanted by the java.time framework built into Java 8 and later.

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

Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport and further adapted to Android in ThreeTenABP.

Date-time types

You should be storing your date-time values as a date-time type in your database. I believe that would be TIMESTAMP type in MySQL (but I'm a Postgres man myself).

Then use the java.sql.Timestamp type to move data in/out of the database.

java.sql.Timestamp ts = myResultSet.getTimestamp( 1 );

Immediately convert from java.sql to java.time. The java.sql classes are part of those old date-time classes that have proven to be poorly designed, confusing, and troublesome. Use them only until JDBC drivers are updated to directly deal with java.time types.

The old java.util.Timestamp has new methods for converting to/from an Instant. An Instant is a moment on the timeline in UTC with a resolution of nanoseconds.

java.time.Instant instant = ts.toInstant();

Get the current moment.

Instant now = Instant.now();

Compare.

Boolean isStoredDateTimePast = now.isAfter( instant );

In JDBC 4.2 and later, your JDBC driver may be able to directly access the value from the database as an Instant object via the ResultSet::getObject method.

If you must parse that string, convert it to standard ISO 8601 format. Replace the space in the middle with a T. Assuming the string represents an offset-from-UTC of zero, append Z for Zulu which means UTC. The java.time classes parse/generate strings by default in ISO 8601 formats.

String inputOriginal = "2011-12-30 17:10:00".
String input = inputOriginal.replace( " " , "T" ) + "Z" ;
Instant instant = Instant.parse( input );

score 1 · Answer 4 · answered Jun 01 '11 at 15:11

1

Why don't you just use getDate('date') instead of getString() and use Date's before() or after() ?

answered Jun 01 '11 at 15:11

TC1

1
3
20
31

`getDate` returns a `java.sql.Date` which should be normalized so that the time is 0:00:00. Correct is to use `getTimestamp`. – user85421 Jun 01 '11 at 15:45

score 0 · Answer 5 · answered Jun 01 '11 at 15:21

0

You can use the Joda DateTime api that is very easy for comparing dates, we have the same requirement in use that takes mysql datetime format and creates joda datetime objects for comparison.

answered Jun 01 '11 at 15:21

r0ast3d

2,639
1
14
18

1

It would be useful if you could show some code that demonstrates _how_ Joda makes this easier. – Matt Ball Jun 01 '11 at 20:48

score -1 · Answer 6 · edited Jun 01 '11 at 20:13

-1

java.util.Calendar.getInstance().getTime().getTime() will get you the current date and time in milliseconds

ResultSet.getDate('name of column').getTime()

will get you the date and time from the database....

u can compare/make logical operations like you do with numbers..... for example, this will check whether the system time is greater than the datetime from the database

java.util.Calendar.getInstance().getTime().getTime() > new java.sql.Date(WIDTH).getTime();

you can look up in the API documentation for more information on these methods....

Hope it helps

edited Jun 01 '11 at 20:13

Sotiris

38,986
11
53
85

answered Jun 01 '11 at 15:32

yomexzo

665
1
6
22

2

use `System.currentTimeMillis()` instead of creating a Calendar and a Date to get current date and time. And what is it with `new java.sql.Date(WIDTH)`? This is not datetime - it's only date - neither is it from the database - it is the Date using the given (WIDTH) milliseconds time value! – user85421 Jun 01 '11 at 15:57

Comparing MySQL and Java Time

6 Answers6

tl;dr

java.time

Date-time types

Linked