Fork me on GitHub
#sql
<
2020-04-07
>
this.rob19:04:40

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?

dharrigan20:04:04

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

this.rob20:04:18

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

this.rob20:04:33

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

this.rob20:04:00

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

dharrigan20:04:05

Is this of help?

dharrigan20:04:13

Working with Date and Time

this.rob20:04:41

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

this.rob20:04:23

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

this.rob20:04:10

ah yes, got it working simple_smile

👍 4
dharrigan20:04:16

fantastic! 🙂

this.rob20:04:40

(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)))

this.rob20:04:03

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

hiredman20:04:52

they are not different arities of the same thing

👍 4
seancorfield20:04:26

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

hiredman20:04:52

and the localdate typehint is bogus

seancorfield20:04:39

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 🙂 )

this.rob20:04:28

I manually added it, copy paste error on the typehint

seancorfield20:04:37

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)

this.rob20:04:18

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!

seancorfield20:04:57

"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."

seancorfield20:04:39

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?

seancorfield20:04:13

Perhaps moving that sentence up into the first paragraph?

seancorfield21:04:08

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

seancorfield21:04:37

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

this.rob21:04:09

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

this.rob21:04:05

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

this.rob21:04:05

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?

seancorfield21:04:10

That's correct.

seancorfield21:04:36

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

kirill.salykin17:04:29

does it make sense to have both from and to java.time conversion enables by default? without need to invoke a function?

kirill.salykin17:04:24

now it seems confusing that it is converted by default it one direction only...

seancorfield17:04:52

No, it definitely does not make sense to convert to Java Time by default. Protocols are whole program and that should definitely be opt-in for consumption.

seancorfield17:04:33

It's fine to enable them by default going into next.jdbc since you're augmenting the JDBC behavior.

seancorfield17:04:25

A real-world example: we have a large code base that spans about ten years. A lot of the early code worked with dates/times via date-clj, based on java.util.Date etc. Then we introduced clj-time and Joda Time. Over the last several years we've been moving to Java Time. Having every single JDBC query suddenly returning Java Time is going to break all the "legacy" code that expects java.util.Date and conversion to Joda Time.

seancorfield17:04:56

Eventually, we hope to be able to switch everything to Java Time but we're years away from that.

kirill.salykin17:04:41

ok, makes sense

kirill.salykin17:04:48

thanks for answer