Query on date doesn't return what expected

New to Julia
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