Just wanted to share how to get month and year from sqllite column that keeps unixepoch numeric value. Without further ado, here is a sample:
Here is what results look like:
What about null values?
After nullifying end value, and running the following query:
SELECT cast(strftime('%m', datetime(end, 'unixepoch')) as integer) as month, cast(strftime('%Y', datetime(end, 'unixepoch')) as integer) as year, * FROM trackthe result is:
What about zeroes in the source column? Here is the result:
What is my take away from all that? From my design perspective I definitely don’t want to have some fields for dates that would be NOT NULL DEFAULT(0), I better have them nullable with null value representing a missing date, not a 0.