Posted by We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value. At first glance, TRUNC seems fine.
SQL> select trunc(localtimestamp,'MM') to_the_month from dual;
TO_THE_MO
---------
01-OCT-16
1 row selected.
SQL> select trunc(localtimestamp,'YYYY') to_the_year from dual;
TO_THE_YE
---------
01-JAN-16
1 row selected.
SQL> select trunc(localtimestamp,'DD') to_the_day from dual;
TO_THE_DA
---------
20-OCT-16
1 row selected.
But the moment you try apply a TRUNC down to the second, then things go wrong
SQL> select trunc(localtimestamp,'SS') to_the_day from dual;
select trunc(localtimestamp,'SS') to_the_day from dual
*
ERROR at line 1:
ORA-01899: bad precision specifier
The clue here is if we look at the DUMP output for some of these commands
SQL> select dump(localtimestamp) from dual;
DUMP(LOCALTIMESTAMP)
-----------------------------------------------------------------------
Typ=187 Len=20: 224,7,10,20,13,48,45,0,192,3,180,35,8,0,3,0,0,0,0,0
1 row selected.
SQL> select dump(trunc(localtimestamp,'YYYY')) dmp from dual;
DMP
-----------------------------------------------------------------------
Typ=13 Len=8: 224,7,1,1,0,0,0,0
1 row selected.
Notice that the datatype has changed. In fact, type 13 is the same datatype as we see for SYSDATE
SQL> select dump(sysdate) from dual;
DUMP(SYSDATE)
-------------------------------------------
Typ=13 Len=8: 224,7,10,20,13,50,1,0
If you look in the SQL documentation, you’ll see that there actually is NOT a trunc command for timestamps. So what is in fact happening is:
- the timestamp is being silently converted to a date,
- the trunc command is being applied to the date
and just like any date, SS is not an appropriate TRUNC mask.
SQL> select trunc(sysdate,'SS') from dual;
select trunc(sysdate,'SS') from dual
*
ERROR at line 1:
ORA-01899: bad precision specifier
A simple workaround is to convert the timestamp to a date, and if necessary, convert it back to a timestamp to preserve the datatype.
SQL> select localtimestamp ts, cast(localtimestamp as date) truncd_to_secs from dual;
TS TRUNCD_TO_SECS
---------------------------------------- -------------------
20-OCT-16 01.54.09.991000 PM 20/10/2016 13:54:09
SQL> select dump(cast(cast(localtimestamp as date) as timestamp)) from dual;
DUMP(CAST(CAST(LOCALTIMESTAMPASDATE)ASTIMESTAMP))
-----------------------------------------------------------------------------
Typ=187 Len=20: 224,7,10,20,13,54,45,0,0,0,0,0,0,0,3,0,0,0,0,0
Learning is not a spectator sport 
How it’s implemented is not a good excuse for inconsistent functionality. Also, I prefer CAST(my_ts to TIMESTAMP (0),but there really should be an ‘SS’ option…
CAST(.. TO TIMESTAMP(0)) will *round* the fractional seconds, not truncate them.