postgrehow将强制转换添加到SQL中的以下代码中

Question

请告诉我如何消除这个错误

这是我的代码：

%%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;

错误如下：

* 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)

请告诉我如何更改代码。提前感谢

Answer 1

尝试MAX(JULIANDAY(inspdate)) - MIN(JULIANDAY(inspdate)) < 30或MAX(JULIANDAY(inspdate)) - MIN(JULIANDAY(inspdate)) < INTERVAL '30 days'，甚至使用DATE_PART('day', end - start)

UPD：假设JULIANDAY()是您的自定义函数

Answer 2

发生此错误是因为postgresql中没有用户定义或内置的函数调用JULIANDAY。因此，用extract(julian from 'timestamp')替换JULIANDAY(timestamp)。然后它就会起作用。