CODEWITHSUNDEEP
sqloracle-database
Zizzencs
Zizzencs

Reputation: 5128

How to trunc a date to seconds in Oracle

This page mentions how to trunc a timestamp to minutes/hours/etc. in Oracle.

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

Upvotes: 15

Views: 69925

Answers (7)

bierwaermer
bierwaermer

Reputation: 41

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

select cast(systimestamp as date) from dual

..does not truncate, but rounds to the next second instead.

I use a function:

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

    RETURN TS;

END;

For example:

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

Returns:


    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

Upvotes: 4

andy
andy

Reputation: 21

trunc work to min only, cast to date to_char(START_TIME,'YYYYMMDDHH24MISS')

or simply select to_char(current_timestamp, 'YYYYMMDDHH24MISS') from dual;

https://www.techonthenet.com/oracle/functions/trunc_date.php

Upvotes: 2

EvilTeach
EvilTeach

Reputation: 28837

Something on the order of:

select to_char(current_timestamp, 'SS') from dual;

Upvotes: 0

David
David

Reputation: 1315

To truncate a timestamp to seconds you can cast it to a date:

CAST(timestamp AS DATE)

To then perform the TRUNC's in the article:

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

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132580

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;

Upvotes: 33

drnk
drnk

Reputation: 764

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;

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52346

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.

Upvotes: 2

Related Questions