xtdb 2023-04-25 | Slack Archive

sergey.shvets22:04:55

Hi, beginner question, but not able to find an answer in the docs. How do I store a date type in the database and search for it? I can save the java.sql.Date and it works for = search, but doesn't work for "<" (smaller) or ">" (greater) ?

refset22:04:19

Hey! If you want to be able to use range predicates then you'll need to coerce to java.util.Date before submitting. java.sql.Date can be stored but it is handled opaquely because it is not natively supported in the index

sergey.shvets23:04:45

Thx! I'll try java.util.Date!

sergey.shvets01:04:05

Doesn't seem to work, I have records with Date smaller then April 29, but when I try to search for them using query below, it returns empty set.

(q db
       '{:find  [(pull c [*])]
         :where [[c :listing/active? true]
                 [c :listing/added-on added]
                 [(< added (java.util.Date. 2023 4 29))]]
         })

sergey.shvets01:04:08

Also tried this, still empty set:

(q db
       '{:find  [(pull c [*])]
         :where [[c :listing/active? true]
                 [c :listing/added-on added]
                 [(= (.compareTo added (java.util.Date. 2023 4 29)) -1)]]
         })

sergey.shvets01:04:02

and order-by while returning something — the order of records is random.

Jacob O'Bryant01:04:38

I usually pass in the filter date via :in like so; not sure if constructing inside the query is supported:

(q db
   '{:find  [(pull c [*]) t]
     :in [t]
     :order-by [[t :desc]]
     :where [[c :listing/active? true]
             [c :listing/added-on added]
             [(< added t)]]}
   (java.util.Date. 2023 4 29))

(Also threw in an :order-by example--basically you have to put the sort date in a dedicated part of the :find clause)

sergey.shvets04:04:26

Thanks for the suggestion. I put a timestamp instead of date to solve my problem, but will try with Dates next time.

👌 2

2023-04-25

Channels