oracle timestamp without seconds

Question

I have a timestamp(6) column in a table in my database. I would like to do a comparison on it but without considering the seconds part. I know that I can trunc(timestamp) to remove the time and leave the date part. Is there a way I can set the seconds to 0?

I want to do this:

CASE WHEN ARR NOT BETWEEN FROM AND TO THEN 1
     ELSE 0 END "mismatch"

..but get mismatches when the ARR has a seconds part greater than FROM and TO. I dont care about seconds and only want to consider minutes.

Any advice greatly appreciated.

Thanks

Answer 1

The trunc() function allows you so decide how much of the precision to discard via the optional fmt parameter; the default is to remove all time components, i.e. the equivalent of trunc(x, 'DD'). If you want to only lose the seconds you can use MI:

select systimestamp, trunc(systimestamp, 'MI') from dual;

SYSTIMESTAMP                        TRUNC(SYSTIMESTAMP,'MI')
----------------------------------- ------------------------
06-JAN-14 13.11.33.046920000 +00:00 2014-01-06 13:11:00      

Note that the value returned from trunc is now a DATE, not a TIMESTAMP. If that is an issue - which is may well not be - you can cast it back:

select systimestamp, cast (trunc(systimestamp, 'MI') as timestamp) from dual;

SYSTIMESTAMP                        CAST(TRUNC(SYSTIMESTAMP,'MI')ASTIMESTAMP)
----------------------------------- -----------------------------------------
06-JAN-14 13.14.43.270506000 +00:00 2014-01-06 13:14:00.000