SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Rounding timestamp component

The_Cute_DBASep 6 2022

Hi Experts,
I have a sample query as shown below:

select 1661352894 sample_value,
timestamp'1970-01-01 00:00:00' + 1661352894 * interval '1' second as converted_to_timestamp
from dual;

SAMPLE_VALUE CONVERTED_TO_TIMESTAMP
------------- --------------------------------
  1661352894 08/24/2022 02:54:54.000000000 PM

I have been working on how to truncate the timestamp to the nearest hour, so in my example above 02:54:54.000000000 PM should be truncated to 02:00:00.000000000 PM.
I have been trying some other approach like the one below. I was able to truncate the hour component but I have issues with the date component.

SELECT
TIMESTAMP '1-1-1 00:00:00' + Numtodsinterval(1661352894,'second') - TIMESTAMP '1-1-1 00:00:00' orig_time,
trunc(TIMESTAMP '1-1-1 00:00:00' + Numtodsinterval(1661352894,'second'),'hh') - TIMESTAMP '1-1-1 00:00:00' truncated_value
  FROM dual;

ORIG_TIME		TRUNCATED_VALUE
---------------------   -------------------
+19228 14:54:54.000000	+19228 14:00:00.000000

Any suggestions or ideas?

This post has been answered by Arun Kumar Gupta on Sep 6 2022

Jump to Answer

Added on Sep 6 2022

5 comments

1,168 views

1 mention