PostgreSQL -> SQLite: DATE_TRUNC Equivalent
sql, sqlite
Solution
SQLite has no data type for dates; it uses strings or numbers instead.
To remove the time portion of a timestamp, use the `start of day` modifier. The actual function to use (`datetime()`, `julianday()`, `strftime('%s')`) depends on the format of your date values:
WHERE
datetime(c.start_date, 'start of day') <= datetime(q.date, 'start of date') AND
datetime(c.end_date, 'start of day') >= datetime(q.date, 'start of date')
In this case, you could just use the `date()` function because you do not care about the actual format of the result, only how it compares:
WHERE
date(c.start_date) <= date(q.date) AND
date(c.end_date) >= date(q.date)
Problem
Friday afternoon and I'm fried. So fellow SQL aficionado's how to take the following WHERE clause in PostgreSQL and convert it to SQLite3 without using a compiled extension: ``` WHERE DATE_TRUNC('day', c.start_date) <= DATE_TRUNC('day', q.date) AND DATE_TRUNC('day', c.end_date) >= DATE_TRUNC('day', q.date) ``` going over the date/time functions in SQLite3 it seems like they're only for string formatting. Hope I'm seeing something wrong.