All Apps and Add-ons

DB connect postgres database SQL statement Unix timestamp filtering execution failed 02

Steven01
New Member

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

Steven01_0-1730362278370.png

However, this SQL statement cannot be executed in dbxquery, and the error is unknown

Steven01_1-1730362351629.png

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

Steven01_2-1730362426580.png

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

Labels (1)
0 Karma
Get Updates on the Splunk Community!

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...

4 Ways the Splunk Community Helps You Prepare for .conf25

.conf25 is right around the corner, and whether you’re a first-time attendee or a seasoned Splunker, the ...