How do I convert Unix timestamp in milliseconds to a Timestamp compatible with PostgreSQL?

Ask Time：2021-06-03T00:51:30 Author：TheStranger

I have a REST service with an endpoint that GETs data within a requested interval. The requested interval comes down as two Unix Timestamps in milliseconds of the type Long. Like this:

Long start = 1622648253010;
Long end = 1622651853010;

I have a PostgreSQL database which has a table with a Timestamp column. I need to select some data from that table where the timestamp is between the two Unix Timestamps. However PostgreSQL does not understand Unix Timestamps, so I have to convert the two timestamps to PostgreSQL compatible timestamps before using them in the query. The conversion should be done in Java, and not in the SQL Query.

How do I do that? The only way I know is:

java.sql.Timestamp.from(Instant.ofEpochSecond(
                        start.getSeconds(), start.getNanos()))

But this does not work.

Author:TheStranger，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/67809383/how-do-i-convert-unix-timestamp-in-milliseconds-to-a-timestamp-compatible-with-p

Arvind Kumar Avinash :

java.time\nThe java.util Date-Time API 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*.\n\nConvert the epoch milliseconds to OffsetDateTime:\n\nlong millisStart = 1622648253010L;\nOffsetDateTime odtStart = Instant.ofEpochMilli(millisStart).atOffset(ZoneOffset.UTC);\n\n\nUse the OffsetDateTime with the PreparedStatement:\n\nPreparedStatement st = conn.prepareStatement(SELECT * FROM mytable WHERE columnfoo BETWEEN ? AND ?");\nst.setObject(1, odtStart);\nst.setObject(2, odtEnd);\nResultSet rs = st.executeQuery(); \nwhile (rs.next()) {\n //...\n}\nrs.close();\nst.close();\n\nLearn more about java.time, the modern Date-Time API* from Trail: Date Time.\n\n* 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.\n",

2021-06-02T17:19:12

Dri372 :

Divide by 1000 and use to_timestamp)\nselect to_timestamp(1622648253010/1000);\n to_timestamp \n------------------------\n 2021-06-02 17:37:33+02\n",

2021-06-02T16:59:48

How do I convert Unix timestamp in milliseconds to a Timestamp compatible with PostgreSQL?