Convert input date in YYYY-MM-DD'T'HH:MM:SS:SSS+HHMM format to the java.sql.Timestamp with the timezone from input

Question

We have a requirement wherein the string is passed from UI in YYYY-MM-DD'T'HH:MM:SS:SSS+HHMM along with timezone (say example: 2020-01-20'T'05:40:334-0500) whcih needs to be parsed into sql timestamp to be inserted to DB.

All attempts are ignoring the timestamp sent from input and setting the local timestamp while setting in DB. Please advice if any solution?

Not sure how you are storing the timestamp in DB since you have not provided any code example. But I believe the following post will be useful for you to understand what it is problem and fine solution: https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific — trims, Apr 30 '20 at 07:28
@ThorbjørnRavnAndersen java.sql.Date does not store the timezone information in it. So I am using java.sql.Timestamp, however I am failing to convert the timezone from input string to the java.sql.Timezone (the timezone get defaulted to localtimezone when I convert) — Ajay Dsouza, Apr 30 '20 at 08:35
@trims I am setting this timestamp object to the tabletype which will be passed as input to the stored procedure call with SpringStoredProcedure interface. The real problem is that conversion to java.sql.Timestamp erases the timezone info, so after the conversion using DateFormatter I am not able to see timezone on printing it in console logs as well. I went through the link you provided, thanks for the same. But unfortunely the scenario that I mentioned is not answered there. Thanks again for responses. — Ajay Dsouza, Apr 30 '20 at 08:38
Why on Earth do you need the time zone stored for each date? — Thorbjørn Ravn Andersen, Apr 30 '20 at 11:16
I recommend you don’t use `java.sql.Timestamp`. That class is poorly designed and long outdated. Instead use either `OffsetDateTime` or `LocalDateTime`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Apr 30 '20 at 14:29
The datatype in SQL, is that `timestamp` without time zone or `timestamp with time zone`? (The latter is recommended.) — Ole V.V., Apr 30 '20 at 14:30
I believe and hope that `2020-01-20'T'05:40:334-0500` isn’t quite right? (1) While you have single quotes around the `T` in a format pattern, you should not in the string itself. (2) Is that 334 seconds after 05:40 in the morning??! Probably a typo? — Ole V.V., Apr 30 '20 at 19:27

score 2 · Accepted Answer · answered Apr 30 '20 at 19:58

java.time and JDBC 4.2 or later

I recommend that you use java.time, the modern Java date and time API, for your date and time work. The java.sql.Timestamp class is poorly designed, a true hack on top of the already poorly designed java.util.Date class. Fortunately both are also long outdated. You may have thought that you needed a Timestamp for transferring a date and time value to your SQL database. Since JDBC 4.2 this is no longer true.

First, to parse your string:

    DateTimeFormatter uiFormatter = new DateTimeFormatterBuilder()
            .append(DateTimeFormatter.ISO_LOCAL_DATE_TIME)
            .appendPattern("XX")
            .toFormatter();

    String stringFromUi = "2020-01-20T05:40:34-0500";

    OffsetDateTime dateTime = OffsetDateTime.parse(stringFromUi, uiFormatter);

    System.out.println(dateTime);

Output this far:

2020-01-20T05:40:34-05:00

I have corrected your string, though. It‘s so close to being ISO 8601 format, so I was convinced that it was intended to conform to this standard. If you intended milliseconds in there, no problem, the code will work with a fraction on the seconds too, for example 2020-01-20T05:40:30.334-0500. If you could persuade your UI folks to send the string with a colon in the offset, e.g., 2020-01-20T05:40:34-05:00, you would not need a formatter but could use the one-arg OffsetDateTime.parse(String).

Next, I was assuming that you asked for a Timestamp for interaction with your database. And since you asked for a Timestamp with the time zone from the input, I further assumed that the datatype on the SQL side is timestamp with time zone. Which is recommended for timestamps. In this case since JDBC 4.2 you directly pass our OffsetDateTime to the JDBC driver. For a simplified example:

    PreparedStatement ps = yourDatabaseConnection.prepareStatement(
            "insert into your_table(your_timestamp_with_time_zone_column) values (?);");
    ps.setObject(1, dateTime);
    int rowsInserted = ps.executeUpdate();

If you do need a Timestamp for some legacy API that you cannot afford to change just now, first know that a java.sql.Timestamp cannot have a time zone or offset from UTC. The good way to obtain a Timestamp is by converting from Instant, another modern class:

    Instant instant = dateTime.toInstant();
    Timestamp ts = Timestamp.from(instant);
    System.out.println(ts);

Output in my time zone, Europe/Copenhagen, is:

2020-01-20 11:40:34.0

It’s correct even though it may look like it’s 6 hours wrong. Europe/Copenhagen was at UTC offset +01:00 in January. When we print the Timestamp, we are implicitly invoking its toString method. Timestamp.toString() uses the time zone setting of the JVM for rendering the string to be returned. So your date-time string has now been converted first from UTC offset -05:00 to some internal format that we don’t need to care about and then to offset +01:00, a difference of 6 hours from the starting point.

Links

Oracle tutorial: Date Time explaining how to use java.time.
Wikipedia article: ISO 8601
Other questions about a Timestamp with a time zone or offset:
- Java SQL Timestamp to ZonedDateTime
- How to generate a java.sql.Timestamp in UTC for a query comparison?
- For a given Date object, capture it's value relevant to GMT timezone (this one even has a couple of answers)

Thank you @Ole-v-v for the good explantion. We will try this option and update once it worked. Thanks again. — Ajay Dsouza, May 15 '20 at 10:25

Ananthapadmanabhan · Answer 2 · 2020-04-30T10:52:09.507

@Ajay Dsouza Have you tried using the Java 8 ZonedDateTime instead of the standard Date for your use case and set the timezone explicitly in the statement.For instance :

ZonedDateTime receivedTimestamp = somevalue;
Timestamp ts = new Timestamp(receivedTimestamp.toInstant().toEpochMilli());
ps.setTimestamp(
   1, 
   ts, 
   Calendar.getInstance(TimeZone.getTimeZone(receivedTimestamp.getZone()))
);

Actually the issue that you face when converting the timezone from input string to the java.sql.Timezone is actually pretty well known issue.It happens because by default JDBC uses the local timezone of the JVM during conversion unless you set a property(in spring-boot or configure hibernate)explicitly to change that or you could use the overloaded prepared statement method like I have provided above to set the time zone explicitly in the statement.

The PreparedStatement setTimestamp Javadoc states that:

With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

In Spring-boot you can set the following property to set timezone globally (this is not probably what you want but) :

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Thank you @Ananthapadmanabhan, I (and my teammate) will try ZonedDateTime and see if this is good. Thanks for the answer. — Ajay Dsouza, May 15 '20 at 10:27

Convert input date in YYYY-MM-DD'T'HH:MM:SS:SSS+HHMM format to the java.sql.Timestamp with the timezone from input

2 Answers2

java.time and JDBC 4.2 or later

Links