Json response to postgresql datetime conversion Java postgresql

Question

I am getting the JSON responses for the Date time field as follows: /Date(1534291200000)/ and PT12H18M02S

 SELECT     CAST('ClearingDate' AS TIMESTAMP) from testdata;

On using CAST functions or to_timestamp I am getting the following error:

ERROR: invalid input syntax for type timestamp: "/Date(1534291200000)/" SQL state: 22007

How can I convert this to timestamp using postgresql? If not postgresql is there a way to do so in Java?

Answer 1

The solution to directly convert it in PostgreSQL:

to_timestamp(CAST(SUBSTRING (CAST(Clearingdate AS varchar), 9, 10) AS NUMERIC))
from date_test;

I finally figured it out. Thanks a lot for all your inputs.

Answer 2

use timestamp (import java.sql.Timestamp)

String fromJson = "/Date(1534291200000)/";
String ts = fromJson.substring(6, fromJson.length()-2);
Long tsInMillisSec = Long.parseLong(ts);
Timestamp ts = new Timestamp(tsInMillisSec);

Answer 3

Sorry, I don’t know how to do this in PostgreSQL. In Java:

    String timestampString = "/Date(1534291200000)/";
    String millisString = timestampString.replaceFirst("^/Date\\((\\d+)\\)/$", "$1");
    Instant inst = Instant.ofEpochMilli(Long.parseLong(millisString));
    System.out.println(inst);

Output is

2018-08-15T00:00:00Z

I am using a regular expression for validating the syntax of the string and taking out just the substring of digits. This number denoted milliseconds since the epoch. The Instant class from java.time, the modern Java date and time API, is the correct one to use for a timestamp (forget about the outdated Timestamp class, we don’t need it anymore).

If you need to store this back into PostgreSQL:

    PreparedStatement stmt = yourConnection.prepareStatement(
            "insert into your_table(your_timestamp_column) values (?);");
    stmt.setObject(1, inst);

Please modify to your database design and situation.

Link: Oracle tutorial: Date Time explaining how to use java.time.

Answer 4

java.time

The date-time API of java.util and their formatting API, SimpleDateFormat are outdated and error-prone. It is recommended to stop using them completely and switch to the modern date-time API.

• 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.

Instant#ofEpochMilli

The key here is to get an object of Instant out of the milliseconds in the given string. Once you have Instant, you can convert it to other java.time types e.g. ZonedDateTime or even to the legacy java.util.Date.

A note on the regex, \D+: \D specifies a non-digit while + specifies its one or more occurrence(s).

Demo:

import java.time.Instant;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Locale;

public class Main {
    public static void main(String[] args) {
        String text = "/Date(1534291200000)/";
        // Replace all non-digits i.e. \D+ with a blank string
        Instant instant = Instant.ofEpochMilli(Long.parseLong(text.replaceAll("\\D+", "")));
        System.out.println(instant);

        // Now you can convert Instant to other java.time types e.g. ZonedDateTime
        // ZoneId.systemDefault() returns the time-zone of the JVM. Replace it with the
        // desired time-zone e.g. ZoneId.of("Europe/London")
        ZonedDateTime zdt = instant.atZone(ZoneId.systemDefault());
        // Print the default format i.e. the value of zdt#toString
        System.out.println(zdt);

        // A custom format
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("EEE MMMM dd HH:mm:ss uuuu", Locale.ENGLISH);
        String strDateTimeFormatted = zdt.format(dtf);
        System.out.println(strDateTimeFormatted);
    }
}

Output:

2018-08-15T00:00:00Z
2018-08-15T01:00+01:00[Europe/London]
Wed August 15 01:00:00 2018

How to get java.util.Date from an Instant?

You should avoid using java.util.Date but for whatsoever purpose, if you want to get java.util.Date, all you have to do is to use Date#from as shown below:

Date date = Date.from(instant);

What about PT12H18M02S?

You can parse it to java.time.Duration which is modelled on ISO-8601 standards and was introduced with Java-8 as part of JSR-310 implementation.

If you have gone through the above links, you might have already learnt that PT12H18M02S specifies a duration of 12 hours 18 minutes 2 seconds which you add to a date-time object (e.g. zdt obtained above) to get a new date-time.

Duration duration = Duration.parse("PT12H18M02S");
ZonedDateTime zdtUpdated = zdt.plus(duration);
System.out.println(zdtUpdated);

Output:

2018-08-15T13:18:02+01:00[Europe/London]

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

How to use java.time types with JDBC?

The PostgreSQL™ JDBC driver implements native support for the Java 8 Date and Time API (JSR-310) using JDBC 4.2.

Note that ZonedDateTime, Instant and OffsetTime / TIME [ WITHOUT TIMEZONE ] are not supported. Also, note that all OffsetDateTime instances will have to be in UTC (have offset 0). This is because the backend stores them as UTC.

OffsetDateTime odt = zdt.withZoneSameInstant(ZoneOffset.UTC).toOffsetDateTime();
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, odt);
st.executeUpdate();
st.close();

Answer 5

This might work:

select cast(cast('{"dtJson":"2020-10-23 13:40:54"}'::json->'dtJson' as varchar) as timestamp);