Clojurians Log v2

Hi everyone. I’ve just started using next.jdbc with Postgres, and am struggling to get it to read date columns correctly. I’m using next.jdbc.sql/insert! to insert a row containing a value of "2020-04-07" for a date column, but the return value has this converted to #inst \"2020-04-06T23:00:00.000-00:00\" which looks to be the wrong day. I don’t care about the time element - I just want it to return the same date I inserted, either as a string, keyword or a date object of some sort. What is the recommended way to achieve this?

It’s suspicous that it’s 1 hour out, BST vs UTC. Do you have a timezone issue?

If I do I shouldn’t do, I don’t want any timezone information stored with the date

I’ve got as far as figuring out it’s a java.sql.date i’m getting back, and that if I call .toString on this I get a string with the correct date e.g. "2020-04-07" for that example

But i’d rather next.jdbc perform that conversion for me if possible

Is this of help?

https://cljdoc.org/d/seancorfield/next.jdbc/1.0.409/doc/getting-started/tips-tricks

Working with Date and Time

I think that is talking about date conversion problems encountered when inserting, not problems with the return vals

but ty

I’m reading this at the moment as I have a feeling the required info is in here somewhere… https://cljdoc.org/d/seancorfield/next.jdbc/1.0.409/doc/getting-started/result-set-builders

ah yes, got it working :simple_smile:

fantastic! 🙂

(extend-protocol result-set/ReadableColumn
  java.sql.Date
  (read-column-by-label ^java.time.LocalDate [^java.sql.Date v _]
    (.toString v))
  (read-column-by-index ^java.time.LocalDate [^java.sql.Date v _2 _3]
    (.toString v)))

great!

curious to know why the 2 different arity versions are required there

they are not different arities of the same thing

@rs Did you require next.jdbc.date-time? Or did you manually add the extend-protocol?

and the localdate typehint is bogus

I manually added it, copy paste error on the typehint

https://cljdoc.org/d/seancorfield/next.jdbc/1.0.409/api/next.jdbc.date-time -- you can get the same effect by just calling (read-as-local) from that namespace (somewhere in your program startup)

Not sure how those ended up in the docs. They're not in the source code. And they are now not in the docs either (at least on GitHub -- they'll make it to cljdoc in the 1.0.next release 🙂 )

thanks@seancorfield. I did see that namespace mentioned in the tips & tricks section @dharrigan posted a link to, but it didn’t mention it extended the ReadableColumn protocol as well as the SettableParameter one!

"In addition, there are several read-as-* functions here that will extend next.jdbc.result-set/ReadableColumn to allow java.sql.Date and java.sql.Timestamp columns to be read as (converted to) various Java Time types automatically."

What change can I make so that is clearer about what it does? Would it be better as a separate paragraph in the ns docstring?

Perhaps moving that sentence up into the first paragraph?

^ @rs

Or is it that the Tips & Tricks reference to it could be better worded?

(I'm always looking to improve the documentation based on people's feedback, especially when they're just learning to use the library!)

Yes it was just the Tips & Tricks reference that lead me to believe it wouldn’t have worked for my case

I read this part: …does not always perform conversions from java.util.Date to a SQL data type. and thought this meant it wasn’t relevant because I was trying to convert from an SQL data type. Am I right in thinking that the purpose of extending SettableParameter is to make sure the dates we are inserting are converted to their SQL types? Where as extending ReadableColumn is for converting values as they come back out of the db?

I’ve found them to be clear enough so far! Thanks :simple_smile:

That's correct.

OK, I'll update the text in Tips & Tricks to make next.jdbc.date-time's purpose clearer. Thank you!

https://github.com/seancorfield/next-jdbc/commit/2fd27d18bd4dfe9d4cacd00b3558a9ae193fdd31 based your feedback @rs