This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2023-10-04
Channels
- # announcements (6)
- # babashka (7)
- # beginners (2)
- # biff (5)
- # calva (2)
- # cherry (17)
- # cider (3)
- # clj-kondo (8)
- # clojure (202)
- # clojure-brasil (8)
- # clojure-europe (20)
- # clojure-norway (23)
- # clojure-uk (4)
- # clojuredesign-podcast (5)
- # conjure (1)
- # cursive (9)
- # eastwood (22)
- # events (8)
- # fulcro (3)
- # hyperfiddle (22)
- # introduce-yourself (7)
- # lsp (67)
- # malli (1)
- # matrix (1)
- # meander (6)
- # off-topic (76)
- # pedestal (8)
- # polylith (17)
- # quil (12)
- # re-frame (2)
- # reagent (8)
- # releases (3)
- # shadow-cljs (67)
- # sql (93)
- # squint (39)
- # tools-deps (46)
- # vim (7)
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?
You are not storing timezones and your jvm and database are not set to use the same timezone
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...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 timezoneNow 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)))
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]
which uses this https://github.com/henryw374/time-literalsIf 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?:thinking_face: Will resume tomorrow to tired for this right now.
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
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
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
@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
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"]
into project.clj with no success. I am using leiningen..I think if I would successfuly changed the jvm timezone to UTC
(java.time.LocalDateTime/now)
this would print the london time right?(~/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.
#inst is a serialization format for dates in general, and you can hook the reader to read #insts as any type you choose
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.
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
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
.
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.
clojure.java.jdbc packages that up in a few different ways, like turning it into a lazy seq or a vector
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
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.