Speaking of the file table, I’m trying to write a query that returns the 20 most recently modified files and its behaving unexpectedly..

SELECT 
    path,
    datetime(f.atime,'unixepoch') AS file_last_access_time,
    datetime(f.mtime,'unixepoch') AS file_last_modified_time,
    datetime(f.ctime,'unixepoch') AS file_last_status_change_time,
    datetime(f.btime,'unixepoch') AS file_created_time
FROM
    file f
WHERE
    path LIKE  "/%%"
AND
    file_created_time > date('now','-7 day')
ORDER BY
    file_last_modified_time DESC
LIMIT
    20
;

❯ cat test.sql| osqueryi
+------------------------------------+-----------------------+-------------------------+------------------------------+---------------------+
| path                               | file_last_access_time | file_last_modified_time | file_last_status_change_time | file_created_time   |
+------------------------------------+-----------------------+-------------------------+------------------------------+---------------------+
| /etc/resolv.conf                   | 2021-08-30 20:03:10   | 2021-08-30 20:03:09     | 2021-08-30 20:03:09          | 2021-08-30 20:03:09 |
| /tmp/com.google.Keystone/          | 2021-08-30 13:31:30   | 2021-08-30 13:31:30     | 2021-08-30 13:31:30          | 2021-08-25 15:35:46 |
| /tmp/com.apple.launchd.YGDOAvFSHA/ | 2021-08-25 15:14:04   | 2021-08-24 20:35:18     | 2021-08-24 20:35:18          | 2021-08-24 20:35:18 |
+------------------------------------+-----------------------+-------------------------+------------------------------+---------------------+

VS