How to truncate timestamp to minutes?

Question

I have to subtract 5 minutes from current timestamp and floor(truncate) it to nearest minute. Like '2016-02-23 06:10:39.0' should be '2016-02-23 06:05:00.0'. I have found way to subtract 5 minutes as

 systimestamp - interval '5' minute

EDIT 1: I need timestamp in particular format,

 TO_TIMESTAMP((systimestamp - interval '15' minute),'YYYY-MM-DD HH24:MI:SS.ff')

But this is giving

ORA-01830: date format picture ends before converting entire input string

But I am not able to floor it to nearest minute. Please help. Thanks

Solution 1

You could use TRUNC() with the precision you want. To trunc only till minutes, use MI.

For example,

SQL> SELECT SYSTIMESTAMP, trunc(SYSTIMESTAMP - INTERVAL '5' MINUTE, 'MI') new_tmstmp
  2  FROM dual;

SYSTIMESTAMP                        NEW_TMSTMP
----------------------------------- -------------------
16-MAR-16 04.44.02.379000 PM +05:30 03/16/2016 16:39:00

SQL>

Remember, the above output will be a DATE and not TIMESTAMP. You can explicitly CAST the date as timestamp:

SQL> SELECT SYSTIMESTAMP,CAST(trunc(SYSTIMESTAMP - INTERVAL '5' MINUTE,'MI') AS TIMESTAMP) tm
  2  FROM dual;

SYSTIMESTAMP                        TM
----------------------------------- ------------------------------
16-MAR-16 04.53.25.802000 PM +05:30 2016-03-16 04:48:00.000000

SQL>

Solution 2

You can use trunc():

trunc(systimestamp - interval '5' minute, 'minute')