|
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; |
|