I need best query that converts input string with time format "hh:mm" into integer that represent offset/length from time '00:00' to "hh:mm" in minutes.
For example '12:59' is 779 minutes starting from '00:00'.
The time format is fixed always, Php ensures it has colon in the middle and 2 digits surrounding it.
My example query:
WITH Timetable AS
(SELECT '12:59' AS Timevalue FROM Dual)
SELECT Hourpart * 60 + Minutespart AS Minuteoffsetfrom00
FROM (SELECT To_Number(Regexp_Substr(Timevalue, '[^:]+', 1, 1)) AS Hourpart,
To_Number(Regexp_Substr(Timevalue, '[^:]+', 1, 2)) AS Minutespart
FROM Timetable);--779 minutes from 00:00
Can you provide better query?
Oracle 10g, Solaris OS.