Skip to Main Content
  • Questions
  • Format for TRUNC(TIMESTAMP,<seconds>)

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jeffrey.

Asked: October 19, 2016 - 9:25 pm UTC

Last updated: April 06, 2023 - 6:59 am UTC

Version: 11g, 12.1

Viewed 50K+ times! This question is

You Asked

Is there a format specifier for TRUNCATE(timestamp,?) to seconds? The following all work:

TRUNC(SYSTIMESTAMP,'MI')
TRUNC(SYSTIMESTAMP,'HH')
TRUNC(SYSTIMESTAMP,'MM')
TRUNC(SYSTIMESTAMP,'YY')

But TRUNC(SYSTIMESTAMP,'SS') and any variation I've tried fails with ORA-01899: bad precision specifier

There are certainly workarounds, but this is driving me crazier than usual, as there doesn't seem to be anything in documentation nor have I been able to find anything on Internet.

Thanks!

and Connor said...

If you look in the SQL docs, you'll see that there actually *isnt* a trunc command for timestamps. So what is in fact happening is:

a) your timestamp is being silently converted to a *date*
b) the trunc command is being applied to your date

and hence SS is not appropriate.

Easy solution:

SQL> select systimestamp ts, cast(systimestamp as date) truncd_to_secs from dual;

TS                                                                          TRUNCD_TO_SECS
--------------------------------------------------------------------------- -------------------
20-OCT-16 09.47.21.505000 AM +08:00                                         20/10/2016 09:47:21


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Missing TRUNC(TIMESTAMP, <seconds> functionality

Jeffrey Jacobs, October 21, 2016 - 5:29 pm UTC

The underlying implementation is not a good reason for inconsistent/incomplete functionality. Hard to explain this to average developer, when we have TRUNC for both timestamp and date for other granularities; 'SS' should be added.

Personally, I prefer CAST(<my_ts> to TIMESTAMP(0)).

To confuse things a little, look at https://docs.oracle.com/cd/E29805_01/server.230/es_eql/src/cdfp_analytics_lang_trunc.html

You have to have your magnifying glass to realize this isn't for the database :-)
Connor McDonald
October 22, 2016 - 6:05 am UTC

CAST(<my_ts> to TIMESTAMP(0)).

Nice touch.

But when you say "when we have TRUNC for both timestamp and date for other granularities".... well, we dont. We are relying on the automatic datatype conversion (something I hate, and wish we had a setting to disable it).

For example, to_date(sysdate) is a valid command *not* because to_date takes a date parameter (it doesnt), but because we silently convert the sysdate to a string, and then apply the to_date function.

cast as timestamp(0)

Vito, March 22, 2018 - 12:24 pm UTC

Note that cast(systimestamp as timestamp(0)) works more like ROUND, where's cast(systimestamp as date) works like TRUNC
Connor McDonald
March 23, 2018 - 12:31 am UTC

Thanks - I didn't know that

Agreed Connor but

Gh, March 25, 2018 - 7:35 am UTC

something I hate, and wish we had a setting to disable it). 

Well something you hate, I don't mind
But hopefully never happens and fortunately Oracle is so. Otherwise developer must all be Aces. Moreover all worldwide code applications must be reviewed and redebugged and what about recode optimizer algorithms..

Connor McDonald
March 25, 2018 - 11:27 am UTC

That is why I said a "setting", not a "rule" :-)

A reader, January 08, 2020 - 10:02 am UTC

I want to remove fractional seconds including HH:MI:SS from date value.
Input : 2014-12-18 00:00:00.000000000
Output value: 2014-12-18
How the same can be achieved?
Chris Saxon
January 08, 2020 - 10:45 am UTC

As Connor shows in his original answer, casting the timestamp to a date removes the fractional seconds.

If you also want to "remove" the time component, use trunc without a format mask. Note that dates in Oracle Database always have a time.

IS THAT A BUG OR AM I DOING SOMETING WRONG ?

Kaz Klimek, April 04, 2023 - 11:54 pm UTC

Creation_date is timestamp
Start_date is date - all belong to table A,
date ineffective and date_effective are dates in separate table (B)
I want to select records
where greatest of creation_date or start_date are between date effective and date ineffective
However formula:
GREATEST(a.start_date, CAST(creation_date AS date)) BETWEEN
b.efective_date AND b.date_ineffective -returns no rows
In order to present data, I've changed formula to CAST(creation_date+1 AS DATE), then is working:

START_DATE CREATION_DATE DATE_EFFECTIVE DATE_INEFFECTIVE

2021-02-22T07:58:18.000+00:00
2021-03-30T07:33:35.062+00:00
2021-03-30T07:33:35.000+00:00
2021-11-22T15:51:30.000+00:00

Connor McDonald
April 06, 2023 - 6:59 am UTC

Show us your full test case, ie

create table...
insert statements
your query and the output