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;

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

One thought on “Truncating a timestamp to the second

  1. 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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s