PostgreSQL -> SQLite: DATE_TRUNC Equivalent

Question

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.

Answer 1

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)

Answer 2

I couldn't make the above method work for hours or minutes.

For these, you can hack something up using the strftime functionality like so:

select datetime(strftime('%Y-%m-%dT%H:00:00', 'now'));