I am trying to set up a JMS Message Selector for a trigger that will only pick up messages say 15 minutes or older. I can use a simple query such as JMSTimestamp > 12345… and messages with timestamps larger than the one I hard coded in will be picked up, so I know that part works. I however want to pick up messages older than the current time minus 15 minutes.
From the Built-in reference I saw this “A message selector allows a client to filter the messages it wants to receive by use of a SQL92 string expression in the message header.”. The JMS timestamp is in Unix time while the SQL92 CURRENT_TIMESTAMP comes in a more human readable format. I have seen SQL92 examples online where people will attempt to convert the current timestamp into Unix format with something like SELECT DATEDIFF(s, ‘1970-01-01 00:00:00’, CURRENT_TIMESTAMP)
The problem is I cannot get this to work in the JMS Message Selector field despite the doc saying SQL92 IS supported. Even putting something simple in such as JMSTimestamp > SELECT DATEDIFF (‘2000-01-10’, ‘2000-01-05’); which is the equivalent of JMSTimestamp > 5 did not work.
Any help or other approaches I can take? Perhaps there is a more simple way to pickup messages older than a certain amount of time.