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  "/Users/zmackie/%%"
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   |
+--------------------------------------------------------------------------------------+-----------------------+-------------------------+------------------------------+---------------------+
| /Users/zmackie/workspaces/osquery-package/test.sql                                   | 2021-08-30 20:47:05   | 2021-08-30 20:47:05     | 2021-08-30 20:47:05          | 2021-08-26 15:47:26 |
| /Users/zmackie/Library/Preferences/com.apple.xpc.activity2.plist                     | 2021-08-30 20:34:03   | 2021-08-30 20:34:03     | 2021-08-30 20:34:03          | 2021-08-30 20:34:03 |
| /Users/zmackie/Library/Preferences/knowledge-agent.plist                             | 2021-08-30 20:31:15   | 2021-08-30 20:31:14     | 2021-08-30 20:31:14          | 2021-08-30 20:31:14 |
| /Users/zmackie/Library/Preferences/com.apple.finder.plist                            | 2021-08-30 20:05:30   | 2021-08-30 20:05:29     | 2021-08-30 20:05:29          | 2021-08-30 20:05:29 |
| /Users/zmackie/Downloads/osqery design - 8.25.21 (1).png                             | 2021-08-30 20:05:26   | 2021-08-30 20:05:12     | 2021-08-30 20:05:12          | 2021-08-30 20:05:11 |
| /Users/zmackie/Library/Preferences/com.apple.Enterprise-Connect.plist                | 2021-08-30 20:03:23   | 2021-08-30 20:03:23     | 2021-08-30 20:03:23          | 2021-08-30 20:03:23 |
| /Users/zmackie/Library/Preferences/ContextStoreAgent.plist                           | 2021-08-30 19:58:15   | 2021-08-30 19:58:14     | 2021-08-30 19:58:14          | 2021-08-30 19:58:14 |
| /Users/zmackie/Library/Preferences/com.apple.appstored.plist                         | 2021-08-30 19:58:15   | 2021-08-30 19:58:14     | 2021-08-30 19:58:14          | 2021-08-30 19:58:14 |
| /Users/zmackie/Library/Preferences/com.paloaltonetworks.GlobalProtect.settings.plist | 2021-08-30 19:53:03   | 2021-08-30 19:53:02     | 2021-08-30 19:53:02          | 2021-08-30 19:53:02 |
| /Users/zmackie/Library/Preferences/com.google.Keystone.Agent.plist                   | 2021-08-30 19:34:40   | 2021-08-30 19:34:39     | 2021-08-30 19:34:39          | 2021-08-30 19:34:39 |
| /Users/zmackie/Library/Preferences/com.apple.remindd.babysitter.plist                | 2021-08-30 19:33:05   | 2021-08-30 19:33:04     | 2021-08-30 19:33:04          | 2021-08-30 19:33:04 |
| /Users/zmackie/Library/Preferences/com.apple.parsecd.plist                           | 2021-08-30 19:32:35   | 2021-08-30 19:32:34     | 2021-08-30 19:32:34          | 2021-08-30 19:32:34 |
| /Users/zmackie/Desktop/Screen Shot 2021-08-30 at 3.31.29 PM.png                      | 2021-08-30 19:31:36   | 2021-08-30 19:31:34     | 2021-08-30 19:31:34          | 2021-08-30 19:31:34 |
| /Users/zmackie/Desktop/Screen Shot 2021-08-30 at 3.30.20 PM.png                      | 2021-08-30 19:30:26   | 2021-08-30 19:30:25     | 2021-08-30 19:30:25          | 2021-08-30 19:30:25 |
| /Users/zmackie/Desktop/Screen Shot 2021-08-30 at 3.29.14 PM.png                      | 2021-08-30 19:29:22   | 2021-08-30 19:29:20     | 2021-08-30 19:29:20          | 2021-08-30 19:29:20 |
| /Users/zmackie/Desktop/Screen Shot 2021-08-30 at 3.28.23 PM.png                      | 2021-08-30 19:28:29   | 2021-08-30 19:28:28     | 2021-08-30 19:28:28          | 2021-08-30 19:28:28 |
| /Users/zmackie/Library/Preferences/com.apple.HIToolbox.plist                         | 2021-08-30 19:22:45   | 2021-08-30 19:22:45     | 2021-08-30 19:22:45          | 2021-08-30 19:22:45 |
| /Users/zmackie/Library/Preferences/com.apple.dock.plist                              | 2021-08-30 18:30:43   | 2021-08-30 18:30:42     | 2021-08-30 18:30:42          | 2021-08-30 18:30:42 |
| /Users/zmackie/Library/Preferences/com.apple.spaces.plist                            | 2021-08-30 18:30:43   | 2021-08-30 18:30:42     | 2021-08-30 18:30:42          | 2021-08-30 18:30:42 |
| /Users/zmackie/Library/Preferences/us.zoom.xos.plist                                 | 2021-08-30 18:30:25   | 2021-08-30 18:30:24     | 2021-08-30 18:30:24          | 2021-08-30 18:30:24 |
+--------------------------------------------------------------------------------------+-----------------------+-------------------------+------------------------------+---------------------+