Postgres : invalid input syntax for type timestamp with time zone: ""

Question

I am new to Postgres DB. I am getting the error:

invalid input syntax for type timestamp with time zone: ""

when trying to run the below query from java. ":from_date" and ":upto_date" will be replaced with null while executing in java. Any ideas on how to resolve this?

select tm.*
from team tm
 and tm.schedule_finish  >=  (case
                                 when :from_date is null or :upto_date is null  
                                       then TO_CHAR(to_date(concat(TO_CHAR(LOCALTIMESTAMP - INTERVAL '1 month','YYYY-MM') , '-01'),'YYYY-MM-DD'),'YYYY-MM-DD')
                                  when (date_part('year',age(:upto_date,:from_date))*12+date_part('month',age(:upto_date,:from_date)))>36 
                                    then TO_CHAR(to_date(concat(TO_CHAR(LOCALTIMESTAMP -INTERVAL '1 month','YYYY-MM') , '-01'),'YYYY-MM-DD'),'YYYY-MM-DD')
                                 else TO_CHAR(:from_date,'YYYY-MM-DD')
                               end)::timestamp

How exactly are you passing those values to your query? Please edit your question and add the Java code. — user330315
– user330315, Commented Jul 15, 2020 at 15:07
Thank you for your response. Those params are date fields which are passed through java code. These params are not mandatory, so if there is no selection made in the date fields then null will be passed to the query. Right now i dont have the java code with me. Sorry. — Sravan
– Sravan, Commented Jul 15, 2020 at 15:17
If you really pass instances of e.g. java.time.LocalDateTime then the error you have can't happen. What data type is schedule_finish I assume that is timestamp? — user330315
– user330315, Commented Jul 15, 2020 at 15:18
schedule_finish is a date field . Also after your suggestion i could narrow down the issue to this - TO_CHAR(:date_from - INTERVAL '1 month','YYYY-MM-DD'). in the param empty string is getting passed like this - ''. i am trying to convert it to date but no luck. I am getting an error as 'invalid input syntax for type interval: "" ' — Sravan
– Sravan, Commented Jul 16, 2020 at 9:06
If you want to convert a string to a date, use to_date() to to_char() — user330315
– user330315, Commented Jul 16, 2020 at 9:08

score 1 · Accepted Answer · 2020-07-16 09:44:56Z

It's a bit unclear to me what type those parameters have when passed to the query.

TO_CHAR(:from_date,'YYYY-MM-DD') suggests it's a timestamp or timestamptz value (e.g. passed as a java.time.LocalDate or java.time.OffsetTime)

However the error message seems to indicate that you are passing strings as parameters (which is a bit dangerous as in that case you rely on implicit data type conversion when calling the age() function).

Assuming you do pass strings, you can handle empty strings in the first WHEN part by converting them to NULL. That way the subsequent expressions don't have to deal with empty strings.

I would add an explicit conversion for the call of the age() function. You also don't need to call age() twice, you can compare the result directly with an interval constant age(...) > interval '36 month'. The back and forth between to_date() and to_char() just to get a timestamp value of the previous month's first day is also unnecessary.

And if all the expressions in the then part already return a timestamp it's no longer necessary to cast the whole CASE expression to a timestamp

(case
   when nullif(trim(:from_date),'') is null or nullif(trim(:upto_date), '') is null  
      then date_trunc('month', LOCALTIMESTAMP - INTERVAL '1 month')
    when age(to_date(:upto_date, 'yyyy-mm-dd'), to_date(:from_date, 'yyyy-mm-dd')) > interval '36 month'
      then date_trunc('month', LOCALTIMESTAMP - INTERVAL '1 month')
   else to_timestamp(:from_date,'YYYY-MM-DD')
end)

If you want to do any calculation with your "date strings", you have to convert them first:

to_date(:from_date, 'yyyy-mm-dd') - interval '1 month'

Collectives™ on Stack Overflow

Postgres : invalid input syntax for type timestamp with time zone: ""

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related