In a certain table I’m querying, time is stored like this:
09:30 is stored as 9,3
14:25 is stored as 14,25
How can I, using SQL, convert 9,3 to 09:30 (hh:mm format)? So far, the only thing I could find in other questions are people who need to convert 9,5 to 09:30 (in my case, 9,5 means 09:50).
Right now, I’m using PHP to convert this. But my DB server is better than my application server. Since I need to get a lot of data, it would be better to solve this using SQL.
Sorry if this is a stupid question, but I’m new to SQL. Feel free to ask for more questions if you need to.
Advertisement
Answer
Assuming those are numeric values, you can get the hour part with trunc(your_column) and the minutes part with 100 * mod(your_column, 1). That gives you two numbers; you can format those and concatenate them, again assuming you want a string result, e.g.:
to_char(trunc(your_column), 'FM00') ||':' || to_char(100 * mod(your_column, 1), 'FM00')
Or more simply, format the whole number as a single string in one step, by telling it to use a colon as the decimal separator (with anything as the group separator – that isn’t used):
to_char(your_column, 'FM00D00', 'NLS_NUMERIC_CHARACTERS=:,')
Demo with sample data in a CTE:
-- CTE for sample data
with your_table (time) as (
select 9.3 from dual
union all select 9.03 from dual
union all select 14.25 from dual
)
-- actual query
select time,
to_char(trunc(time), 'FM00') ||':' || to_char(100 * mod(time, 1), 'FM00') as new_time1,
to_char(time, 'FM00D00', 'NLS_NUMERIC_CHARACTERS=:,') as new_time2
from your_table;
TIME NEW_TIME1 NEW_TIME2
---------- --------- ---------
9.3 09:30 09:30
9.03 09:03 09:03
14.25 14:25 14:25
If you actually want an interval data type result then you can use the same split with trunc/mod but handle the two numbers differently:
select time,
numtodsinterval(trunc(time), 'HOUR')
+ numtodsinterval(100 * mod(time, 1), 'MINUTE') as new_time
from your_table;
TIME NEW_TIME
---------- -------------------
9.3 +00 09:30:00.000000
9.03 +00 09:03:00.000000
14.25 +00 14:25:00.000000
Or you can convert to that time on a nominal date if you prefer, e.g.:
date '2000-01-01' + trunc(your_column), 'HOUR') + numtodsinterval(100 * mod(your_column, 1), 'MINUTE')
At some point you’ll run into problems if you have values that don’t really represent valid times – for example, 9.60 or 25.3. Either of the queries above will handle that to some extent – at least, up to 99.99 for the first one – but won’t necessarily give useful results. The string would give you ’09:60′ and ’25:30′, while the interval would give you ‘0 10:00:00’ and ‘1 01:30:00’, which is slightly more sensible perhaps. That’s the danger of using an inappropriate data type though.
Preserving a comment from @BobJarvis, if you actually want a date data type result then you can convert the number to a string without any separator and then convert from that to a date:
to_date(to_char(your_column, '00V00'), 'HH24MI')
which will give you that time on the first day of the current month. However, that will error with 9.60 or 25.3.