Converting a UNIXEPOCH timestamp stored in a database using SQLite3 in a bash script

Question

I have an sqlite3 query

SELECT DATETIME(ROUND(start_time / 1000), 'unixepoch') as Date FROM history

When I open the database in dbbrowser,( https://sqlitebrowser.org/ )the query runs fine.

I now try to run the command in a bash script.

echo 'SELECT DATETIME(ROUND(start_time / 1000), 'unixepoch') as Date FROM history' | sqlite3 database.db

The result is an error stating no such column unixepoch

I have also tried

echo 'SELECT DATETIME(ROUND(start_time / 1000), 'unixepoch') FROM history' | sqlite3 database.db

I've looked at the sqlite3 man page and can't see any usage for datetime.

Running

echo 'SELECT start_time FROM history' | sqlite3 database.db

will return the 13 digit unix epoch value ie:1586107737232

Answer 1

echo 'SELECT DATETIME(ROUND(start_time / 1000), 'unixepoch') as Date FROM history' | sqlite3 database.db
# ...^..........................................^.........^......................^

This is a quoting error: you can't embed single quotes in a single quoted string. The "inner" quotes around "unixepoch" are being dropped.

Do this

echo "SELECT DATETIME(ROUND(start_time / 1000), 'unixepoch') as Date FROM history" | sqlite3 database.db
# ...^..........................................^.........^......................^