android - How to convert milliseconds to date in SQLite

Question

I store date from Calendar.getTimeInMilliseconds() in SQLite DB. I need to mark first rows by every month in SELECT statement, so I need convert time in milliseconds into any date format using SQLite function only. How can I avoid this?

Answer 1

One of SQLite's supported date/time formats is Unix timestamps, i.e., seconds since 1970. To convert milliseconds to that, just divide by 1000.

Then use some date/time function to get the year and the month:

SELECT strftime('%Y-%m', MillisField / 1000, 'unixepoch') FROM MyTable