DATE_PART and Postgresql

Question

I have a problem when I subtract two date in a function DATE_PART in this query.

SELECT
    TO_CHAR(date_trunc('month',sql_activity_days.created_month),'YYYY-MM') AS "sql_activity_days.created_month",
    coalesce(SUM(
        CASE
            WHEN(date_part('day', (sql_activity_days.sale_date + 1) - sql_activity_days.start_date) < 122) 
             THEN sql_activity_days.cad_net_invoiced
            ELSE NULL
        END
    ),0) AS "sql_activity_days.activity_over_122_day_after_signup"
FROM
    camel.f_subscription_touch  AS subscription_touch
    LEFT JOIN sql_activity_days ON subscription_touch.id  = sql_activity_days.customer_id 
    group by date_trunc('month',sql_activity_days.created_month) 
    order by 1 desc limit
500

The PostgreSQL database encountered an error while running this query.

ERROR: function date_part(unknown, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 1340

score 6 · Accepted Answer · answered Jul 10 '18 at 16:19

The second argument of the function date_part can be either a timestamp or an interval.

Your expression (sql_activity_days.sale_date + 1) - sql_activity_days.start_date subtracts two dates, whose resulting datatype is an integer, hence the error.

The solution to this would be to remove date_part and use the expression directly. The difference of two dates always gives the value in days.

DATE_PART and Postgresql

1 Answers1