How to convert string to get java.sql.Timestamp using DateTimeFormatter

Question

I have a timestamp in string and I am using DateTimeFormatter to parse the string as below and assign that to a timestamp type variable

import java.sql.Timestamp
import java.time.format.DateTimeFormatter
import java.time.temporal.TemporalAccessor
import java.time.Instant

String myTime = "2020-08-03T20:15:49"
String myTimeFormat = "yyyy-MM-dd'T'HH:mm:ss"

DateTimeFormatter timestampFormatter = DateTimeFormatter.ofPattern(myTimeFormat);
TemporalAccessor ta = timestampFormatter.parse(tempValue);


// getting error that Cannot create Instant from java.time.format.Parsed
Timestamp finalTime = Timestamp.from(Instant.from(ta));

How to convert it to java.sql.Timestamp?

Context: I am trying to convert a string column in a spark dataframe (using timestamp format) to a timestamp column and for which I am using the above logic in my udf (using udf as I need to perform other checks in addition to just casting) and thus trying to convert to Timestamp to apply the spark schema with this column as Timestamp

Ref: https://spark.apache.org/docs/latest/sql-ref-datatypes.html

Answer 1

What is a timestamp?

Wikipedia defines a timestamp as

a sequence of characters or encoded information identifying when a certain event occurred …

So your string is not a timestamp or at least can only be considered one if we know which time zone (or UTC offset) is assumed for the date and time of day it contains. Today most IT systems are not confined to one time zone, so it is generally recommended to include UTC offset information with your timestamps and/or keep them in UTC. I am assuming that you asked for an old-fashioned java.sql.Timestamp for use with your SQL database. Depending on your adherence to the recommendations you will need different types both in SQL and in Java.

1. Recommended: Use explicit offset, preferably UTC. With most database engines this means using its timestamp with time zone or timestamptz data type. The JDBC standard since 4.2 says that you should then use OffsetDateTime in Java. Many drivers also handle Instant, the class we would normally prefer for an unambiguous timestamp in Java. Your attempt to create an Instant may hint that this agrees with your intentions.
2. Not recommended: Use implicit offset, preferably UTC. Many old applications would use their own time zone. In any case use timestamp (without time zone) in SQL and LocalDateTime in Java. The lack of offset in your string may hint that this was your approach.

The java.sql.Timestamp class that you mentioned is poorly designed, in fact, it is a true hack on top of the already poorly designed java.util.Date class. So Timestamp is not among the classes I recommend for sending your timestamp value to the database, whether for storage or for use in a query.

Saving your timestamp to SQL

Here’s a code example using OffsetDateTime and a custom assumed time zone.

String myTime = "2020-08-03T20:15:49";
OffsetDateTime odt = LocalDateTime.parse(myTime)
        .atZone(ZoneId.of("Asia/Colombo"))
        .toOffsetDateTime();

PreparedStatement ps = yourDatabaseConnection
        .prepareStatement("insert into your_table(your_timestamptz_column) values (?);");
ps.setObject(1, odt);
ps.executeUpdate();

    

Since JDBC 4.2 the setObject method accepts java.time types including OffsetDateTime.

If your time string is UTC, the conversion to OffsetDateTime is a bit simpler:

OffsetDateTime odt = LocalDateTime.parse(myTime).atOffset(ZoneOffset.UTC);

Using Instant: You may convert the OffsetDateTime from before simply:

Instant inst = odt.toInstant();

Now you can pass inst to setObject() in the same way that we passed odt before.

If you are using timestamp without time zone in SQL and LocalDateTime in Java, the answer by Arvind Kumar Avinash already shows the simple way to parse your string. Also a LocalDateTime can be passed to setObject() in the same way as above.

By the way, we most often neither need nor want to use the TemporalAccessor interface. Its documentation says:

This interface is a framework-level interface that should not be widely used in application code. …

Answer 2

You do not need a DateTimeFormatter

The modern Date-Time API is based on ISO 8601 and does not require using a DateTimeFormatter object explicitly as long as the Date-Time string conforms to the ISO 8601 standards.

Once parsed into LocalDateTime, you can obtain java.sql.Timestamp using Timestamp#valueOf.

Demo:

import java.sql.Timestamp;
import java.time.LocalDateTime;

public class Main {
    public static void main(String[] args) {
        String myTime = "2020-08-03T20:15:49";
        LocalDateTime ldt = LocalDateTime.parse(myTime);
        Timestamp finalTime = Timestamp.valueOf(ldt);
        System.out.println(finalTime);
    }
}

Output:

2020-08-03 20:15:49.0

ONLINE DEMO

Note: The java.util Date-Time API and their formatting API, SimpleDateFormat are outdated and error-prone. Since java.sql.Timestamp extends java.util.Date, it inherits the same drawbacks. It is recommended to stop using them completely and switch to the modern Date-Time API^*. Check this answer and this answer to learn how to use java.time API with JDBC.

Learn more about the modern Date-Time API from Trail: Date Time.

---

^{* For any reason, if you have to stick to Java 6 or Java 7, you can use ThreeTen-Backport which backports most of the java.time functionality to Java 6 & 7. If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.}

Answer 3

Your issue isn't that you can't create a Timestamp: it's that you can't create an Instant.

An Instant identifies a single point on the timeline, identified via an offset from Unix epoch (1970-1-1 00:00:00 UTC).

The problem with your input is that it doesn't identify a timezone. As such, it doesn't uniquely identify a single point in time, because 2020-08-03T20:15:49 isn't the same instant in London vs New York vs Shanghai vs Delhi (for example).

As such: parse your string to a LocalDateTime; then specify the time zone; then convert to an Instant; then convert to a Timestamp:

LocalDateTime ldt = LocalDateTime.parse(myTime, timestampFormatter);

// Or whichever time zone.
Instant instant = ldt.atZone(ZoneId.of("UTC")).toInstant();

Timestamp finalTime = Timestamp.from(instant);