wheaties wheaties - 1 year ago 298
SQL Question

PostgreSQL -> SQLite: DATE_TRUNC Equivalent

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:

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 Source

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:

  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:

  date(c.start_date) <= date(q.date) AND
  date(c.end_date)   >= date(q.date)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download