How can I obtain the record of the last 2 hours of a table?
I need to obtain the records of the last 2 hours with format (HH:MM:SS), it only leaves me specifying the exact time
Comments
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.
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())
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)?
Please note that all timestamps (TimeCreted, TimeProcessed) in Ens.MessageHeader use Ens.DataType.UTC datatype. It's UTC time.
So, in what mode is your query running?
GETDATE() : A date/time function that returns the current local date and time.
Hello, thank you very much for the help, I managed to get what I expected
glad you figured it out!