Postgresql query within a bash script

Question

We are having an issue with a postgresql bash query. The output of the query from the bash script add's additional quotes around the date. Please can you help me fix it?!

Script:

#!/bin/sh

lastmonth=$(date -d "-1 month -$(($(date +%d)-1)) days" +%Y-%m-%d)
thismonth=$(date -d '-1 month -12 days' +%Y-%m-%d)

sudo -u postgres psql -d linetest_uptime -A -o /tmp/linetest_$(date +%Y-%m-%d).txt -c "select t.*, ip,port from line_test t,service_address s where '$thismonth' <= start and start < '$lastmonth' and service_address_id=s.id order by t.start,status desc" >/dev/null 2>&1 && sudo mv /tmp/linetest_$(date +%Y-%m-%d).txt /reporting/line_test_data/ && sudo chown $USER:$USER /reporting/line_test_data/linetest_$(date +%Y-%m-%d).txt

Output is:

sudo -u postgres psql -d linetest_watchdog -A -o /tmp/linetest_2014-03-13.txt -c 'select t.*, ip,port from line_test t,service_address s where '\''2014-02-01'\'' <= start and start < '\''2014-02-01'\'' and service_address_id=s.id order by t.start,status desc

We need to remove the '\' '\' which is now being wrapped around the date variable.

Answer 1

That doesn't look like output from your commands - is it some debug output instead?

What you have looks correct, in that it could be fed into the shell and give what you asked for. Note that 'aaa'\''bbb' is identical to "aaa'bbb"!

Side note: your variables look a bit overcomplicated, and you repeat $(date +%Y-%m-%d); the following may help:

lastmonth=$(date -d "-1 month" +%Y-%m-01)
thismonth=$(date +%Y-%m-01)
today=$(date +%Y-%m-%d)

and the SQL query may be more idiomatic as ... where date between '$lastmonth' and '$thismonth' ....