Query on date doesn't return what expected

New to Julia
1

I am using SQLite to perform queries on my Table “Todos”

I run on julia the commands:

julia> using SQLite
julia> db = SQLite.DB(“db/dev.sqlite3”)
julia> using DataFrames

From my “Todos” table I want to return values where date > = 2024-10-03

julia> df = DBInterface.execute(db, “SELECT id, category, date FROM todos WHERE date >= 2024-10-03 ORDER BY date” ) |> DataFrame

And I get the result:

Row │ id     category    date       
     │ Int64  String      Date       
─────┼───────────────────────────────
   1 │    25  family      2024-08-10
   2 │    72  other       2024-08-13
   3 │    24  other       2024-08-14
   4 │    47  work        2024-08-14
   5 │    35  learning    2024-08-17
   6 │    42  accounting  2024-08-17
   7 │    52  shopping    2024-08-17
   8 │     6  family      2024-08-18
   9 │     9  work        2024-08-18
  10 │    74  work        2024-08-19
  11 │    61  accounting  2024-08-21
  12 │    63  hobby       2024-08-21
  13 │    50  errands     2024-08-22
  14 │    39  accounting  2024-08-24
  15 │    20  shopping    2024-08-25
  16 │    31  accounting  2024-08-26
  17 │    66  accounting  2024-08-26
  18 │    33  hobby       2024-08-27
  19 │    60  hobby       2024-08-28
  20 │    12  work        2024-08-29
  ⋮  │   ⋮        ⋮           ⋮
  52 │     7  accounting  2024-10-05
  53 │    45  personal    2024-10-06
  54 │    58  work        2024-10-07
  55 │    32  family      2024-10-08
  56 │    64  work        2024-10-09
  57 │    11  shopping    2024-10-15
  58 │    21  hobby       2024-10-15
  59 │    67  work        2024-10-15
  60 │     8  other       2024-10-21
  61 │    51  hobby       2024-10-23
  62 │    57  other       2024-10-26

...

Why is not giving the correct results?
Do I need some kind of Casting? if yes, which one?

I have tried with the cast : (I actually need to get the values within a date range)

julia> DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE (cast(date as date) BETWEEN ‘2024-10-03’ AND ‘2024-10-27’) ORDER BY date " ) |> DataFrame

and I get 0 results, but I do have values!

0×3 DataFrame
 Row │ id      category  date    
     │ Int64?  String?   Missing 
─────┴───────────────────────────

Same case when I query with SearchLight:

