ERROR: invalid input syntax for type timestamp with time zone

Question

I have a PostgreSQL query as below which is running fine . I am calling it from a shell script as below

Result=$(psql -U username -d database -t -c 
$'SELECT round(sum(i.total), 2) AS "ROUND(sum(i.total), 2)" 
  FROM invoice i 
  WHERE i.create_datetime = '2019-03-01 00:00:00-06' 
  AND i.is_review = '1' AND i.user_id != 60;')

now I want the value which I have hard coded as i.create_datetime = '2019-03-01 00:00:00-06' to replace it with a variable date value.

I have tried two ways

way 1:

Result=$(psql -U username -d database -t -c 
$'WITH var(reviewMonth) as (values(\'$reviewMonth\')) 
SELECT round(sum(i.total),2) AS "ROUND(sum(i.total),2)" 
FROM var,invoice i 
WHERE i.create_datetime = var.reviewMonth::timestamp 
AND i.is_review = \'1\' AND i.user_id != 60;')

and

way 2:

Result=$(psql -U username -d database -t -c 
$'SELECT round(sum(i.total),2) AS "ROUND(sum(i.total),2)" 
FROM invoice i 
WHERE i.create_datetime = \'$reviewMonth\' 
AND i.is_review = \'1\' AND i.user_id != 60;')

But both way it's throwing error

way 1 throwing error as :

ERROR: operator does not exist: timestamp with time zone = text

way 2 throwing error as :

ERROR: invalid input syntax for type timestamp with time zone: "$reviewMonth"

Please suggest what should be my approach.

user_895640 · Answer

You should try using the psql variables. Here's an example:

# Put the query in a file, with the variable TSTAMP:

> echo "SELECT :'TSTAMP'::timestamp with time zone;" > query.sql
> export TSTAMP='2019-03-01 00:00:00-06'
> RESULT=$(psql -U postgres -t --variable=TSTAMP="$TSTAMP" -f query.sql )
> echo $RESULT
2019-03-01 06:00:00+00

Note how we format the string literal substitution in the query: :'TSTAMP'

You could also do the substitution yourself. Here's an example using a heredoc:

> export TSTAMP='2019-03-01 00:00:01-06'
> RESULT=$(psql -U postgres -t << EOF
SELECT '$TSTAMP'::timestamp with time zone;
EOF
)
> echo $RESULT
2019-03-01 06:00:01+00

In this case, we aren't using psql's variable substitution, so we have to quote the variable like '$TSTAMP' . Using a heredoc makes the quoting much simpler than using -c because you aren't trying to quote the whole command.

EDIT: more examples because it appears this wasn't clear enough. TSTAMP does not have to be hard coded, it's just a bash variable than can be set like any other bash variable.

> TSTAMP=$(date -d 'now' +'%Y-%m-01 00:00:00')
> RESULT=$(psql -U postgres -t << EOF
SELECT '$TSTAMP'::timestamp with time zone;
EOF
)
> echo $RESULT
2019-06-01 00:00:00+00

However, if you're really just looking for the start of the month, there's no need for shell variables at all

> RESULT=$(psql -U postgres -t << EOF
SELECT date_trunc('month', now());
EOF
)
> echo $RESULT
2019-06-01 00:00:00+00

ERROR: invalid input syntax for type timestamp with time zone

1 Answers