CODEWITHSUNDEEP
sqloracle-databasetimestamptimestamp-arithmetic
Jaydeep
Jaydeep

Reputation: 149

How to truncate timestamp to minutes?

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

Upvotes: 3

Views: 19687

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

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>

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use trunc():

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

Upvotes: 1

Related Questions