Search code examples
sqlpostgresqlpostgresql-10

How to replace a fixed date with dynamic date in WHERE clause?

select concat(CURRENT_DATE, ' ', '00:00:00+02:00')

returns "2020-07-14 00:00:00+02:00"

I can copy-paste this result into my WHERE clause:

where (xyz between timestamp with time zone '2020-06-01 00:00:00+02:00' 
and timestamp with time ZONE 2020-07-14 00:00:00+02:00

and it works, but if I use a more dynamic construct concat(CURRENT_DATE, ' ', '00:00:00+02:00') instead:

where (xyz between timestamp with time zone '2020-06-01 00:00:00+02:00' 
and timestamp with time ZONE concat(CURRENT_DATE, ' ', '00:00:00+02:00')

I get a syntax error:

ERROR: syntax error at or near "concat" LINE 57: ...6-01 00:00:00+02:00' and timestamp with time ZONE concat(CUR...

Why the first WHERE clause works, but not the other, even though it's printing the same date in same format?

Solution

  • You can use at time zone:

    where xyz between ('2020-06-01'::date at time zone '+02:00') and (current_date at time zone '+02:00')