CODEWITHSUNDEEP
bashdatesqlite
user3447273
user3447273

Reputation: 361

How to add conditional date filter to SQLite export script

I am using a bash script to export data from the songs table in a Clementine db:

#!/bin/bash
/usr/bin/sqlite3 /home/username/.config/Clementine/clementine.db <<!
.headers off
.mode csv
select filename from songs where lastplayed > -1;
!

The date information shown in the column lastplayed is expressed in unix time (example:1479607204). Where a record has no date, the field's data is shown as -1.

Using the above script correctly displays all entries in the songs table that have any lastplayed date (lastplayed > -1).

If instead, I want the date filtered based on number of days elapsed (using today's date, so assume the NOW statement is used in some way), how would I modify the SELECT line above to calculate and filter output? I reviewed the SQLite manual page for date/time but as a newbie I could not solve how to use its syntax in the script.

Assume the filter lists only those records for which there is a date, and the unix date is at least 10 calendar days earlier than today.

Upvotes: 0

Views: 82

Answers (1)

CL.
CL.

Reputation: 180050

To do comutations with dates, add the appropriate modifiers to some date function. To convert a date into the Unix format, use strftime() with the %s format:

sqlite> SELECT strftime('%s', 'now', '-3 days');
1479410717

And to make the comparison work, you have to convert the resulting string into a number:

SELECT filename
FROM songs
WHERE lastplayed > CAST(strftime('%s', 'now', '-3 days') AS INTEGER);

Upvotes: 0

Related Questions