How to trunc a date to seconds in Oracle

Question

This page (http://www.techonthenet.com/oracle/functions/trunc_date.php) mentions how to trunc a timestamp to minutes/hours/etc. in Oracle.

How would you trunc a timestamp to seconds in the same manner?

Answer 1

Since the precision of DATE is to the second (and no fractions of seconds), there is no need to TRUNC at all.

The data type TIMESTAMP allows for fractions of seconds. If you convert it to a DATE the fractional seconds will be removed - e.g.

select cast(systimestamp as date) from dual;

Answer 2

To trunc a timestamp to seconds you can cast it to a date

CAST( timestamp AS DATE)

To then perform the TRUNC's in the article:

eg. TRUNC(CAST(timestamp AS DATE), 'YEAR')

Answer 3

On the general topic of truncating Oracle dates, here's the documentation link for the format models that can be used in date trunc() AND round() functions

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions242.htm#sthref2718

"Seconds" is not listed because the granularity of the DATE datatype is seconds.

Answer 4

Something on the order of
select to_char(current_timestamp, 'SS') from dual;

Answer 5

I used function like this:

FUNCTION trunc_sec(p_ts IN timestamp)
IS
    p_res timestamp;
BEGIN
    RETURN TO_TIMESTAMP(TO_CHAR(p_ts, 'YYYYMMDDHH24MI'), 'YYYYMMDDHH24MI');
END trunc_sec;

Answer 6

I am sorry, but all my predecessors seem to be wrong:

select cast(systimestamp as date) from dual does not truncate but round to the next second.

I use a function:

CREATE OR REPLACE FUNCTION TRUNC_TS(TS IN TIMESTAMP) RETURN DATE AS
BEGIN
  RETURN TS;
END;

SELECT systimestamp, trunc_ts(systimestamp) date_trunc, 
    CAST(systimestamp AS DATE) date_cast FROM dual

SYSTIMESTAMP                       DATE_TRUNC             DATE_CAST
21.01.10 15:03:34,567350 +01:00    21.01.2010 15:03:34    21.01.2010 15:03:35