Fork me on GitHub

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 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?


Working with Date and Time


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


I’m reading this at the moment as I have a feeling the required info is in here somewhere…


ah yes, got it working simple_smile

👍 4

fantastic! 🙂


(extend-protocol result-set/ReadableColumn
  (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)))


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


they are not different arities of the same thing

👍 4

@rs Did you require Or did you manually add the extend-protocol?


and the localdate typehint is bogus


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


I manually added it, copy paste error on the typehint

seancorfield20:04:37 -- you can get the same effect by just calling (read-as-local) from that namespace (somewhere in your program startup)


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?


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


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


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?


That's correct.


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


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


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


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.


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


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.


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


ok, makes sense


thanks for answer