how to convert time in seconds to timestamp with time zone in clojure?

Question

I am trying to convert time in seconds to timestamp with timezone.
I tried

(defn- seconds-to-milliseconds[time]
  (* time 1000))

(:require [clj-time.coerce :as clj-time])
(clj-time/from-long (seconds-to-milliseconds 1564132000))

Result is: #clj-time/date-time"2019-07-26T09:06:40.000Z"

But this result I can not store in Postgres database because i get

PSQLException: Can't infer the SQL type to use for an instance of org.joda.time.DateTime. Use setObject() with an explicit Types value to specify the type to use

So I need to convert it

(clj-time/to-timestamp (clj-time/from-long (seconds-to-milliseconds 1564132000))))

Result is

#inst"2019-07-26T09:06:40.000000000-00:00"

which I can store this in postgres but it don't have time zone.

could anyone help me to convert time in seconds to timestamp with time zone.

Answer 1

I guess you can use the plain old java.util.Date:

(-> time-in-millis
    (java.util.Date.)
    (save-in-db))

Since you have time in milliseconds on the input anyway, you can use that directly without having to convert it to a timezone-aware date type.

Btw. although PostgreSQL has "with time zone" data type internally, it doesn't affect how values are stored (that is your "timezone" information isn't stored anyway!) -> see Difference between timestamps with/without time zone in PostgreSQL for more details.

Answer 2

Found a solution to this problem. By converting to UTC and storing in database.

(:require [clj-time.coerce :as clj-time])
(:import (java.util TimeZone))

(TimeZone/setDefault (TimeZone/getTimeZone "UTC"))

(defn- seconds-to-milliseconds
  [time]
  (* time 1000))

(defn seconds-to-timestamp
  [time]
  (clj-time/to-sql-time (clj-time/from-long (seconds-to-milliseconds time))))

Example: (seconds-to-timestamp 1564132000)