Oracle SQL - Trying to Add One Second to a Date I&

Question

SQL newbie here.

In Oracle BI Publisher, I'm using Oracle SQL to do the following.

I need to pull only the time from a string and add one second to the time. The data in the 'Flight' column looks like this:

Dayton 01:23:59

I pull only the time using the following syntax:

substr(Flight,length(Flight)-8,8)

Which gives me this:

01:23:59

In the messy code below, I'm trying to add one second to the time which works but the second is '60' which is obviously is an invalid time.

 substr(lpad(to_number(replace(substr(Flight,length(Flight)-15,9),':')+2,'999999'),6,'0'),1,2)||':'||substr(lpad(to_number(replace(substr(Flight,length(Flight)-15,9),':')+2,'999999'),6,'0'),3,2)||':'||substr(lpad(to_number(replace(substr(Flight,length(Flight)-15,9),':')+2,'999999'),6,'0'),5,2)

Any ideas for a better way to do this?

Dan

Answer 1

Can you do something like:

 to_date(substr(Flight,length(Flight)-8,8), HH:MI:SS') + interval '1' second

Answer 2

You can also use

TO_DATE(SUBSTR(FLIGHT, LENGTH(FLIGHT-8)), 'HH24:MI:SS') + 1 / (24 * 60 * 60)

or

TO_DATE(SUBSTR(FLIGHT, LENGTH(FLIGHT-8)), 'HH24:MI:SS') + 1 / 86400

because

   24 = hours in a day
 * 60 = minutes in an hour
 * 60 = seconds in a minute
-----
86400 = seconds in a day

After a while you find you've memorized numbers like that. :-)