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?
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
Datetime expects epochtime, which is in number of seconds while you are passing in milliseconds. Convert to seconds & apply.
SELECT datetime(1346142933585/1000, 'unixepoch');
Can verify this from this fiddle
Do you need to avoid milliseconds to date conversion or function to convert milliseconds to date?
Since sqlite date functions work with seconds, then you can try to
- convert milliseconds in your query, like this
select date(milliscolumn/1000,'unixepoch','localtime') from table1
- convert millis to seconds before saving it to db, and then use date function in sql query