CODEWITHSUNDEEP
sql-serveroracle-databaset-sqldatetimetime
JSLover
JSLover

Reputation: 63

How to fetch only last 1 hour entries from a SQL Server or Oracle table?

I have a log table where I have a date column log_date value like 2021-03-02 07:51:41.000 in the format.

My requirement is that on click of a button I want to list out of the log entries from the last hour.

I searched SO and find out the below query which is for MySQL. I need two separate T-SQL queries that should work on SQL Server and Oracle for my requirement since the common query is not possible.

What is the best way to fetch the last 1 hour records from the log table for SQL and Oracle (separate query)?

select count(*) as cnt
from  log
where log_date >= DATE_SUB(NOW(), INTERVAL 1 HOUR);

Upvotes: 0

Views: 2959

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21115

Create a database view in each of your databases, that filter only teh last hour of the log table.

here an example for Oracle

create view log_last_hour as
select * 
from log
where log_date >= sysdate - interval '1' hour;

Than you can use a single simple query that is database independent

Example

select count(*) from log_last_hour

Upvotes: 1

Related Questions