# Query on date doesn't return what expected

**URL:** <https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512>\
**Category:** New to Julia\
**Tags:** sqlite\
**Created:** [November 11, 2024, 7:25pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512 "2024-11-11T19:25:30Z")\
**Posts on this page:** 6\
**Page:** 2

<div class="post-metadata">

**Author:** ![stephancb](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stephancb/32/14243_2.png) [@stephancb](https://discourse.julialang.org/u/stephancb)\
**Post date:** [November 14, 2024, 5:52am UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/21 "2024-11-14T05:52:26Z")

</div>

> [@andreh](#):
>
> SQLWhereExpression(“date \>= ? AND date \<= ?”, startdate, enddate),

The expression has only one placeholder ‘?’, but two parameters ‘startdate’ and ‘enddate’, of which only ‘startdate’ will be used. So it should return rows with the start date if there are any. Try

```julia
SQLWhereExpression(“date >= ?1 AND date <= ?2”, startdate, enddate),

```

---

<div class="post-metadata">

**Author:** ![andreh](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@andreh](https://discourse.julialang.org/u/andreh)\
**Post date:** [November 16, 2024, 12:50am UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/22 "2024-11-16T00:50:44Z")

</div>

you need this model (Todo) [git](https://github.com/PacktPublishing/Web-Development-with-Julia-and-Genie/blob/main/Chapter8/TodoMVC/app/resources/todos/Todos.jl)

with the _function search_ (line 32) which is called from [DashController](https://github.com/PacktPublishing/Web-Development-with-Julia-and-Genie/blob/main/Chapter8/TodoMVC/app/resources/dashboard/DashboardController.jl)

and you can load this data (CSV) to the “Todo” model [output2.csv](https://github.com/amhg/parentModule)

Thank you!

---

<div class="post-metadata">

**Author:** ![drizk1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/drizk1/32/208422_2.png) [@drizk1](https://discourse.julialang.org/u/drizk1)\
**Post date:** [November 16, 2024, 3:55pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/23 "2024-11-16T15:55:18Z")

</div>

ok, so I just threw it in TidierDB since I was having some issues setting up the above method, but I was able to correctly filter by dates with sql.

```julia
DBInterface.execute(db, "select user_id, category, date from read_csv(
    'https://raw.githubusercontent.com/amhg/parentModule/refs/heads/main/output2.csv',
    ignore_errors=true) where date >= '2024-10-03' AND date <= '2024-10-27'") |>DataFrame 
#returns 13 rows shown below

```

Another option (with the code and sql output are below via duckdb) is to use `STRPTIME` to explicitly convert… `ymd` converts the sql to `STRPTIME('2024-10-03', '%Y-%m-%d')` which might be the solution to your output issue. The `STRPTIME` conversion is not necessary in TidierDB either, so i am not sure why the issue is arising for you. The CSV does appear to be a bit buggy to read as i had to use `ignore_errors=true`

```julia
using TidierDB
db = connect(duckdb())
data = db_table(db, "read_csv(
    'https://raw.githubusercontent.com/amhg/parentModule/refs/heads/main/output2.csv',
    ignore_errors=true
)")  

@chain t(data) begin
    @select(user_id, category, date)
    @filter(date >= ymd("2024-10-03"))
    @arrange(date)
    @aside @show_query _
    @collect
end

WITH cte_1 AS (
SELECT *
        FROM read_csv(
    'https://raw.githubusercontent.com/amhg/parentModule/refs/heads/main/output2.csv',
    delim=',',
    header=true,
    ignore_errors=true
)
        WHERE date >= STRPTIME('2024-10-03', '%Y-%m-%d'))  
SELECT user_id, category, date
        FROM cte_1  
        ORDER BY date ASC
20×3 DataFrame
 Row │ user_id category date       
     │ Int64 String Date       
─────┼─────────────────────────────────
   1 │ 3 personal 2024-10-05
   2 │ 3 hobby 2024-10-06
   3 │ 4 work 2024-10-06
   4 │ 4 shopping 2024-10-06
   5 │ 3 learning 2024-10-09
   6 │ 3 work 2024-10-10
   7 │ 3 learning 2024-10-16
   8 │ 3 work 2024-10-16
   9 │ 3 other 2024-10-17
  10 │ 4 accounting 2024-10-18
  11 │ 4 personal 2024-10-21
  12 │ 3 accounting 2024-10-21
  13 │ 3 family 2024-10-22
  14 │ 3 work 2024-10-28
  15 │ 3 other 2024-10-30
  16 │ 4 learning 2024-10-30
  17 │ 3 hobby 2024-10-31
  18 │ 3 errands 2024-10-31
  19 │ 3 personal 2024-11-02
  20 │ 3 shopping 2024-11-06

```

```julia
@chain t(data) begin
    @select(user_id, category, date)
    @filter(date >= ymd("2024-10-03") && date <= ymd("2024-10-27"))
    @arrange(date)
    @aside @show_query _
    @collect
end
13×3 DataFrame
 Row │ user_id category date       
     │ Int64 String Date       
─────┼─────────────────────────────────
   1 │ 3 personal 2024-10-05
   2 │ 3 hobby 2024-10-06
   3 │ 4 work 2024-10-06
   4 │ 4 shopping 2024-10-06
   5 │ 3 learning 2024-10-09
   6 │ 3 work 2024-10-10
   7 │ 3 learning 2024-10-16
   8 │ 3 work 2024-10-16
   9 │ 3 other 2024-10-17
  10 │ 4 accounting 2024-10-18
  11 │ 4 personal 2024-10-21
  12 │ 3 accounting 2024-10-21
  13 │ 3 family 2024-10-22

```

---

<div class="post-metadata">

**Author:** ![andreh](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@andreh](https://discourse.julialang.org/u/andreh)\
**Post date:** [November 16, 2024, 8:49pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/24 "2024-11-16T20:49:32Z")

</div>

Thank you! let me check it out  
Could you share your whole code in git?  
the project I am following it uses SearchLight do you also use it?

How do you connect to an existing DB with DuckDB?  
I looked at the documentation [https://duckdb.org/docs/api/julia.html](https://duckdb.org/docs/api/julia.html)

It shows only how to create a:

```julia
# create a new in-memory database
con = DBInterface.connect(DuckDB.DB, ":memory:")

```

---

<div class="post-metadata">

**Author:** ![drizk1](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/drizk1/32/208422_2.png) [@drizk1](https://discourse.julialang.org/u/drizk1)\
**Post date:** [November 16, 2024, 9:16pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/25 "2024-11-16T21:16:02Z")

</div>

The entirety of my code i used is copied above, but if you still need me to I can throw on my github if thats what u mean? i dont use git.

here is how you connect to an existing one stored locally.

```julia
db = DBInterface.connect(DuckDB.DB, "/path/to/you/database.db")
db2 = DBInterface.connect(DuckDB.DB, "/path/to/you/database.sqlite")

```

I have never used SearchLight before, no. Just briefly looked at their docs.

---

<div class="post-metadata">

**Author:** ![andreh](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@andreh](https://discourse.julialang.org/u/andreh)\
**Post date:** [November 16, 2024, 10:25pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512/26 "2024-11-16T22:25:20Z")

</div>

I see, ok !

may I ask you how do you specify the SQL Driver when executing the command:

```julia
DBInterface.execute(db, "select user_id, category, date from read_csv(
    'https://raw.githubusercontent.com/amhg/parentModule/refs/heads/main/output2.csv',
    ignore_errors=true) where date >= '2024-10-03' AND date <= '2024-10-27'") |>DataFrame

```

I have MySQL installed and I would like to try

[Previous page](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512.md?page=1)