Use of sysdate in oracle

 

Period

Expression 1

Expression 2

Expression 3

Value

1 Day

1

1

1

1

1 Hour

1/24

1/24

1/24

.041666667

1 Minute

1/24/60

1/(24*60)

1/1440

.000694444

1 Second

1/24/60/60

1/(24*60*60)

1/86400

.000011574

 

Description of Time Interval

SQL Expression

Now

Sysdate

Tomorrow/Next Day

sysdate+1

Yesterday

sysdate-1

Seven days from now

sysdate + 7

Seven days back from now

sysdate - 7

One hour from now

sysdate + 1/24

One hour back from now

sysdate - 1/24

Three Hours from now

sysdate + 3/24

Three Hours back from now

sysdate - 3/24

Half an hour from now

sysdate + 1/48

Ten Minutes from now

sysdate+ 10/1400

Ten Minutes back from now

sysdate- 10/1400

Thirty seconds from now

sysdate + 30/86400

Thirty seconds back from now

sysdate - 30/86400

Tomorrow at 12 Midnight

trunc(sysdate+1)

Yesterday at 12 Midnight

trunc(sysdate-1)

Tomorrow at 8 AM

trunc(sysdate+1)+8/24

Yesterday at 8 AM

trunc(sysdate-1)+8/24

Next Monday at 12 Noon

next_day(trunc(sysdate),‘MONDAY’)+12/24

First Day of the Month at 12 Midnight

trunc(last_day(sysdate) + 1)

The Next Monday, Wednesday or Friday at 9am

trunc(least(next_day(sysdate,''MONDAY''),
next_day(sysdate,''WEDNESDAY''),
next_day(sysdate,''FRIDAY''))) + (9/24)

 

 

Examples:

-- select last one minute data from table
select * from TRANSACTION t
where T.CREATED>=sysdate - 1/1440
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - 1/24/60
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - INTERVAL '1' MINUTE
order by created;

-- select last five minute data from table
select * from TRANSACTION t
where T.CREATED>=sysdate - 5/1440
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - 5/24/60
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - INTERVAL '5' MINUTE
order by created;

-- select last one second data from table
select * from TRANSACTION t
where T.CREATED>=sysdate - 1/86400
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - 1/24/60/6
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - INTERVAL '1' SECOND
order by created;


-- Select last one hour data from table
select * from TRANSACTION t
where T.CREATED>=sysdate - 1/24
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - INTERVAL '1' HOUR
order by created;


-- Select last 12 hour data from table or select last 1/2 day data from table
select * from TRANSACTION t
where T.CREATED>=sysdate - 12/24
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - 1/2
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - INTERVAL '12' HOUR
order by created;


-- select last 1 day data from table

select * from TRANSACTION t
where T.CREATED>=sysdate - 1
order by created;

or

select * from TRANSACTION t
where T.CREATED>=sysdate - INTERVAL '1' DAY
order by created;
 

Comments