Conversion to java.sql.Date

Question

I have a String as below which needs to be converted into java.sql.Date format:

2017-08-31 01:40:00+00:00

I am using below code and I can see date is only parsed as 2017-08-31 and not the entire string as above. Can someone please suggest?

java.util.Date utilDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(dateTimeStamp);

java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

Based on the suggestion in the answers, I implemented below:

String dateTimeStamp = "2017-08-31 01:40:00+00:00";
java.text.DateFormat format = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ssZZ");
java.util.Date date = format.parse(dateTimeStamp);
java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());
System.out.println("timestamp - " + timestamp);

But, am getting below error:

java.text.ParseException: Unparseable date: "2017-08-31 01:40:00+00:00"
        at java.text.DateFormat.parse(DateFormat.java:366)
        at com.eneco.mysqlsink.WeatherForecastSink.WeatherForecastTask.put(WeatherForecastTask.java:94)
        at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:435)
        at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:251)
        at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:180)
        at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:148)
        at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:146)
        at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:190)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

Answer 1

java.sql.Date only provides the date.. You need to use java.sql.Timestamp to get both date and time.

java.text.DateFormat format = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
java.util.Date date = format.parse("2017-08-31 01:40:00+00:00");
java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());
System.out.println(timestamp);

Answer 2

You need to include timezone identifier in your format

java.util.Date utilDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ssZZ").parse(dateTimeStamp);
java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); 

Answer 3

To get the timestamp just use java.sql.Timestamp instead of java.sql.Date!

The toString() method of the java.sql.Timestamp object should return the string as you needed.

Answer 4

The format you are using is good but you loose Timezone offset.

If you want to consider TimeZone offset then please use below format.

java.util.Date utilDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(dateTimeStamp);

Result:

utilDate = Thu Aug 31 01:40:00 IST 2017

java.util.Date utilDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ssX").parse(dateTimeStamp);

Result:

utilDate = Thu Aug 31 07:10:00 IST 2017.

I am running this code from India and there is offset of 05:30 Hours in timezone.

Answer 5

You're using the hh pattern, which corresponds to hour-of-am-pm field (values from 1 to 12). As the input doesn't have AM/PM designator, this won't always work as expected. You must change it to HH (hour-of-day, with values from 0 to 23).

Also, to parse the offset +00:00 you need to use the X pattern:

String input = "2017-08-31 01:40:00+00:00";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssX");
// java.util.Date
Date date = sdf.parse(input);

The X pattern was introduced in Java 7. If you're using an older version, you can also set the UTC timezone in the formatter:

// "X" is not supported
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// set UTC in the formatter
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
// java.util.Date
Date date = sdf.parse(input);

This is worse because you need to know the offset from the input and set it in the formatter. So it's better to use X if supported.

Then you can create the sql dates from the java.util.Date:

java.sql.Date sqlDate = new java.sql.Date(date.getTime());
java.sql.Timestamp timestamp = new Timestamp(date.getTime());

But remind that if you just System.out.println the sql date or the timestamp, those will be converted to the JVM default timezone (giving you the impression that it's wrong: see this article for more details).

Also, keep in mind that a java.sql.Date just keeps the date fields (day/month/year), setting the hours to zero (so the 01:40 is lost). A java.sql.Timestamp, on the other hand, preserves the whole UTC millis value.

---

Java new Date/Time API

The old classes (Date, Calendar and SimpleDateFormat) have lots of problems and design issues, and they're being replaced by the new APIs.

If you're using Java 8, consider using the new java.time API. It's easier, less bugged and less error-prone than the old APIs.

If you're using Java 6 or 7, you can use the ThreeTen Backport, a great backport for Java 8's new date/time classes. And for Android, you'll also need the ThreeTenABP (more on how to use it here).

The code below works for both. The only difference is the package names (in Java 8 is java.time and in ThreeTen Backport (or Android's ThreeTenABP) is org.threeten.bp), but the classes and methods names are the same.

First you parse the input to an OffsetDateTime, using a DateTimeFormatter to specify the format:

String input = "2017-08-31 01:40:00+00:00";

DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ssXXX");
OffsetDateTime odt = OffsetDateTime.parse(input, fmt);

Then you can convert it to sql types. In Java 8, there are built-in methods to do it:

java.sql.Date sqlDate = java.sql.Date.valueOf(odt.toLocalDate());
java.sql.Timestamp sqlTimestamp = java.sql.Timestamp.from(odt.toInstant());

In Java 7, the ThreeTen Backport has the org.threeten.bp.DateTimeUtils class:

java.sql.Date sqlDate = DateTimeUtils.toSqlDate(odt.toLocalDate());
java.sql.Timestamp sqlTimestamp = DateTimeUtils.toSqlTimestamp(odt.toInstant());