Clojurians Log v2

You are not storing timezones and your jvm and database are not set to use the same timezone

You can solve this by using the same timezone for both, or storing timezones

You'll need to show more of your code. You mentioned HoneySQL to me via DM, but didn't show what values you had in it for date/time. Also bear in mind:

user=> #inst "2023-10-03T19:34:00"
#inst "2023-10-03T19:34:00.000-00:00"
user=> (java.util.Date.)
#inst "2023-10-04T02:34:47.396-00:00"
user=> (java.time.LocalDateTime/now)
#object[java.time.LocalDateTime 0x9a7a808 "2023-10-03T19:37:10.395294338"]
user=> (java.time.Instant/now)
#object[java.time.Instant 0x53dfacba "2023-10-04T02:37:22.185943150Z"]

I have tried all 3 of them

#time/instant"2022-10-30T00:00:00Z"
#inst"2022-10-30T00:00:00.000-00:00"
#time/date-time"2022-10-30T02:00"

2022-10-30 02:00:00.000000

Now you've added yet another unmentioned piece of software - what are those #time/* tagged literals? Like I said, you're not showing us your actual code.

There is some liberal that defines distinct tag literals for every time under java.time

create table test
(
    open_time timestamp
);
this is honeSql

  (-> (honey.sql.helpers/insert-into :test)
      (honey.sql.helpers/values [{:open_time #inst"2022-10-30T00:00:00.000-00:00"}])
      (honey.sql/format {:pretty true}))

that generates this

["\nINSERT INTO test (open_time)\nVALUES (?)\n" #inst"2022-10-30T00:00:00.000-00:00"]

and then
  (with-open [connection (next.jdbc/get-connection (next.jdbc/get-datasource persist.postgres/db-config))]
    (-<>> ["\nINSERT INTO test (open_time)\nVALUES (?)\n" #inst"2022-10-30T00:00:00.000-00:00"]
          (next.jdbc/execute! connection)))

here is everything

https://clojurians.slack.com/archives/C053AK3F9/p1634660795316700?thread_ts=1634660795.316700&cid=C053AK3F9 is a thread about why those literals are gross

I am reuiring this library ito my namespace

[tick.core :as t]

Yeah, it is incidental to the SQL stuff, but the linked thread is all about it

If you search "in:#sql hiredman date" there are a few exchanges about this kind of date stuff

https://clojurians.slack.com/archives/C03RZGPG3/p1662743981383049?thread_ts=1662743981.383049&cid=C03RZGPG3 is maybe a good compact one (from #off-topic)

i have read your last posted link. And there you say that the best way is to set your db to UTC. Mine alaredy is... I dont understand what you were trying to point me at?

Out of curiosity. Doesnt sql server stores your datetime based on the type you defined the column. If the column is timestamp without time zone, should not the server just insert the date/string as is? So it is the client job to convert the datetime into the right timzeone before sending it to the db server. In this case I pass an inst/java.util.Date(doesnt contain timezone is like UTC) to jdbc and then it is the jdbc and postgres drivers job to format the date correctly so that the database just stores it since it is timestamp without time zone?🤔 Will resume tomorrow to tired for this right now.

It depends on what the jdbc driver for your database does

A string is usually not what is sent over the wire

The jdbc driver is responsible for taking the java type and transforming it into bytes that go over the wire

And when given a datetime with a timezone to store in a column without a timezone they have a surprisingly number of options to making that happen

And then that same process happens in reverse when the date comes back from the server

And to be clear, next.jdbc does no translation - it just wraps the jdbc driver.

So you have to learn to work with the specific database driver.

Jdbc is a common low level java sql database thing, database vendors typically write the drivers for it

next.jdbc wraps it in some clojure nice to haves

But the low level type coercion stuff, how things get serialized, wire formats, etc is all jdbc drivers

If you read my threads above then you know that just because a date in clojure prints as a utc date, that doesn't mean the underlying date object is in utc

The default #inst printer transforms all dates into utc as part of the printing process, but that doesn't change the object

The is no date object with no timezone on the jvm

utc is not the same as no timezone

Often jdbc drivers, when reading dates back from the database, if they don't come back with a timezone, will create the date objects using whatever the default timezone of the jvm process is

One more thing, I notice you're using timestamp as the datatype for the field. In general it is recommended to use timestampz instead. It goes into some detail here

@hiredman I consider calling time-literals library gross as snark bc you haven't addressed the reason it exists. when using edn, #inst is the right tag to use for a point on the timeline - but as the readme of that lib and https://widdindustries.com/blog/what-is-inst.html explains that is not mutually exclusive with round tripping. if you had looked at either, you might at least say something like round-tripping dates at the repl is not something I have ever found useful although I accept a lot of people do

Hello. Thanks for all your replays. You probably alredy answered me but I still cant wrap my head around this thing. And now I get that that postgres drivers and me not being able to use it correclty is the cause of all this mess. But still I would appreciate your help/ideas. Today I discovered that postgres timestampz does indeed stores dates differently. Dunno what I was doing yesterday. Here is my new attempt at trying to understand what is going on. I made a new project with only these 3 dependencies. [org.clojure/clojure "1.11.1"] [org.postgresql/postgresql "42.6.0"] [com.github.seancorfield/next.jdbc "1.3.883"] I have made a new clean database with only one table in it to test these dates. create table test ( inst timestamp, instz timestamp with time zone, instant timestamp, instantz timestamp with time zone, date_time timestamp, date_timez timestamp with time zone ); here is my namespace (ns test.core (:require [next.jdbc :as jdbc] [next.jdbc.date-time :as dt];;automagic = enables datetime types )) My local mashcine is set to timezone GMT+2 and my time currently is 2023-10-04 20:32:49.243000 here is my date insertion code (with-open [connection (jdbc/get-connection (jdbc/get-datasource datasource))] (jdbc/execute! connection ["INSERT INTO test (inst, instant, date_time, instz, instantz, date_timez) VALUES (?, ?, ?, ?, ?, ?)" (java.util.Date.) (java.time.Instant/now) (java.time.LocalDateTime/now) (java.util.Date.) (java.time.Instant/now) (java.time.LocalDateTime/now) ])) this is the output I see in my db client app inst = 2023-10-04 20:32:49.243000 instant = 2023-10-04 20:32:49.243000 date_time = 2023-10-04 20:32:49.250000 instz = 2023-10-04 18:32:49.252000 +00:00 instantz = 2023-10-04 18:32:49.252000 +00:00 date_timez = 2023-10-04 18:32:49.252000 +00:00 An observation. Why does postgres timestamp doesnt differentiate between Instant and LocalDateTime. One should be UTC and the other one should be local=GMT+2. Same for timestampz, it too does not differentiate. after executing this statement: (with-open [connection (jdbc/get-connection (jdbc/get-datasource datasource))] (jdbc/execute! connection ["select * from test"])) this is the repl output inside itellij [#:test{:inst #inst"2023-10-04T18:32:49.243000000-00:00", :instz #inst"2023-10-04T18:32:49.252000000-00:00", :instant #inst"2023-10-04T18:32:49.243000000-00:00", :instantz #inst"2023-10-04T18:32:49.252000000-00:00", :date_time #inst"2023-10-04T18:32:49.250000000-00:00", :date_timez #inst"2023-10-04T18:32:49.252000000-00:00"}] An observation. Here all the datetimes are the same!, compared to the output of my db client. Different timezones? Every mentioned program runs on the same machine. I havent messed with any configuration about the timezone. So defaults should make sense? here with current_timestamp which is a postgres builtin function I see that database is configured as utc. These means also that jdbc client uses utc? (with-open [connection (jdbc/get-connection (jdbc/get-datasource datasource))] (jdbc/execute! connection ["select current_timestamp, now()"])) [{:current_timestamp #inst"2023-10-04T18:37:32.237635000-00:00", :now #inst"2023-10-04T18:37:32.237635000-00:00"}] I really cant figure this out. Basically what I would like to achive is if I send (java.time.LocalDateTime/now) to jdbc/execute => I should have 2023-10-04 20:32:49.243000 stored in my db. And if I send (java.util.Date.) or (java.time.Instant/now) => I would like to have the utc date time in my db.

As I mentioned already, the default behavior in clojure when printing things as #insts is to print them as utc, regardless of what timezone the date object has

SQL types and JDBC (Java) types are different so there's a layer of conversion happening there already (and even different versions of the "same" JDBC driver may do different conversions). As you've seen, without requiring next.jdbc.date-time, some Java types are not auto-converted by (some versions of) the PG driver. In addition, the default Java type used for SQL timestamp is typically java.sql.Timestamp which is a subclass (and wrapper) of java.util.Date so there's also inherently some coercion going on there when dealing with it. The next.jdbc.date-time namespace has three read-as-* functions that let you globally change how java.sql.Timestamp (and java.sql.Date) are returned to the application code. Also, as mentioned earlier, some of this pain goes away if you have the JVM and the database both configured for the same timezone. Each can be configured independently and they each can be different to the host machine's timezone. Different (versions of) JDBC drivers may or may not negotiate the assumed timezone for a connection based on the server (database) timezone or the client (JVM) timezone, which can also complicate things if you don't have your JVM and your database both configured to use the same timezone. A lot of people will recommend setting everything to UTC but there are also articles out there which talk about edge cases where that can still be problematic.

thanks. I will try to change my jvm timezone. Tried putting

:jvm-opts ["-Duser.timezone=UTC"]

no success in what?

just because things print the same doesn't mean they are the same

do you know how can I check my current timezone inside the repl?

I think if I would successfuly changed the jvm timezone to UTC

(java.time.LocalDateTime/now)

(~/clojure)-(!2017)-> clj # default: I'm on Pacific time
Clojure 1.12.0-alpha4
user=> (java.time.LocalDateTime/now)
#object[java.time.LocalDateTime 0x38d5b107 "2023-10-04T13:17:20.467147439"]
user=>

Wed Oct 04 13:17:27
(~/clojure)-(!2017)-> clj -J-Duser.timezone=UTC
Clojure 1.12.0-alpha4
user=> (java.time.LocalDateTime/now)
#object[java.time.LocalDateTime 0x1ad777f "2023-10-04T20:17:39.092031107"]
user=>

(I can't speak to setting JVM options with Leiningen -- I haven't used lein for about eight years now)

% TZ=Europe/Madrid clj
Clojure 1.11.1
user=> (def d (java.util.Date.))
#'user/d
user=> d
#inst "2023-10-04T20:28:21.190-00:00"
user=> (str d)
"Wed Oct 04 22:28:21 CEST 2023"
user=>

Correct me if I am wrong, #inst/Date is date without timezone(you can interpret it as UTC). But when its .toString gets called it gets printed out in local time.

like browser does with js date object

#inst and Date are not synonymous

(type #inst"2023-10-04T20:34:34.208-00:00") => java.util.Date

#inst is a serialization format for dates in general, and you can hook the reader to read #insts as any type you choose

Date is just the type the #inst reader defaults to

but other types that include time zone data can also get printed as #insts

hmmm... this is getting even more complicated 🙂

let me explain you how I see it in my head and than you can correct me where I am wrong

I was thinking that when you create (java.util.Date.) internally it gets stored as gmt+0(londonTime + 0 offset) datetime. => this when printed is printed in local time which means + 2 in my case. But if I would debug this date I would see + 0 offset. This is ok since Date is know to have no timezone and to represent utc datetime? When you create (java.time.LocalDateTime/now) internally it gets stored as gmt+2(in my case), which means london time + 2 offset => gets printed as-is and if I would debug its internal state I would see + 2 offset basically they both get printed the "same" but internally are stored one gmt+0 and the other one gmt+2 What is confusing to me is that even though they are stored as completely different values, once they are sent over to jdbc/execute! they are stored as the same value inside the database. I was thinking that when postgres driver sends this two serialized dates as bytes each one set of bytes would represent a diferend value.

I have managed to set my jvm timezone to utc. let see what happens

yeah, when a java.util.Date is sent, and the field requires a timezone, the postgres jdbc driver has to get one from somewhere, it selects the timezone for the jvm, and if that timezone is not utc, the date will get adjusted to be the same moment in time as the Date object, but represented as a the time in the jvm's timezone

this is universally known to set jvm to utc? I know that storing utc is almost a must, but never heard of setting the backend to utc. We at work use localtimezone on the backend and utc isnide the db with no issue.

it is generally considered best practice to use utc everywhere (this is not without its issues, etc). I am not sure exactly what set of conversions the postgresql jdbc driver does, but it might just work out if all the clients are in the same tz

with postgres right now with jvn set to utc there is no issue since there is no difference between localDateTIme and UTCDateTime 🙂 . This is a cheat code

everything gets stored the same the right wat 2023-10-04 21:09:46.092000

now I just have to think about what will be more confusing because of this

https://github.com/clojure/java.jdbc/blob/master/src/main/clojure/clojure/java/jdbc.clj#L1245-L1262 no reify there?

Ah yeah got that confused. reducible-result-set* returns the reified IReduce.

It’s complex and written for performance, not for reading, which makes sense. I’m just hoping to understand how it works and why one might use it because I suspect that the code base at my work is using it incorrectly.

the results from jdbc comback as a ResultSet, which is basically an interator

you do something like while not empty(results): f(next(results))

clojure.java.jdbc packages that up in a few different ways, like turning it into a lazy seq or a vector

in the reducible-query case it packages it up as a custom "reducible collection"

https://ce2144dc-f7c9-4f54-8fb6-7321a4c318db.s3.amazonaws.com/reducers.html#sec-2-2 is pretty dated these days, but might be of interest

Cool thank you

If I’m calling transduce on the results, does it matter much which collection the query results are in?

maybe not really, it is complicated and depends on things like how eagerly the underlying jdbc driver fetches data

FWIW, c.j.j was my first stab at a "reducible query" and it hasn't had much real world usage. next.jdbc implemented it from the get-go and it gets a lot of real world usage so I'd point you at that code -- which I think is also a lot easier to understand: https://github.com/seancorfield/next-jdbc/blob/develop/src/next/jdbc/result_set.clj#L696

The main "benefit" of this is performance: it doesn't need to convert each row of the ResultSet object to a Clojure hash map -- you can access the columns of each row by name in the reducing fn and it fetches them directly from ResultSet. It also means you can process result sets that are very large because, again, it's not materializing a big vector full of hash maps for the result.

Interesting. Keywordize doesn’t convert the results to maps?

We treat the results as maps, maybe it’s just an implantation detail i didn’t put together

Are you asking about c.j.j or next.jdbc now?

Cjj. We haven’t converted yet

The columns come from the result set metadata -- not the result set itself.

But c.j.j makes things more complicated because it has a :raw? option which does even less work -- closer to what next.jdbc does by default.

raw-query-reducer is closer to what is in next.jdbc.

Yeah, I think c.j.j still makes hash maps out of rows... which was a mistake really...

In addition, c.j.j provided both IReduce and IReduceInit whereas next.jdbc only provides the latter (the init-less arity doesn't really make sense).

We have a function called transduce-batches that takes a query and a transducer and then calls something like (transduce xf (into []) (reducible-query … (format query))). It’s intended to be used with partition so the expensive operations run on the results happen in batches. However, the docstring (written by the original author who no longer works at the company) implies that the query itself is batched, and that the total results aren’t held in memory because of it.

Thanks for all the info, i think the docstring is wrong and has led to a lot of poor code that assumes the query itself is batched

You often need additional options on queries to get the DB to batch or stream them. next.jdbc's docs talk about this for some databases (and the settings are different for different databases... of course). Not sure if all those knobs and dials are accessible via c.j.j

If they are, I’m certain we’re not passing them along lol

If you're using reducible-query, I would highly recommend adding next.jdbc to your project, just to replace/upgrade that aspect instead of relying on the somewhat experimental code in c.j.j

It’s on our road map! Always something more pressing lol

If it's any consolation, we still have a lot of code using c.j.j at work, alongside a lot of code using next.jdbc 🙂

I discovered the source of my confusion: we are in fact passing in :fetch-size which calls .setFetchSize on the prepared statement. this converts the statement to "cursor mode" (https://jdbc.postgresql.org/documentation/query/#getting-results-based-on-a-cursor), which will transparently batch the query with a cursor inside of the result set

i wonder if there's any way to improve the c.j.j docs without copy-pasting them from the java.sql documentation.