sqlitesqlite-json1

SQLite Epoch time query

Could use a bit of help on this. I have a table which stores records in JSON format in the acctinfo column. I can export the JSON content without issues, but the problem i'm running into is with the epoch time. I would like to be able to display my LastLoginTime in standard locatime format(Not convert the column, but rather convert the output to make it understandable). Any suggestion would be greatly appreciated.

SELECT name, 
       json_extract(table1.acctinfo, '$.LastloginTime'(1319017136629, 'unixepoch', 'localtime'))
from table1;

Here's an example of the JSON stored in the acctinfo column:

{
  "AcctCreateTime": 1518112456,
  "LastLoginTime": 1601055231,
  "LastModified": 1518112456,
}
Solution

  • Use the function datetime() and json_extract() like this:

    SELECT datetime(json_extract(acctinfo, '$.LastLoginTime'), 'unixepoch', 'localtime')
FROM table1;

    See a simplified demo.