Hi, brother,
I have encountered a confusing problem regarding Postgres data integration.
I want to execute this statement every two minutes to retrieve the latest results from the database, so I use the following statement for filtering.
SELECT
history_uint.itemid,
history_uint.value,
interface.ip,
items.name,
hosts.host,
TO_TIMESTAMP(history_uint.clock) AS clock_datetime
FROM
history_uint
JOIN items ON history_uint.itemid = items.itemid
JOIN hosts ON items.hostid = hosts.hostid
JOIN interface ON interface.hostid = hosts.hostid
WHERE
history_uint.clock > (FLOOR(EXTRACT(EPOCH FROM NOW())) - 120)
AND items.flags = 4
AND (items.name LIKE '%Bits sent%' OR items.name LIKE '%Bits received%')
AND (hosts.host LIKE 'CN%' OR hosts.host LIKE 'os%')
ORDER BY
history_uint.clock DESC
LIMIT
90000;
This SQL statement executes perfectly in database tools
However, this SQL statement cannot be executed in dbxquery, and the error is unknown
I found that the key reason is the following SQL statement:
Among them, history_uint.clock>(FLOOR (EXTRACT (EPOCH)
FROM NOW（））） - 120）
When I replaced (FLOOR (EXTRACT (EPOCHFAM NOW()) -120) with Unix timestamps, everything was fine
I tried to replace it with other SQL statements, but they all failed.
Please help me analyze this issue together. thank you.
Add some environmental information:
Postgres version: 14.9
Java version 21.0.5
DB connet version 3.18.1
Postgres JDBC version 1.2.1
thank you
