Help with timestamp with timezone in YeSQL

[gvim]

I have a YeSQL query:

-- name: add-birth<!
INSERT INTO births (date_time) VALUES (:date_time)

In PostgreSQL I can enter:

INSERT INTO births (date_time) VALUES ('1967-07-31 06:30:00
America/Caracas')

... and all is well but my defquery equivalent:

(add-birth<! {:date_time "1967-07-31 06:30:00 America/Caracas"})

.... fails because the date_time string is passed to PostgreSQL as a
varchar, not a timestamp with timezone. So, to remedy this I tried
clj-time's coerce function:

(add-birth<! {:date_time (c/to-timestamp "1967-07-31 06:30:00
America/Caracas")})

.... which fails again because:

(c/to-timestamp "1967-07-31 06:30:00 America/Caracas")

.... evaluates to nil. However:

(c/to-timestamp "1967-07-31 06:30:00")

.... gives me an: #inst "1967-07-31T06:30:00.000000000-00:00" ,
whatever that is, so I checked the clj-time docs and it appears
to-timestamp doesn't handle timezones.

Any ideas?

gvim

[Michael Cramm]

Out of curiosity have you tried clj-time.coerce/to-sqs-time ?

[Sam Roberton]

On Sunday, 17 May 2015 01:46:14 UTC+10, g vim wrote:

<snip/>

  (c/to-timestamp "1967-07-31 06:30:00 America/Caracas")

.... evaluates to nil. However:

(c/to-timestamp "1967-07-31 06:30:00")

.... gives me an:  #inst "1967-07-31T06:30:00.000000000-00:00" ,
whatever that is, so I checked the clj-time docs and it appears
to-timestamp doesn't handle timezones.

Any ideas?

Looks like you might want this:

user> (clj-time.format/parse (clj-time.format/formatter "yyyy-MM-dd HH:mm:ss ZZZ") "1967-07-31 06:30:00 America/Caracas")

#object[org.joda.time.DateTime 0x79e05a0 "1967-07-31T10:30:00.000Z"]

I suspect you'll then need some further work to make sure that the Joda DateTime object correctly converts into what you actually want stored in the database when it's set on the SQL PreparedStatement (something like this, but extending the type to Joda's DateTime rather than java.util.Date) -- or alternatively you might be OK just coercing the above to a java.sql.Date or java.sql.Timestamp.

 

gvim

[gvim]

'Turns out I was looking in the wrong place. YeSQL relieves you of all
the clj-time formatting as you can simply add the PostgreSQL cast
directly to your placeholder so this:

-- name: add-birth<!
INSERT INTO births (date_time) VALUES (:date_time)

.... becomes:

-- name: add-birth<!
INSERT INTO births (date_time) VALUES (:date_time::timestamptz)

Now this works:

(add-birth<! {:date_time "1967-07-31 06:30:00 America/Caracas"})

gvim

On 17/05/2015 01:36, Sam Roberton wrote:
> On Sunday, 17 May 2015 01:46:14 UTC+10, g vim wrote:
>
> <snip/>
>
> (c/to-timestamp "1967-07-31 06:30:00 America/Caracas")
>
> .... evaluates to nil. However:
>
> (c/to-timestamp "1967-07-31 06:30:00")
>
> .... gives me an: #inst "1967-07-31T06:30:00.000000000-00:00" ,
> whatever that is, so I checked the clj-time docs and it appears
> to-timestamp doesn't handle timezones.
>
> Any ideas?
>
>
> Looks like you might want this:
>
> user> (clj-time.format/parse (clj-time.format/formatter "yyyy-MM-dd
> HH:mm:ss ZZZ") "1967-07-31 06:30:00 America/Caracas")
> #object[org.joda.time.DateTime 0x79e05a0 "1967-07-31T10:30:00.000Z"]
>
> I suspect you'll then need some further work to make sure that the Joda
> DateTime object correctly converts into what you actually want stored in
> the database when it's set on the SQL PreparedStatement (something like
> this

> <http://tapestryjava.blogspot.ie/2014/09/postgres-jdbc-time-zones.html>,

> but extending the type to Joda's DateTime rather than java.util.Date) --
> or alternatively you might be OK just coercing the above to a
> java.sql.Date or java.sql.Timestamp.
>
> gvim
>

> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clo...@googlegroups.com
> Note that posts from new members are moderated - please be patient with
> your first post.
> To unsubscribe from this group, send email to
> clojure+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en
> ---
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to clojure+u...@googlegroups.com
> <mailto:clojure+u...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.