Can anyone translate this mySQL query part to PostgresSQL?


I am pretty new to Postgres and I want to migrate an old project of mine to this new datbase.
Mostly I am fine but I have one mySQL query which I cannot “translate” to PostgresSQL without any help.
So maybe one of you is able to help?

This is the mySQL Query


@calc_days := CASE WHEN t.tool_purchasedate > $1 
                          THEN DATEDIFF($2, t.tool_purchasedate) + 1 
                          ELSE DATEDIFF($2, $1) + 1 
                     END AS calc_days,
@used_days := 
        (SELECT SUM(DATEDIFF(CASE WHEN order_enddate <= $2 
                                  THEN order_enddate 
                                  ELSE $2 
                             CASE WHEN order_startdate >= $1 
                                  THEN order_startdate 
                                  ELSE $1 
                             END) + 1)
         FROM ge_ordertool
         LEFT JOIN ge_order ON ordertool_order = order_id
         WHERE ordertool_tool = t.tool_id AND order_enddate >= $1 AND order_startdate <= $1
    0) AS used_days,
ROUND((@used_days * 100) / @calc_days) AS percentage
FROM ....

where $1 is the start date and $2 is the end date of the date interval I want to look at.

My problem is, that there seems to be no inline variable declare in Postgres. Otherwise I could solve the problem myself.
so how do you solve such problems in Postgres?

sorry, can’t help you with the user variable problem

what if you were to run a query that gave the desired calculations, but in the form of a query result?

it looks like your SELECT clause returns only three columns, calc_days, used_days, and percentage

wouldn’t a 1-row query result serve your purpose?

[tap, tap] is this thing on?

Sorry, yes i had some other things to do and could not come back to it earlier.

I have changed the process that way, that I do not calc the percentage in the query but in the frontend. So now I do not longer need the variables in the select and can just select them.

I will post the result tomorrow when I am back in the office

you can also use a CTE

     ( SELECT CASE... END AS calc_days
            , IFNULL(... ,0) AS used_days
        FROM ... )
SELECT calc_days
     , used_days
     , ROUND((used_days * 100.0) / calc_days) AS percentage
  FROM cte
1 Like

That’s a cool idea.