how to add the casting to the following code in SQL-CodePudding

Please do tell me how to get rid of this error

Here is my code:

%%sql 
DROP TABLE IF EXISTS ages;
CREATE TABLE ages AS
SELECT CODEDESC, SUM(age) AS age FROM
(SELECT CODEDESC, (CASE WHEN  (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))< 30 THEN 30
             ELSE  (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))
        END)AS age FROM filtered_webextract
INNER JOIN cuisine
ON filtered_webextract.CUISINECODE = cuisine.CUISINECODE
GROUP BY CODEDESC, camis) as foo
GROUP BY CODEDESC;

Here is the error:

 * postgresql://docker:***@nycinspection.tditrain.com:5433/NYCinspection
Done.
(psycopg2.errors.UndefinedFunction) function julianday(timestamp without time zone) does not exist
LINE 3: (SELECT CODEDESC, (CASE WHEN  (JULIANDAY(MAX(inspdate))-JULI...
                                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[SQL: CREATE TABLE ages AS
SELECT CODEDESC, SUM(age) AS age FROM
(SELECT CODEDESC, (CASE WHEN  (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))< 30 THEN 30
             ELSE  (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))
        END)AS age FROM filtered_webextract
INNER JOIN cuisine
ON filtered_webextract.CUISINECODE = cuisine.CUISINECODE
GROUP BY CODEDESC, camis) as foo
GROUP BY CODEDESC;]
(Background on this error at: http://sqlalche.me/e/13/f405)

Please let me know how to change my code. Thanks in advance

CodePudding user response：

try MAX(JULIANDAY(inspdate)) - MIN(JULIANDAY(inspdate)) < 30 or MAX(JULIANDAY(inspdate)) - MIN(JULIANDAY(inspdate)) < INTERVAL '30 days', or even use DATE_PART('day', end - start)

UPD: assuming that the JULIANDAY() is your custom function

CodePudding user response：

This error occur because there is no user defined or inbuilt function calls JULIANDAY in postgresql. Therefore replace JULIANDAY(timestamp) with extract(julian from 'timestamp').Then it will work.