PostgreSQL interval error

Question

I am trying to run the following query. Not sure, what mistake I am doing:

select min(p.start_timestamp AT TIME ZONE p.timezone AT TIME ZONE 'America/Los_Angeles' ) as Date,
 'America/Los_Angeles' AS Timezone, sum(GREATEST(0, p.value)) as Value  
from main.production_m p 
where 
p.start_timestamp AT TIME ZONE p.timezone >= '2017-02-18' 
and p.start_timestamp AT TIME ZONE  p.timezone < '2017-02-22' + INTERVAL '1 day'

Getting the following error:

ERROR:  invalid input syntax for type interval: "2017-02-20"
LINE 5: ...and p.start_timestamp AT TIME ZONE  p.timezone <= '2017-02-2...

Appreciate your input. Thanks Karthey

'2017-02-22' + INTERVAL ('1 day') or '2017-02-22' + INTERVAL '1 day'::interval — wildplasser
– wildplasser, Commented Jun 26, 2017 at 20:26

Andrew Rueckert · Accepted Answer · 2017-06-26 20:26:38Z

1

PSQL thinks that your date should be an interval for some reason; you can fix this by casting:

p.start_timestamp AT TIME ZONE  p.timezone < '2017-02-22'::timestamptz + INTERVAL '1 day'

answered Jun 26, 2017 at 20:26

Andrew Rueckert

5,3163 gold badges42 silver badges48 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

"Some reason": the reason is point 2.a. of the PostgreSQL operator type resolution rules.

Collectives™ on Stack Overflow

PostgreSQL interval error

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