1

I am creating a SQL query in Oracle that reads from an HFM Errorlog table and returns rows from only the last 15 minutes. The timestamp (labeled DTIMESTAMP) is in float form.

select DTIMESTAMP, to_char((to_date('12/30/1899', 'MM-DD-YYYY') + DTIMESTAMP), 'MM-DD-YYYY HH24:MI:SS')
from errorlogtable
where to_timestamp(to_char((to_date('12/30/1899', 'MM-DD-YYYY') + DTIMESTAMP), 'MM-DD-YYYY HH24:MI:SS'), 'MM-DD-YYYY HH24:MI:SS') >= current_timestamp - numtodsinterval(15, 'MINUTE')
order by to_date('12/30/1899', 'MM/DD/YYYY') + DTIMESTAMP desc;

So far I've been able to convert the float to timestamp and order the rows in descending order. I've tried adding in >= current_timestamp - numtodsinterval(15, 'MINUTE') to subtract minutes (source), but the query result does not give me rows from the JUST the past 15 minutes:

DTIMESTAMP          TO_CHAR((TO_DATE('12/30/1899','MM-DD-YYYY')+DTIMESTAMP),'MM-DD-YYYYHH24:MI:SS')
44396.866425865199  07-19-2021 20:47:39
44396.866262089308  07-19-2021 20:47:25
44396.866253134955  07-19-2021 20:47:24
44396.866135289652  07-19-2021 20:47:14
44396.866051527948  07-19-2021 20:47:07
44396.865806648253  07-19-2021 20:46:46
44396.865774980128  07-19-2021 20:46:43
44396.865570532624  07-19-2021 20:46:25
44396.865386139281  07-19-2021 20:46:09
44396.864962337764  07-19-2021 20:45:33
44396.862953316297  07-19-2021 20:42:39
44396.862790021121  07-19-2021 20:42:25
44396.862570618447  07-19-2021 20:42:06
44396.862294215593  07-19-2021 20:41:42
44396.862097731675  07-19-2021 20:41:25
44396.86190194558   07-19-2021 20:41:08
44396.861138356333  07-19-2021 20:40:02
44396.85947988947   07-19-2021 20:37:39
44396.859317128168  07-19-2021 20:37:25
44396.859097455475  07-19-2021 20:37:06
44396.858821731345  07-19-2021 20:36:42
44396.858624718217  07-19-2021 20:36:25
44396.858428757965  07-19-2021 20:36:08
44396.856007583832  07-19-2021 20:32:39
44396.855843930753  07-19-2021 20:32:25
44396.855624130905  07-19-2021 20:32:06
44396.855348883277  07-19-2021 20:31:42
44396.855152074444  07-19-2021 20:31:25
44396.854956858158  07-19-2021 20:31:08
44396.852535187223  07-19-2021 20:27:39

Is there anything wrong with my query that's preventing it from getting just the past 15 minutes of results? Thank you!

kknilop
  • 11
  • 2
  • 1
    The query look good to me. Have you considered that the calculated timestamps get represented in another time zone and thus may not correspond to your local time zone? – Matthias Jul 19 '21 at 21:14
  • 2
    Also understand that "date" and "timestamp" are different datatypes in Oracle. "date" should always be the system time zone, where a "timestamp" (e.g. TO_TIMESTAMP) can have a specific time zone. For the purposes of your query predicate, would it be easier to convert sysdate to your numeric format (one calculation, rather than every row), and then just select values with the correct numerical difference, then perform "TO_DATE" only on the selected rows? – pmdba Jul 19 '21 at 21:23
  • 1
    The query is overcomplicated with `to_timestamp(to_char`. Use [literals](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-8F4B3F82-8821-4071-84D6-FBBA21C05AC1) for this. And do reverse calculation to filter the `DTIMESTAMP` directly. And try `localtimestamp` instead of `current_timestamp` – astentx Jul 19 '21 at 21:53
  • In addition to other comments above, `TIMESTAMP` has a lot finer resolution than `DATE` -- DATE includes up to seconds, and `TIMESTAMP` supports fractional seconds, up to 9 digits. – Mark Stewart Jul 20 '21 at 14:26

0 Answers0