SQLite – How to Change Date Format Using SQL

The Unix epoch timestamp is defined as the number of seconds since the beginning of 1970 (in UTC).

These timestamps are measured from the beginning of 2001, and appear to use the respective local time zone:

> select datetime(strftime('%s', '2013-11-10') - 405720000, 'unixepoch');
2001-01-01 04:00:00
> select strftime('%s', '2013-11-10') - strftime('%s', '2001-01-01 04:00:00');
405720000

To correct for the random time zone differences, you should round to the nearest midnight.

Try this

select used_for_lookup.id,
        used_for_lookup.datetime,
        fid_table.fid_important_string,
        did_table.did_important_string
 from used_for_lookup 
 join fid_table on fid_table.id = used_for_lookup.fid
 join did_table on did_table.id = used_for_lookup.did
where datetime between '2014-10-09' and '2014-10-11';