Filter using now() / period()

Hi All,

I’m trying to do this:

c3Grid(ChInRevproTdInspection.fetch({filter: “executionStartDateTime < now() - period(5, ‘YEAR’)”}))

but even doing this:

c3Grid(ChInRevproTdInspection.fetch({filter: “executionStartDateTime < now()”}))

or

c3Grid(ChInRevproTdInspection.fetch({filter: “executionStartDateTime < DateTime.now()”}))

I get this error:
main.js:3070 ChInRevproTdInspection.fetch: Unable to execute sql SELECT T.servicepointid, T.companyid, T.creationdatetime, T.issuingdatetime, T.assignmentdatetime, T.executionstartdatetime, T.executionenddatetime, T.inspectionstatus, T.resulttype, T.calculatedvaluekwh, T.propertystatus, T.inspectorcode, T.contractorcode, T.executioninspectorcode, T.executioncontractorcode, T.withpolice, T.observations, T.origin, T.comuna, T.economicactivitycode, T.tariff, T.inspectionid
FROM inspection T
WHERE (T.executionstartdatetime<timestamp ‘2019-07-31T14:05:24.000Z’)
ORDER BY T.inspectionid…

The query without the filter works.

executionstartdatetime is a datetime and I’m using Hive.

Best,
Mario

Hello Mario,

This behaviour is happening because your type ChInRevproTdInspection mixes ExternalType. As you can see, you are trying to compare a datetime of an external database and a timestamp.

You could think of converting your expression into string like this:

c3Grid(ChInRevproTdInspection.fetch({filter: "executionStartDateTime < string(now() - period(5, 'YEAR'))"}))

Hi Gabriel,

I tried your suggestion but I get this result:

ChInRevproTdInspection.fetch: Unable to execute sql SELECT T.servicepointid, T.companyid, T.creationdatetime, T.issuingdatetime, T.assignmentdatetime, T.executionstartdatetime, T.executionenddatetime, T.inspectionstatus, T.resulttype, T.calculatedvaluekwh, T.propertystatus, T.inspectorcode, T.contractorcode, T.executioninspectorcode, T.executioncontractorcode, T.withpolice, T.observations, T.origin, T.comuna, T.economicactivitycode, T.tariff, T.inspectionid
FROM inspection T
WHERE (T.executionstartdatetime<CAST(timestamp ‘2019-08-01T07:24:37.000Z’ - INTERVAL ‘5YEAR’ AS VARCHAR))
ORDER BY T.inspectionid
LIMIT 2001
AnalysisException: Syntax error in line 3:
…ecutionstartdatetime<CAST(timestamp '2019-08-01T07:24:…
^
Encountered: TIMESTAMP
Expected: CASE, CAST, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, TRUNCATE, TRUE, IDENTIFIER…

@mnslarcher This appears to be a bug. It seems the “now()” function is translated in a Postgres specific way. Please file a ticket and include the database type you are encountering the problem with.

Thanks Tom :)! I’ll file a ticket about this problem with all the details

@gabrielM @trothwein For your reference on this issue, I’ve created EXC-17530.