Easily Convert Seconds To Hours:Minutes:Seconds

Using the datatypes INTERVAL it is very easy to do time conversions. Let's look at some handy examples!

How many days, hours, minutes and seconds are contained in 1234567 seconds?

  select interval '1234567' second 
    from dual ; 

INTERVAL'1234567'SECOND
-----------------------
14 6:56:7.0

It's 14 days, 6 hours, 56 minutes and 7 seconds! But what if I just want to know the number of complete days? Just use the extract function to get a single component:

  select extract( day from interval '1234567' second ) days
    from dual ; 

DAYS
----
14

Adding two intervals together is equally simple:

  select interval '5-3' year to month + interval'20' month 
    from dual ; 

INTERVAL'5-3'YEARTOMONTH+INTERVAL'20'MONTH
------------------------------------------
6-11

Five years and three months added to 20 months is: six years and eleven months.

That's really easy, isn't it? What does the next query do? You'll have no problem to tell!

  select extract( hour from interval '2:12' hour to minute + interval '2:52' hour to minute ) hours
    from dual ; 

HOURS
-----
5