CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Konstantin.Efimenko
Konstantin.Efimenko

Reputation: 1478

How to convert milliseconds to date in SQLite

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?

Upvotes: 28

Views: 41293

Answers (4)

DSMoslem
DSMoslem

Reputation: 11

select date(milliscolumn/1000,'unixepoch','localtime') from table1

this is better for localtime other than GTC.

But depend on Date And Time Functions I used '%F-%T' as formatter, why result is null?

Upvotes: 1

bsvtag
bsvtag

Reputation: 318

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

Upvotes: 22

CL.
CL.

Reputation: 180270

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

Upvotes: 69

Mit Bhatt
Mit Bhatt

Reputation: 1645

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

http://sqlfiddle.com/#!5/d41d8/223

Upvotes: 31

Related Questions