Receive invalid values from filter in QueryRecord

Question

I have a JSON:

[{
  "id" : 1,
  "start_time" : 0,
  "status" : 0,
  "stop_time" : 0,
  "update_time" : 1692859450
},
{
  "id" : 2,
  "start_time" : 0,
  "status" : 1,
  "stop_time" : 0,
  "update_time" : 1692859455
},
{
  "id" : 3,
  "start_time" : 0,
  "status" : 0,
  "stop_time" : 0,
  "update_time" : 1687680280
}]

I want to filter ids that have status = 1 or stop_time/update_time > date from now minus 30 days ago. I ran query inside QueryRecord:

SELECT 
    CAST('campaign_daily_stat' AS VARCHAR) AS report_type,
    CAST('campaign' AS VARCHAR) AS ids_type,
    LISTAGG(id, ',') as ids,
    COUNT(id) as ids_count
FROM FLOWFILE
WHERE status = 1 OR ((stop_time*1000) >= ${now():toNumber():minus(2592000000)} OR (update_time*1000) >= ${now():toNumber():minus(2592000000)})

But it returns only one id: 2. I expect to get also id = 1.

[ {
  "report_type" : "campaign_daily_stat",
  "ids_type" : "campaign",
  "ids" : "2",
  "ids_count" : 1
} ]

I tried to reproduce this (WHERE clause) in database inside table and this id (1) I can get with this query (ofc, modified for postgresql). What is the problem with QueryRecord and what did I miss?

Answer 1

You must cast also stop_time and update_time fields. This works for me:

SELECT 
    CAST('campaign_daily_stat' AS VARCHAR) AS report_type,
    CAST('campaign' AS VARCHAR) AS ids_type,
    LISTAGG(id, ',') as ids,
    COUNT(id) as ids_count
FROM FLOWFILE
WHERE status = 1 OR ((CAST(stop_time AS BIGINT) *1000) >= ${now():toNumber():minus(2592000000)} OR (CAST(update_time AS BIGINT) *1000) >= ${now():toNumber():minus(2592000000)})

output:

[ {
  "report_type" : "campaign_daily_stat",
  "ids_type" : "campaign",
  "ids" : "1,2",
  "ids_count" : 2
} ]