ORA-01722: invalid number in an Oracle query that tries to pick up the scheduled signals in the next 30 seconds

Question

I have a so-called signal_table in Oracle 11G which has the columns below:

signal_type VarChar(2)
signal_time VarChar(2)

signal_time has a format HH24:MI:SS (e.g. "15:35:30"), which means that an application fires this type of signal at 15:35:30 every day.

Now, I am trying to write a query which lists up all the signals that are due to be fired in 30 seconds from now.

I tried something like

select  
  signal_type,  
  signal_time,  
from  
  signal_table  
where  
  to_number(to_char(substr(signal_time,1,2)))*3600 + to_number(to_char(substr(signal_time,4,2)))*60 + to_number(to_char(substr(signal_time,7,2))) - to_number(to_char(sysdate,'HH24'))*3600 + to_number(to_char(sysdate,'MI'))*60 + to_number(to_char(sysdate,'SS')) < 30
;

thinking that I would convert the VarChar time stamps to numbers in seconds.
However, to the above query I got "ORA-01722: invalid number" error.

The query like below works OK:

select  
  signal_type,  
  signal_time,  
  to_number(to_char( substr(signal_time,1,2)))*3600 + to_number(to_char(substr(signal_time,4,2)))*60 + to_number(to_char(substr(signal_time,7,2))) - to_number(to_char(sysdate,'HH24'))*3600 + to_number(to_char(sysdate,'MI'))*60 + to_number(to_char(sysdate,'SS'))
from  
  signal_table  
;

Can anyone shed a light onto why I get the above error when I put the to_number cast portion in where clause?

Better still, is there a more elegant way to achieve this goal?

Answer 1

What you've done works (up to a point) as long as all the data in the table does really have values in nn:nn:nn format. The error suggests you have at least one row that is formatted incorrectly. Although it's odd that your second query works - maybe you have a where clause you haven't shown?

Here's an SQL Fiddle showing it action. It doesn't error, but it shows you all the signal times that have already passed for today, so you need to restrict it to be between the current time and thirty seconds form now, if I've understood you. This fiddle does that, and also removes some unnecessary conversions:

select  
  signal_type,
  signal_time
from
  signal_table
where  
  (to_number(substr(signal_time,1,2))*3600
    + to_number(substr(signal_time,4,2))*60
    + to_number(substr(signal_time,7,2)))
    - (to_number(to_char(sysdate,'HH24'))*3600
    + to_number(to_char(sysdate,'MI'))*60
    + to_number(to_char(sysdate,'SS'))) >= 0
and
  (to_number(substr(signal_time,1,2))*3600
    + to_number(substr(signal_time,4,2))*60
    + to_number(substr(signal_time,7,2)))
    - (to_number(to_char(sysdate,'HH24'))*3600
    + to_number(to_char(sysdate,'MI'))*60
    + to_number(to_char(sysdate,'SS'))) < 30
;

You could also convert the signal_time into a date and compare that with sysdate:

select
  signal_type,
  signal_time
from
  signal_table
where
  to_date(signal_time, 'HH24:MI:SS') - trunc(sysdate, 'MM')
    >= sysdate - trunc(sysdate, 'DD')
  and to_date(signal_time, 'HH24:MI:SS') - trunc(sysdate, 'MM')
    < sysdate + interval '30' second - trunc(sysdate, 'DD')
;

Yet another fiddle. This extracts the time portion as a fraction of a day, for the signal_time, current time and current time + 30 seconds.

To find values which are not formatted correctly, a quick check would be something like:

select
  signal_type,
  signal_time
from
  signal_table
where
  not regexp_like(signal_time, '^\d\d:\d\d:\d\d$')
;

(I'm not too hot on regex so that can probably be done more neatly). That will only find badly formatted values though, and won't spot invalid times, e.g. 24:60:60, which your query will convert. The date version would complain about that (ORA-01850), but would also be more forgiving of slightly varied formats, e.g. missing a leading zero on one of the elements. Which may or may not be a good thing.

---

If you have records in a different format that you need to exclude, but cannot correct, then (apart from suggesting a data model problem) you can use the check query as a subquery in your main one:

select
  signal_type,
  signal_time
from
  (
    select
      *
    from
      signal_table
    where
      regexp_like(signal_time, '^\d\d:\d\d:\d\d$')
  )
where
  to_date(signal_time, 'HH24:MI:SS') - trunc(sysdate, 'MM')
    > sysdate - trunc(sysdate, 'DD')
  and to_date(signal_time, 'HH24:MI:SS') - trunc(sysdate, 'MM')
    < sysdate + interval '30' second - trunc(sysdate, 'DD')
;

Another fiddle showing this working, and then the earlier simpler query failing, with the same data - including one bad record.

It will still fail if you have valid formatting but invalid times (e.g. 24:60:60). If that is the case then you really need to clean your data, but could maybe come up with a more restrictive regex, or use a function something like this to check for valid formats at runtime.

In some cases you might need to add hints to stop the filter being applied to the inner select but I don't think that will be an issue in this case.

Answer 2

SELECT
TO_CHAR(TO_DATE(signal_time, 'HH24:MI:SS'),  'HH24:MI:SS') - TO_CHAR(NOW(),  'HH24:MI:SS')
;