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

glenn jackman · Accepted Answer · 2020-04-18 15:19:15Z

4

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
# ...^..........................................^.........^......................^

answered Apr 18, 2020 at 15:19

glenn jackman

249k42 gold badges233 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SqLITE Over a year ago

Oh my god.... I never noticed I was doing that... Thank you. Also just realized I could have used: date -d '1970-01-01 UTC + 1586107737232 seconds with the returned value.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related