CODEWITHSUNDEEP
sqlsqliteselectstrftime
beyonddc
beyonddc

Reputation: 1256

sqlite converting milliseconds to HH:MM:SS.SSS

I have a column in the table that stores milliseconds as an Integer type in sqlite. The millisecond timestamp is stores the time it took for certain event happen. For example, it took 36 milliseconds for Action 1 occurred, the value 36 will be stored into the table.

I want to construct a SQL select statement that will format the millisecond timestamp to HH:MM:SS.SSS format, and so far I couldn't get it working.

Here's what the SQL query that I constructed.

select strftime('%H:%M:%f', table_foo.time/1000, 'unixepoch') as time from table_foo

The query result returned 00:00:00.000 where I was expecting to see 00:00:00.036 when the timestamp is 36 ms.

It appears that it is not showing the reminder properly.

Can someone points me what I did wrong? or what is the appropriate way to do what I need?

Thanks in advance.

Upvotes: 0

Views: 903

Answers (1)

zealous
zealous

Reputation: 7503

try the following, replace int 1000 with decimal 1000.0.

select 
  strftime('%H:%M:%f', table_foo.time/1000.0, 'unixepoch') as time 
from table_foo

Upvotes: 3

Related Questions