Search code examples
javaspringdatedatetimesql-timestamp

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

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?

Solution

  • 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