How to convert timestamp to string in SQLite?

Question

I have table with timestamps in ms stored in it. I want to convert those timestamps in a human readable form.

Here is a sample output of my table:

SELECT date raw, strftime('%d-%m-%Y', (date/1000)) as_string
  FROM my_table

 +-----------------+--------------+
 |     raw         |  as_string   |
 +-----------------+--------------+
 |  1444687200000  |  06-47-3950  |
 +-----------------+--------------+
...               ...            ...
 +-----------------+--------------+

As you can see, the date as string is quite strange (06-47-3950).

How can I obtain 12-10-2015?

Answer 1

Try this:

SELECT date raw, strftime('%d-%m-%Y', datetime(date/1000, 'unixepoch')) as_string
  FROM my_table

You need to convert timestamp to date before.

Answer 2

You need to convert timestamp to daytime first. There was an answer on one forum. I quote it here.

Here you are: try those queries to see why and how.

select julianday('1899-12-30 00:00:00');

-- that gives 2415018.5 (remember Julian dates start at noon)

select datetime('40660.9454658044', '+2415018 days', '+12 hours', 'localtime');

-- gets you 2011-04-28 00:41:28 (depending on your local time)