Title
#general
f

fritz

10/23/2020, 4:04 PM
SELECT datetime(unix_time, 'unixepoch', 'localtime') FROM time;
Esteban

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:
SELECT datetime(myColumn, 'InputTimeFormat', 'OutputTimeModifier') FROM table;
Esteban

Esteban

10/23/2020, 4:11 PM
Cool! OutputTimeFormat could be any time zone?
f
4:11 PM
See section 3. Modifiers
4:11 PM
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
4:12 PM
datetime(...)	==	strftime('%Y-%m-%d %H:%M:%S', ...)
Esteban

Esteban

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

fritz

10/23/2020, 5:30 PM
For comparisons also remember there is:
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