PostgreSQL interval invalidate

Question

 select date,
    to_char(shares, '99g999g999g999') as shares,
    to_char(trades, '99g999g999') as trades,
    to_char(dollars, 'L99g999g999g999') as dollars
    from facebook
    where date >= '2017-02-01'  + interval '1 months'
    order by date;

message: ERROR:  invalid input syntax for type interval: "2017-02-01"
LINE 6:  where date >= '2017-02-01'  + interval '1 months

I know there is one way to do it by declare a date variable. so the code would be

 date >= date: 'Variable' + interval '1 month'

But I don't know how to set a date variable in PGadmin.

@a_horse_with_no_name Would you please give me some links about how to declare variables in PGadmin. Thanks. — Mark
– Mark, Commented Sep 13, 2020 at 10:45
SQL (at least standard SQL) has no "variables". You would need to use PL/pgSQL if you want a variable. See here for some workarounds — user330315
– user330315, Commented Sep 13, 2020 at 10:46

user330315 · Accepted Answer · 2020-09-13 10:44:34Z

1

'2017-02-01' is treated as a string literal, not as a date literal. To make Postgres recognize it as a proper date value, use a proper date literal.

I prefer using ANSI date literals, which are strings prefixed with the keyword date

where date >= date '2017-02-01'  + interval '1 months'

Note that your choice of the column name date makes this quite confusing. The date on the left side refers to the column. The date prefixing the string literal refers to the data type name. To avoid the confusion, don't use reserved words (e.g. data types) as column names.

answered Sep 13, 2020 at 10:44

user330315

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

Collectives™ on Stack Overflow

PostgreSQL interval invalidate

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related