Top 60 Oracle Blogs

Recent comments

Truncating a timestamp to the second

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;


1 row selected.

SQL> select trunc(localtimestamp,'YYYY') to_the_year from dual;


1 row selected.

SQL> select trunc(localtimestamp,'DD') to_the_day from dual;


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;

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;

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;

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 PM             20/10/2016 13:54:09

SQL> select dump(cast(cast(localtimestamp as date) as timestamp)) from dual;

Typ=187 Len=20: 224,7,10,20,13,54,45,0,0,0,0,0,0,0,3,0,0,0,0,0