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

Question

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.

I recommend you don’t use `java.sql.Timestamp`. That class is very poorly designed and long outdated. Instead use `OffsetDateTime` or `LocalDateTime`; both are from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Jun 02 '21 at 17:40
`1622648253010` is not a `timestamp` value http://blog.sql-workbench.eu/post/epoch-mania/ — , Jun 02 '21 at 19:35

score 3 · Answer 1 · answered Jun 02 '21 at 17:19

java.time

The 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^*.

Convert the epoch milliseconds to OffsetDateTime:

long millisStart = 1622648253010L;
OffsetDateTime odtStart = Instant.ofEpochMilli(millisStart).atOffset(ZoneOffset.UTC);

Use the OffsetDateTime with the PreparedStatement:

PreparedStatement st = conn.prepareStatement(SELECT * FROM mytable WHERE columnfoo BETWEEN ? AND ?");
st.setObject(1, odtStart);
st.setObject(2, odtEnd);
ResultSet rs = st.executeQuery(); 
while (rs.next()) {
   //...
}
rs.close();
st.close();

Learn more about java.time, 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.}

score 2 · Answer 2 · answered Jun 02 '21 at 16:59

2

Divide by 1000 and use to_timestamp)

select to_timestamp(1622648253010/1000);
      to_timestamp      
------------------------
 2021-06-02 17:37:33+02

answered Jun 02 '21 at 16:59

Dri372

1,275
3
13

score 0 · Answer 3 · answered Jun 02 '21 at 16:57

0

Long type doesn't have methods getSeconds() and getNanos(). See: https://docs.oracle.com/javase/7/docs/api/java/sql/Timestamp.html#Timestamp%28long%29

answered Jun 02 '21 at 16:57

markw

321
1
3
14

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

3 Answers3

java.time