CODEWITHSUNDEEP
sqloracle-database
Shanna
Shanna

Reputation: 783

Get previous 10 seconds

I want to list the datetime with previous 10 seconds.

I tried with this

 SELECT    (SYSDATE -LEVEL + 1) each_day
    FROM DUAL
 CONNECT BY LEVEL <= 10

But I get previous 10 days.

Is it possible to get previous 10 seconds?

Upvotes: 0

Views: 86

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

Is it possible to get previous 10 seconds?

You almost got it:

 SELECT    (SYSDATE  + (1-LEVEL)/24/3600 ) each_sec
 --                     ^^^^^^^
 --                   including the 'current' sysdate
 --                   (as LEVEL start at 1)
    FROM DUAL
 CONNECT BY LEVEL <= 10

If you are open to the use of the INTERVAL data type, you might prefer using NUMTODSINTERVAL as it is somehow more self-documenting:

 SELECT    SYSDATE  + NUMTODSINTERVAL(1-LEVEL, 'SECOND') each_sec
    FROM DUAL
 CONNECT BY LEVEL <= 10

Upvotes: 4

Patrick Hofman
Patrick Hofman

Reputation: 156928

Substracting from SYSDATE is in days, so - 1 is minus one day. To convert that into seconds, use: 

select (sysdate - (level / 24 /*hours*/ / 3600 /*seconds*/)) each_second
from   dual
connect
by     level <= 10

Upvotes: 2

Related Questions