Sqllite - Month and Year from unixepoch column

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:

SELECT cast(strftime('%m', datetime(start, 'unixepoch')) as integer) as month, cast(strftime('%Y', datetime(start, 'unixepoch')) as integer) as year, * FROM track where month=4

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 track

the 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.

Official sqlite date and time functions page