julia> SearchLight.query("SELECT id, category, date  FROM todos WHERE date >= '2024-10-03'")
[ Info: SELECT id, category, date  FROM todos WHERE date >= '2024-10-03'
70×3 DataFrame
 Row │ id     category    date       
     │ Int64  String      Date       
─────┼───────────────────────────────
   1 │    25  family      2024-08-10
   2 │    72  other       2024-08-13
   3 │    24  other       2024-08-14
   4 │    47  work        2024-08-14
   5 │    35  learning    2024-08-17
   6 │    42  accounting  2024-08-17
   7 │    52  shopping    2024-08-17
   8 │     6  family      2024-08-18
   9 │     9  work        2024-08-18
  10 │    74  work        2024-08-19
  11 │    61  accounting  2024-08-21
  12 │    63  hobby       2024-08-21
  13 │    50  errands     2024-08-22
  14 │    39  accounting  2024-08-24
  15 │    20  shopping    2024-08-25
  16 │    31  accounting  2024-08-26
  17 │    66  accounting  2024-08-26
  18 │    33  hobby       2024-08-27
  19 │    60  hobby       2024-08-28
  20 │    12  work        2024-08-29
  ⋮  │   ⋮        ⋮           ⋮
  52 │     7  accounting  2024-10-05
  53 │    45  personal    2024-10-06
  54 │    58  work        2024-10-07
  55 │    32  family      2024-10-08
  56 │    64  work        2024-10-09
  57 │    11  shopping    2024-10-15
  58 │    21  hobby       2024-10-15
  59 │    67  work        2024-10-15
  60 │     8  other       2024-10-21

Any suggestions?
Thank you!

2

Try using 20241003 instead of 2024-10-03, as this may be the internal representation in SQLite and the comparison is based on this internal representation in the usual ASCII order (which explains the odd result because 0 >= -).

3

Hi @Dan I have tried, but I get the same result:

julia> df = DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE date >= '20241003' ORDER BY date" ) |> DataFrame
70×3 DataFrame
 Row │ id     category    date       
     │ Int64  String      Date       
─────┼───────────────────────────────
   1 │    25  family      2024-08-10
   2 │    72  other       2024-08-13
   3 │    24  other       2024-08-14
   4 │    47  work        2024-08-14
   5 │    35  learning    2024-08-17
   6 │    42  accounting  2024-08-17
   7 │    52  shopping    2024-08-17
   8 │     6  family      2024-08-18
   9 │     9  work        2024-08-18
  10 │    74  work        2024-08-19
  11 │    61  accounting  2024-08-21
  12 │    63  hobby       2024-08-21
  13 │    50  errands     2024-08-22
  14 │    39  accounting  2024-08-24
  15 │    20  shopping    2024-08-25
  16 │    31  accounting  2024-08-26
  17 │    66  accounting  2024-08-26
  18 │    33  hobby       2024-08-27
  19 │    60  hobby       2024-08-28
  20 │    12  work        2024-08-29
  ⋮  │   ⋮        ⋮           ⋮
  52 │     7  accounting  2024-10-05
  53 │    45  personal    2024-10-06
4

What is the output if you omit the conversion to a DataFrame?

5 
julia> DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE date >= 20241003 ORDER BY date" )
SQLite.Query{false}(SQLite.Stmt(SQLite.DB("db/dev.sqlite3"), Base.RefValue{Ptr{SQLite.C.sqlite3_stmt}}(Ptr{SQLite.C.sqlite3_stmt} @0x0000000170012410), Dict{Int64, Any}()), Base.RefValue{Int32}(100), [:id, :category, :date], Type[Union{Missing, Int64}, Union{Missing, String}, Union{Missing, Dates.Date}], Dict(:id => 1, :date => 3, :category => 2), Base.RefValue{Int64}(0))

julia> SearchLight.query("SELECT id, category, date  FROM todos WHERE date >= '20241003'")
[ Info: SELECT id, category, date  FROM todos WHERE date >= '20241003'
70×3 DataFrame
 Row │ id     category    date       
     │ Int64  String      Date       
─────┼───────────────────────────────
   1 │    25  family      2024-08-10
   2 │    72  other       2024-08-13
   3 │    24  other       2024-08-14
   4 │    47  work        2024-08-14
   5 │    35  learning    2024-08-17
   6 │    42  accounting  2024-08-17
   7 │    52  shopping    2024-08-17
   8 │     6  family      2024-08-18
   9 │     9  work        2024-08-18
  10 │    74  work        2024-08-19
  11 │    61  accounting  2024-08-21
  12 │    63  hobby       2024-08-21
  13 │    50  errands     2024-08-22
  14 │    39  accounting  2024-08-24
  15 │    20  shopping    2024-08-25
  16 │    31  accounting  2024-08-26

Omitting ’ ’ on ‘20241003’

julia> SearchLight.query("SELECT id, category, date  FROM todos WHERE date >= 20241003")
[ Info: SELECT id, category, date  FROM todos WHERE date >= 20241003
70×3 DataFrame
 Row │ id     category    date       
     │ Int64  String      Date       
─────┼───────────────────────────────
   1 │    25  family      2024-08-10
   2 │    72  other       2024-08-13
   3 │    24  other       2024-08-14
   4 │    47  work        2024-08-14
   5 │    35  learning    2024-08-17
   6 │    42  accounting  2024-08-17
   7 │    52  shopping    2024-08-17
   8 │     6  family      2024-08-18
   9 │     9  work        2024-08-18
  10 │    74  work        2024-08-19
  11 │    61  accounting  2024-08-21
  12 │    63  hobby       2024-08-21
6

This is most likely not working on the SQLite side (not the query is just a string on Julia side, parsed on the other side), and has nothing to do with Julia [packages]:

I think you’re subtracting 10 from 2024, then again subtracting 3 form it for the number 2011. This would be the value in any SQL database and in Julia.

SQLite isn’t too strict about types (some other might say “ERROR comparing dates to numbers”), so I think it just allows comparing dates against numbers, and interprets it as that year, and then from Jan 1st (it might seem “helpful” but only if absolutely that date ok for you since midnight, and you not doing calculations; unsure what it would do with e.g. 2011, then from the middle of the year?)?

You most likely need a date function like WHERE date >= date(“2024-10-03”) or maybe:

https://www.sqlite.org/lang_datefunc.html

julianday(‘1776-07-04’);

You most likely want to follow the SQL standard or what PostgreSQL does (I mostly use it, it usually closely follows the standard with some occasion extensions, always documented.

I can fully recommend PostgreSQL that I’m mostly familiar with. SQLite should also be good when used correctly. If it or something there is non-SQL compliant (or using any database, or their extensions, like I think julianday there). then it limits your options to change databases later.

When I use PostgreSQL I use its “REPL”, psql tool directly, and then you can rule out issues in rest of your setup. I’m not sure if SQLite has a similar thing, since it’s an embedded database, or from a GUI SQL tool. Julia’s REPL is in effect the tool you use, and then it’s harder to figure out such seeming beginner mistake/locate the root cause.

1 Like
7

Or WHERE date >= '2024-10-03' (with single quote).

1 Like
8

@Dan @Palli

actually to give you more details, I am following the book Web Development with Julia and Genie

and within the code:

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

function search(; completed = false, startdate = today() - Month(1), enddate = today(), group = ["date"], user_id)
  filters = SQLWhereEntity[
      SQLWhereExpression("completed = ?", completed),
      SQLWhereExpression("date >= ? AND date <= ?", startdate, enddate),
      SQLWhereExpression("user_id = ?", user_id)
  ]

I tested the function only sending the ‘startdate’

The function is called inside the DashboardController
DashboardController.jl line 23

and this is the output from the println(completed_todos)

[ Info: 2024-11-11 23:09:06 SELECT todos.id AS todos_id, todos.todo AS todos_todo, todos.completed AS todos_completed, todos.user_id AS todos_user_id, todos.category AS todos_category, todos.date AS todos_date, todos.duration AS todos_duration FROM "todos" WHERE completed = true AND date >= '2024-10-11' AND user_id = 4 ORDER BY todos.date ASC, todos.category ASC
17×7 DataFrame
 Row │ todos_id  todos_todo                         todos_completed  todos_user_id  todos_category  todos_date  todos_duration 
     │ Int64     String                             Int64            Int64          String          Dates.Date  Int64          
─────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 │       25  Architecto natus quam laudantium…                1              4  family          2024-08-10              13
   2 │       72  Assumenda distinctio consequatur.                1              4  other           2024-08-13             207
   3 │       52  Eius excepturi nulla deserunt eu…                1              4  shopping        2024-08-17             153
   4 │        9  Commodi nam enim.                                1              4  work            2024-08-18              58
   5 │       63  Veritatis molestias rerum fuga q…                1              4  hobby           2024-08-21             112
   6 │       50  Officiis ab ipsum quae.                          1              4  errands         2024-08-22             203
   7 │       33  Asperiores corrupti in ut nam.                   1              4  hobby           2024-08-27              46
   8 │       12  Earum est numquam eos et eos.                    1              4  work            2024-08-29             156
   9 │       40  Quaerat in voluptas.                             1              4  learning        2024-09-04              53
  10 │       55  Dolor alias.                                     1              4  accounting      2024-09-16             228
  11 │       59  Eos.                                             1              4  personal        2024-10-02              23
  12 │       14  Et minus odit aut.                               1              4  family          2024-10-03             216
  13 │       45  Non facilis ex temporibus quia.                  1              4  personal        2024-10-06             146
  14 │       32  Sit molestiae repellat qui nobis.                1              4  family          2024-10-08             222
  15 │       64  Fuga fuga ut.                                    1              4  work            2024-10-09              42
  16 │        8  Sed iusto consequatur dolore non…                1              4  other           2024-10-21             106
  17 │       22  Facilis hic qui sit aliquid faci…                1              4  learning        2024-10-27             159

yeah, maybe I should set up SQL or PostgresSQL, to my application and see how it behaves

I will update you as soon as I have it

9

I could try changing to: SQLWhereExpression("date >= '?' AND date <= '?'", startdate, enddate) or I think you want to change even further to SQLWhereExpression("date BETWEEN '?' AND '?'", startdate, enddate) (BETWEEN is SQL-conformant and supported in SQLite, includes both end-points be design…) and probably all optimizers will see it as equivalent, or if not might optimize better. SQL is case-insensitive, and upper case there is a style-issue, your call, some prefer always lower-case with fewer or no exceptions.

The code there seemingly is made for and thus should work for SQLite already, so did you change it? Thus a changed my PR I’m not longer sure about to draft:

Dates (and time, timezones and quotes) are a can of worms:

Seemingly the SQL-conforming literal is like date '2001-10-01' for e.g. math on dates to work:

date '2001-10-01' - date '2001-09-28'

I would like to know is such works in SQLite. In most or all databases skipping date would work for comparing e.g. as you were doing (I’ve never used this). Arithmetic on strings is of course not supported, so I guess this is the only standard-conforming way, but not needed when there’s no ambiguity.

Some standard functions do not work there:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXTRACT-datetime.html

I saw claimed for Oracle (but not even sure is correct, it was a comment on StackOverflow to a zero-rated answer that used it, seemingly incorrectly):

  • Always use TO_DATE on literals if comparing to a date.

It seems to me, is SQLite, you can insert e.g. in this format (or any garbage string):

  1. HH:MM

and since it’s only a string, it will not sort correctly if you mix with YYYY-MM-DD (10 bytes presumably). And since these are only strings, they will not be stored as compactly in SQLite as in other databases (there “date (no time of day)” is 4 bytes).

SQLite does not have a dedicated date/time datatype. Instead, date and time values can stored as any of the following:

ISO-8601 A text string that is one of the ISO 8601 date/time values shown in items 1 through 10 below. Example: ‘2025-05-29 14:16:00’
Julian day number The number of days including fractional days since -4713-11-24 12:00:00 Example: 2460825.09444444
Unix timestamp The number of seconds including fractional seconds since 1970-01-01 00:00:00 Example: 1748528160

Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren’t used in SQL, but that can vary from database to database.

You can rely on PostgreSQL docs regarding what the “SQL standard requires”:

PostgreSQL is more flexible in handling date/time input than the SQL standard requires. See Appendix B for the exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones.

Remember that any date or time literal input needs to be enclosed in single quotes, like text strings. Refer to Section 4.1.2.7 for more information. SQL requires the following syntax

[not shown, didn’t copy-paste correctly]

where p is an optional precision specification

I think it means requires with date (or other options) as I showed above.

Use one of the SQL functions instead in such contexts. For example, CURRENT_DATE + 1 is safer than 'tomorrow'::date.

Yes, I’m getting rusty. Also even if double quotes allowed, then you would need to escape.

In other context not allowed:

For instance, "Customers" or "Order ID". However, not all SQL databases require or allow double quotes for identifiers. For example, MySQL often uses backticks (`) instead of double quotes for this purpose.
…

Using Double Quotes for Identifiers

  • PostgreSQL / Standard SQL

…
Double quotes are used because PostgreSQL adheres closely to the SQL standard, which recommends double quotes for identifiers.
…
Always remember to check the documentation for the specific SQL database you are using, as these conventions can vary. For example, what works in PostgreSQL might not work exactly the same way in MySQL or Microsoft SQL Server.

The SQL standard differentiates timestamp without time zone and timestamp with time zone literals by the presence of a “+” or “-” symbol and time zone offset after the time. Hence, according to the standard,

TIMESTAMP ‘2004-10-19 10:23:54’

is a timestamp without time zone, while

TIMESTAMP ‘2004-10-19 10:23:54+02’

is a timestamp with time zone. PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type:

TIMESTAMP WITH TIME ZONE ‘2004-10-19 10:23:54+02’

PostgreSQL also provides functions that return the start time of the current statement, as well as the actual current time at the instant the function is called. The complete list of non-SQL-standard time functions is:

transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

1 Like
10 
SELECT id, category, date FROM todos WHERE date BETWEEN ‘2024-10-03’ AND ‘2024-10-27’ ORDER BY date;

should work.

SQLite does not have a date type but can store dates as TEXT (strings). The yyyy-mm-dd format sorts correctly, therefore the above SQL works provided that the dates are stored in this format.

Because 2024-10-03 is an algebraic expression,

sqlite> SELECT 2024-10-03;
2011

julia> df = DBInterface.execute(db, “SELECT id, category, date FROM todos WHERE date >= 2024-10-03 ORDER BY date” ) |> DataFrame

does not work.

Because date is not a type in SQLite, a default cast to INTEGER is done in

sqlite> select cast('2024-10-05' as date);
2024

and the cast in

will not give the expected result.

11

@Palli give me some time to check it out and read your comments
Thank you, I will update you

12

I don’t think that would be the option , I get the error:

Bildschirmfoto 2024-11-12 um 18.52.07
Bildschirmfoto 2024-11-12 um 18.52.071410×1486 285 KB

1 Like
13

yes, is doing an arithmetic operation, what would be then the option to retrieve specific date values with SQLite ?

14

@Palli
So far, no updates, the date is treated as an algebraic expression as @stephancb mentioned:

Because 2024-10-03 is an algebraic expression,

sqlite> SELECT 2024-10-03;
2011

So far, I don’t know yet how to handle it, even in the SQLWhereExpression … I think is also doing an arithmetic operation

15

have you tried running the same query with duckdb? duckdb will load/handle the sqlite db no problem

16

Did you try

julia> DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE date BETWEEN ‘2024-10-03’ AND ‘2024-10-27’ ORDER BY date " ) |> DataFrame

i.e. without the incorrect CAST and the dates as strings within single quotes '?

17

yes

julia> DBInterface.execute(db, "SELECT * FROM todos WHERE date >= '2024-10-03'") |> DataFrame
70×7 DataFrame
 Row │ id     todo                               completed  user_id  category    date        duration 
     │ Int64  String                             Int64      Int64    String      Date        Int64    
─────┼────────────────────────────────────────────────────────────────────────────────────────────────
   1 │    25  Architecto natus quam laudantium…          1        4  family      2024-08-10        13
   2 │    72  Assumenda distinctio consequatur.          1        4  other       2024-08-13       207
   3 │    24  Voluptatibus.                              0        4  other       2024-08-14       206
   4 │    47  Facilis a sint aliquam at maxime.          0        3  work        2024-08-14        67
   5 │    35  Cupiditate dolorem odio id aut.            0        4  learning    2024-08-17       179
   6 │    42  Vitae eligendi commodi aut omnis.          0        3  accounting  2024-08-17        98
   7 │    52  Eius excepturi nulla deserunt eu…          1        4  shopping    2024-08-17       153
   8 │     6  Voluptas eius laboriosam suscipi…          0        4  family      2024-08-18       183
   9 │     9  Commodi nam enim.                          1        4  work        2024-08-18        58
  10 │    74  Dignissimos dolores molestiae no…          0        4  work        2024-08-19        23
  11 │    61  Cumque sint.                               1        3  accounting  2024-08-21       191
  12 │    63  Veritatis molestias rerum fuga q…          1        4  hobby       2024-08-21       112
  13 │    50  Officiis ab ipsum quae.                    1        4  errands     2024-08-22       203
  14 │    39  Magnam.                                    1        3  accounting  2024-08-24       230
  15 │    20  Et veniam.                                 1        3  shopping    2024-08-25        42
  16 │    31  Est sint autem voluptatem aut.             0        4  accounting  2024-08-26       145
  17 │    66  Quaerat qui earum est voluptatum…          0        4  accounting  2024-08-26       207
  18 │    33  Asperiores corrupti in ut nam.             1        4  hobby       2024-08-27        46
  19 │    60  Esse quos aspernatur est.                  1        3  hobby       2024-08-28        39
  ⋮  │   ⋮                    ⋮                      ⋮         ⋮         ⋮           ⋮          ⋮
  53 │    45  Non facilis ex temporibus quia.            1        4  personal    2024-10-06       146
  54 │    58  Libero hic itaque optio.                   0        4  work        2024-10-07        36
  55 │    32  Sit molestiae repellat qui nobis.          1        4  family      2024-10-08       222
  56 │    64  Fuga fuga ut.                              1        4  work        2024-10-09        42
  57 │    11  Aut officiis qui quaerat.                  0        4  shopping    2024-10-15       174
  58 │    21  Et eum saepe voluptas ad.                  0        4  hobby       2024-10-15       210
  59 │    67  Occaecati.                                 0        3  work        2024-10-15       180
  60 │     8  Sed iusto consequatur dolore non…          1        4  other       2024-10-21       106
  61 │    51  Eaque hic similique.                       1        3  hobby       2024-10-23        46
  62 │    57  Deserunt minima.                           1        3  other       2024-10-26       182
  63 │    19  Aliquid eaque iusto quidem.                0        3  other       2024-10-27        47
  64 │    22  Facilis hic qui sit aliquid faci…          1        4  learning    2024-10-27       159
  65 │    23  Vitae eveniet similique aut dolo…          0        3  personal    2024-10-27       126
  66 │    38  Quaerat.                                   0        4  accounting  2024-10-31       233
  67 │    10  Ut possimus eum quae est omnis.            1        3  shopping    2024-11-02       208
  68 │    17  Eos eum quos illum voluptatem.             0        3  errands     2024-11-02       199
  69 │    69  Quibusdam velit mollitia.                  0        4  learning    2024-11-02        38
  70 │    65  Voluptas beatae qui atque dolore…          0        4  shopping    2024-11-06        82
                                                                                       33 rows omitted

julia> DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE date BETWEEN '2024-10-03' AND '2024-10-27' ORDER BY date " ) |> DataFrame
0×3 DataFrame
 Row │ id      category  date    
     │ Int64?  String?   Missing 
─────┴───────────────────────────

julia>
18

but how to do it inside the app , is using SearchLight for example Todos.jl

and in line 35

SQLWhereExpression(“date >= ? AND date <= ?”, startdate, enddate),

but it returns 0 rows, and I do have values

19

is it possible for you to share the data so i can play around with it?

20

Try it without the dashes:

DBInterface.execute(db, "SELECT * FROM todos WHERE date >= '20241003'") |> DataFrame

The logic is that in DataFrame form the Dates can be displayed differently to how they are stored in SQLite (which has Text but no Date type). Keep the single quotes. Same for the other query:

DBInterface.execute(db, "SELECT id, category, date FROM todos WHERE date BETWEEN '20241003' AND '20241027' ORDER BY date " ) |> DataFrame