Fork me on GitHub
#sql
<
2023-10-04
>
Andrew02:10:02

Hello. I need help. I am not able to save datetime as UTC into postgres database. The column in the database is defined as timestamp, clojure datetime value is of type java.time.Instant/java.util.Date(tried them both). When I execute the insert it gets inserted as local date time. I am using [org.postgresql/postgresql "42.6.0"] and [com.github.seancorfield/next.jdbc "1.3.883"] I have tried this with no success https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.894/api/next.jdbc.date-time before requiring [next.jdbc.date-time :as dt] I get Error printing return value (PSQLException) at org.postgresql.jdbc.PgPreparedStatement/setObject (PgPreparedStatement.java:1051). Can't infer the SQL type to use for an instance of java.util.Date. Use setObject() with an explicit Types value to specify the type to use. after requiring [next.jdbc.date-time :as dt], it does not thow anymore, but the datetime gets alway stored as GMT+2(my timezone). I am running postgres on my local machine SELECT current_setting('TIMEZONE'); return UTC App is running on the local machine (java.util.TimeZone/getDefault) returns Gmt+2 Any ideas how to solve this?

hiredman02:10:12

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

hiredman02:10:37

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

seancorfield02:10:41

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"]
So you need to be very clear about what types of date/time you are using as well...

Andrew02:10:04

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"
they all get stored as
2022-10-30 02:00:00.000000
I will try to change the database timezone

seancorfield02:10:52

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.

hiredman02:10:02

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

Andrew02:10:18

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

Andrew02:10:27

here is everything

Andrew03:10:34

I am reuiring this library ito my namespace

[tick.core :as t]
which uses this https://github.com/henryw374/time-literals

hiredman03:10:23

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

hiredman03:10:05

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

Andrew03:10:49

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?

Andrew03:10:04

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?:thinking_face: Will resume tomorrow to tired for this right now.

hiredman03:10:57

It depends on what the jdbc driver for your database does

hiredman03:10:13

A string is usually not what is sent over the wire

hiredman03:10:56

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

hiredman03:10:58

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

hiredman03:10:40

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

seancorfield03:10:45

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

seancorfield03:10:15

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

hiredman03:10:10

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

hiredman03:10:50

next.jdbc wraps it in some clojure nice to haves

hiredman03:10:28

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

1
hiredman03:10:33

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

hiredman03:10:43

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

hiredman03:10:23

The is no date object with no timezone on the jvm

hiredman03:10:36

utc is not the same as no timezone

hiredman03:10:49

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

dharrigan07:10:06

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

henryw37409:10:48

@U0NCTKEV8 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

Andrew18:10:26

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.

hiredman19:10:50

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

seancorfield19:10:52

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.

Andrew20:10:43

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

:jvm-opts ["-Duser.timezone=UTC"]
into project.clj with no success. I am using leiningen..

hiredman20:10:12

no success in what?

hiredman20:10:33

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

Andrew20:10:05

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

Andrew20:10:47

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

(java.time.LocalDateTime/now)
this would print the london time right?

seancorfield20:10:20

(~/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=>

seancorfield20:10:59

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

hiredman20:10:36

% 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=>

Andrew20:10:20

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.

Andrew20:10:32

like browser does with js date object

hiredman20:10:47

#inst and Date are not synonymous

Andrew20:10:23

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

hiredman20:10:31

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

hiredman20:10:11

Date is just the type the #inst reader defaults to

hiredman20:10:39

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

Andrew20:10:43

hmmm... this is getting even more complicated 🙂

Andrew20:10:09

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

Andrew20:10:52

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.

Andrew21:10:55

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

hiredman21:10:02

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

Andrew21:10:14

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.

hiredman21:10:41

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

Andrew21:10:53

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

Andrew21:10:49

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

Andrew21:10:47

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

Noah Bogart21:10:02

with clojure.java.jdbc, how does reducible-query work (and why would you use it)? I tried reading the source and I want to make sure I understand it. My read is that it returns a reified IReduce, deferring execution of the query until reduce (or transduce) is called. However, once it is called, it eagerly executes the query and then calls the reducing function which is... query-reducer? which also reifies IReduce and does the name normal transformation as query.

Noah Bogart21:10:42

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

Noah Bogart21:10:14

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.

hiredman21:10:30

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

👍 1
hiredman21:10:02

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

👍 1
hiredman21:10:49

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

hiredman21:10:13

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

Noah Bogart21:10:24

Cool thank you

Noah Bogart21:10:35

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

hiredman21:10:39

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

seancorfield21:10:32

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

seancorfield21:10:50

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.

Noah Bogart21:10:29

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

Noah Bogart21:10:50

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

seancorfield21:10:42

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

Noah Bogart21:10:27

Cjj. We haven’t converted yet

seancorfield21:10:22

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

👍 1
seancorfield21:10:21

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.

👍 1
seancorfield22:10:07

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

seancorfield22:10:04

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

👍 1
seancorfield22:10:14

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

👍 1
Noah Bogart22:10:09

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.

Noah Bogart22:10:40

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

seancorfield22:10:08

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

Noah Bogart22:10:32

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

seancorfield22:10:59

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

Noah Bogart22:10:40

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

seancorfield22:10:05

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 🙂

Noah Bogart13:10:05

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

Noah Bogart13:10:51

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