https://github.com/osquery/osquery logo
#general
Title
# general
f

fritz

10/23/2020, 4:04 PM
Copy code
SELECT datetime(unix_time, 'unixepoch', 'localtime') FROM time;
❤️ 3
e

Esteban

10/23/2020, 4:08 PM
Thank you! It's from the SQL documentation? I couldn't understand the syntax
f

fritz

10/23/2020, 4:10 PM
I don't remember where I found that syntax referenced but it is essentially:
Copy code
SELECT datetime(myColumn, 'InputTimeFormat', 'OutputTimeModifier') FROM table;
e

Esteban

10/23/2020, 4:11 PM
Cool! OutputTimeFormat could be any time zone?
f
See section 3. Modifiers
1. NNN days 2. NNN hours 3. NNN minutes 4. NNN.NNNN seconds 5. NNN months 6. NNN years 7. start of month 8. start of year 9. start of day 10. weekday N 11. unixepoch 12. localtime 13. utc
Copy code
datetime(...)	==	strftime('%Y-%m-%d %H:%M:%S', ...)
e

Esteban

10/23/2020, 4:17 PM
Thank you! 🙂
f

fritz

10/23/2020, 5:30 PM
For comparisons also remember there is:
Copy code
SELECT 'true' AS within_last_30_days WHERE myColumn < datetime('now', '-1 month');
s

seph

10/23/2020, 6:46 PM
the
datetime
function is pretty overloaded. that list of modifiers comprises date addition, truncation, and some very limited parsing
5 Views