truncate

Truncating a timestamp to the second

Connor McDonald , , ,

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

How do I stop the use of TRUNCATE

Connor McDonald ,

Truncate is one of those commands that cannot be undone trivially…In fact, you might well be looking at a database recovery in order to get a truncated table back to a “full” state.  So often we’d like to bar the use of it.  Obviously, to truncate a table in another schema you need DROP ANY TABLE which one would hope very very very few people, aka none 🙂 would be granted, so really this is all about connections as the owner of a table.

Ideally, the solution is part of the application implementation, namely, that people never connect as the schema owner – that’s just good security practice for applications even without considering truncate. A common technique is to disable the ability to connect as the schema owner, so that all access must come from another schema which only has insert, update, delete, select access to the tables, or even better, the data access is entirely held behind a PL/SQL layer, and execute on those PL/SQL modules is all that is given.

But … out in the wild of the real world, the threat is often still present.  So you can use a trigger as a stopgap measure.



SQL> create or replace trigger ddl_trigger
  2  before truncate on scott.SCHEMA
  3  declare
  4      l_sysevent varchar2(25);
  5  begin
  6      select ora_sysevent into l_sysevent from dual;
  7
  8      if ( l_sysevent in ('TRUNCATE') )
  9      then
 10          raise_application_error(-20000,'no no no no');
 11      end if;
 12  end;
 13  /

Trigger created.

SQL> truncate table t;
truncate table t
               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: no no no no
ORA-06512: at line 8