Example of fetch last one hour or Minutes time from SYSDATE

Example of fetching last one hour or Minutes time from SYSDATE

First set the NLD_DATE_FORMAT for session level
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';

Fetch last one hours report by using sysdate

select sysdate "Current Time", sysdate-1/24 "Last One hour" from dual;

Current Time       Last One hour
------------------ ------------------
23-FEB-21 09:29:12 23-FEB-21 08:29:12

Fetch last 4 hours report by using sysdate

select sysdate "Current Time", sysdate-4/24 "Last Four hour" from dual;

Current Time       Last Four hour
------------------ ------------------
23-FEB-21 09:31:13 23-FEB-21 05:31:13

Fetch last 24 hours or 1 day from current datetime

SQL> select sysdate "Current Time", sysdate-1 "Last One Day" from dual;

Current Time       Last One Day
------------------ ------------------
23-FEB-21 09:30:15 22-FEB-21 09:30:15

Fetch one or 5 minute last data example from current datetime

select sysdate "Current Time", sysdate-1/(24*60) "Last one Minutes" from dual;

Current Time       Last one Minutes
------------------ ------------------
23-FEB-21 14:52:32 23-FEB-21 14:51:32

SQL> select sysdate "Current Time", sysdate-5/(24*60) "Last Five Minutes" from dual;

Current Time       Last Five Minutes
------------------ ------------------
23-FEB-21 14:51:58 23-FEB-21 14:46:58

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.