Can anyone translate this mySQL query part to PostgresSQL?

Databases
#1

Hi,

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

SELECT 

@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 := 
    IFNULL(
        (SELECT SUM(DATEDIFF(CASE WHEN order_enddate <= $2 
                                  THEN order_enddate 
                                  ELSE $2 
                             END,
                             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?

#2

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?

#3

[tap, tap] is this thing on?

#4

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

#5

you can also use a CTE

WITH cte AS
     ( 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
#6

That’s a cool idea.