New post
Question
· Apr 4

How can I obtain the record of the last 2 hours of a table?

#SQL #InterSystems IRIS for Health

I need to obtain the records of the last  2 hours with format (HH:MM:SS), it only leaves me specifying the exact time

Product version: IRIS 2022.1
Discussion (6)3
Log in or sign up to continue
Ben Spead · Apr 4

Can you explain what you mean by this? "it only leaves me specifying the exact time"   It makes it sound like you are using a UI of some sort?  Assuming that is the case, you should be able to access the records directly from the underlying table using SQL.  If you tell us specifically what table you're trying to look at we may be able to be more specific in a recommendation.

2 0
Claudia Cortes Villa · Apr 5

Yes, in the healthshare environment I am running the following script:

SELECT 
    id, 
    substr(TimeCreated, 12, 5) as HORA, 
    (DATEDIFF(ms, TimeCreated, TimeProcessed)) as TiempoTranscurrido ,
    TimeCreated as fecha,
    GETDATE() as hora_actual
FROM 
    ens.messageheader 
WHERE 
    SourceConfigName = 'HL7Filler HCIS'
    AND TimeCreated >= '2024-03-19 12:51:11.125'


I would like that in exchange for entering the exact time I could obtain it in the format, I have tried with the following script but it brings me the messages for that exact time and I need to bring all the logs in that time period "2 hours"

SELECT 
    id, 
    substr(TimeCreated, 12, 5) as HORA, 
    (DATEDIFF(ms, TimeCreated, TimeProcessed)) as TiempoTranscurrido ,
    TimeCreated as fecha,
    GETDATE() as hora_actual
FROM 
    ens.messageheader 
WHERE 
    SourceConfigName = 'HL7Filler HCIS'
    AND TimeCreated >= DATEADD(hour, -2, GETDATE())
 

0 0
Ben Spead · Apr 5

I don't have access to an active HS environment at the moment, but from testing the last part of your query, it looks correct to me.  You are saying with the query that has the literal timestamp you get results, but with the 2nd query which targets the prior 2 hours you are getting no results?  are you sure that there are records in that time window to fetch?  What happens if you change it to a 24 hour window (-24)?

0 0