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.
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)
start of week modifier so I came up with this: date(strftime('%s', my_date) - 86400 * ((strftime('%w', my_date) + 6) % 7), 'unixepoch'). The + 6) % 7 is there to make Monday the first day of the week.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'));
datedata type there can't be an equivalent.date(c.start_date)etc is what you're looking for?