Clojure/JDBC/Postgresql: I am trying to update a timestamp value in postgresql from a string, getting an error

Question

Using the clojure jdbc library with postgresql. I have a table "xxx" with a timestamp column "created_at" in postgresql, and I have a string containing a date in the right format. Doing an insert fails:

(require '[clojure.java.jdbc :as sql])

(sql/with-connection *db*
  (sql/insert-record :xxx {:created_at "Thu Feb 09 10:38:01 +0000 2012"}))

Here is the error:

org.postgresql.util.PSQLException: ERROR: column "created_at" 
is of type timestamp with time zone but expression is of type character varying

So I understand that postgres requires a timestamp value, but how do I convert my string representation of the date into something postgres will accept? java.util.Date fails also, and I can't find any docs on the clojure postgres library.

Thanks!

Michał Marczyk · Accepted Answer · 2012-02-16 05:11:09Z

18

You'll need to pass in a java.sql.Timestamp instance. To parse your string into one using clj-time, a Joda-Time-wrapping library for Clojure, you'd do something along the following lines:

(require '(clj-time [format :as timef] [coerce :as timec])

(->> "Thu Feb 09 10:38:01 +0000 2012"
     (timef/parse (timef/formatter "EEE MMM dd HH:mm:ss Z yyyy"))
     timec/to-timestamp)

The returned value can then be passed to PostgreSQL via JDBC.

In case you're obtaining the date in some other string format and converting it to this one, you could skip the conversion and provide an appropriate formatter for the original representation. There are quite a few available by default in the clj-time.format/formatters map, say (clj-time.format/show-formatters) at the REPL to see a list with examples. Also, clj-time.coerce/from-string tries all default formatters in sequence returning the value of the first succeeding parse (nil if there is none). If you're obtaining the date as a java.util.Date or a long, see from-date and from-long in the same namespace.

Use [clj-time "0.3.6"] as the dependency specifier in your project.clj if you decide to use clj-time.

Alternatively, you could use some other way of parsing your timestamp string into a java.sql.Timestamp; Timestamp itself can parse a different string representation:

(java.sql.Timestamp/valueOf "2004-10-19 10:23:54")

clj-time is the most sane way of dealing with date and time in Clojure, though, so it's likely to be worth your while.

edited Feb 16, 2012 at 5:11

answered Feb 16, 2012 at 4:52

Michał Marczyk

84.5k13 gold badges203 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

prismofeverything Over a year ago

clj-time is indeed the way to handle time in Clojure, thanks much!

Sean Corfield Over a year ago

Since this is an old answer, and I'm one of the maintainers of clj-time, I'll note that clj-time is deprecated and we encourage folks to migrate to Java Time (built-in as of Java 8) or one of its wrapper libraries -- per the README of clj-time!

Collectives™ on Stack Overflow

Clojure/JDBC/Postgresql: I am trying to update a timestamp value in postgresql from a string, getting an error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related