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?

Do you mean a date as seen in UTC, or the date as seen in a particular time zone? — Basil Bourque, Oct 26 '19 at 18:24

score 66 · Accepted Answer · answered Jun 07 '13 at 06:10

66

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

answered Jun 07 '13 at 06:10

CL.

173,858
17
217
259

Please see the below answer by @bsvtag. That one includes `'localtime'` – Mikitz06 May 04 '17 at 12:38
@Mikitz06 These milliseconds timestamps are defined as being in UTC. Whether the result needs to be UTC or in some local time zone depends on the application. – CL. May 04 '17 at 13:32
you are the maannn!! – belphegor Dec 11 '17 at 12:45

score 28 · Answer 2 · answered Jun 07 '13 at 06:10

28

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

answered Jun 07 '13 at 06:10

Mit Bhatt

1,605
2
13
24

This query helped me a lot. Now I am able to get last 15 second records by comparing the date in table with last 15 seconds like this ```select distinct abc, time_in_millis as createdDate from your_table where datetime(time_in_millis/1000, 'unixepoch') >= time('now', 'localtime', '-15 seconds') order by time_in_millis desc;``` – Vadiraj Purohit Jan 31 '20 at 18:05

score 22 · Answer 3 · answered Jun 07 '13 at 06:10

22

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

answered Jun 07 '13 at 06:10

bsvtag

318
1
7

1

This should be the correct answer, because without the localtime parameter, the time is not correct (GMT is being taken care of). – Leeeeeeelo Mar 17 '15 at 13:42

How to convert milliseconds to date in SQLite

3 Answers3

Linked

Related