How to measure time in different time units (seconds, minutes, hours, days)?

My recent struggles with time measurment can be traced in the posts Intrygujące wyniki testów wydajnościowych - prześcignąć samego siebie! i OraGP: replace vs. regexp_replace

To summarize: if we want to measure the time we can use the TIMESTAMP data type for greater accuracy or the DATE data type, if the accuracy in seconds is satisfying.

In the script below, we calculate the time difference between start_date and end_date, which for example is 4 minutes, and present it in different units: seconds, minutes, hours and days.

 with daty (start_tmstp, end_tmstp, start_date, end_date) as (  
  select to_timestamp(to_char(sysdate, 'yyyymmddhh24miss'),'YYYYMMDDHH24MISS.FF'),   
      to_timestamp(to_char(sysdate- 4/1440, 'yyyymmddhh24miss'),'YYYYMMDDHH24MISS.FF'),  
      sysdate, sysdate- 4/1440 from dual  
 )  
 select   
  --- czas dla typ danych TIMESTAMP  
  to_char(round(extract( day from(start_tmstp - end_tmstp )*24*60*60*100)/100,2), '99990D00') czas_tmstp_sec,  
  to_char(round(extract( day from(start_tmstp - end_tmstp )*24*60 *100)/100,2), '99990D00') czas_tmstp_min,  
  to_char(round(extract( day from(start_tmstp - end_tmstp )*24 *100)/100,2), '99990D00') czas_tmstp_godz,  
  to_char(round(extract( day from(start_tmstp - end_tmstp )*100)/100,2), '99990D00') czas_tmstp_dni,  
  --- czas dla typ danych DATE  
  (start_date - end_date)* 24 *60 *60  czas_dt_sec,  
  (start_date - end_date)* 24 *60  czas_dt_min,  
  round((start_date - end_date)* 24,2) czas_dt_godz,  
  round((start_date - end_date) ,2) czas_dt_dni  
 from daty  
 ;  

As you can see, the method is very simple. In the first step, we calculate the difference between the dates as a timestamp or date and then, using multiplication, we convert to the units we expect:

- without multiplication - we will present the time in DAYS

- * 24 - we will present the time in HOURS

- * 24 * 60 - we will present the time in MINUTES

- * 24 * 60 * 60 - we will show the time in SECONDS