CODEWITHSUNDEEP
sqloracle-database
Efraim Efi Gavrieli
Efraim Efi Gavrieli

Reputation: 51

Change int to time oracle

I have cloumn "time" with datatype int i want to make a query that will show

the time like time not as int.

For example:

1356 convert it to 13:56

Upvotes: 0

Views: 162

Answers (4)

astentx
astentx

Reputation: 6751

Since Oracle does not havetime datatype, you may only format your number to look like time. But you need to be sure that your number has appropriate values of hour an minute.

with a as (
  select 1357 as tm from dual
  union all
  select 6789 from dual
)
select
  to_char(tm, 'FM00G00',
  q'[NLS_NUMERIC_CHARACTERS=' :']') as tm
from a

| TM    |
| :---- |
| 13:57 |
| 67:89 |

db<>fiddle here

UPD: Alternatively you may consider to store your time values as appropriate Oracle's datatype: interval day to second. This allows you to:

  • Keep time arithmetics (they can be summed up or added to dates).
  • Keep time restrictions: minute part will always be between 0 and 60, hour part will always be between 0 and 23. Also you can specify a precision of days and seconds to have upper boundary for interval without error-prone substrings or divisions.

The only drawback is formatting: thes still do not support format models (as your current approach do), but offer correct time semantics.

This way your initial time may be represented as

create table t (
  tm interval day(0) to second(0) /*Only one day without fractional seconds*/
)

insert into t
select interval '23:57' hour(0) to minute from dual
union all
select interval '10:12:13' hour to second from dual

/*Substring here is safe, because there's no format model
and we know seconds precision is zero.
So last 8 symbols will be a pure time with hh24:mi:ss*/
select substr(to_char(tm), -8, 5) as formatted
from t

| FORMATTED |
| :-------- |
| 23:57     |
| 10:12     |

db<>fiddle here

Upvotes: 3

Prabir
Prabir

Reputation: 1586

As your datatype for time column is 'int', you have to first convert it to character datatype.

After converting you can use substr function to split the string and then concatenate the split strings adding ':' in the middle.

I hope the below sample query would satisfy your use case.

select substr(to_char(1356, 'FM0000'),1,2) || ':' || substr(to_char(1356, 'FM0000'),3,2) from dual;

To learn more about Oracle Substr and Concatenate function you can follow this and this link respectively.

Upvotes: 2

Manish Kumar
Manish Kumar

Reputation: 192

You can do:-

TO_DATE((TO_CHAR(time), 6, '0'), 'HH24MISS')

Yet another method is to use an NLS trick for NUMBER formatting:

SELECT TO_CHAR(time, 'fm00G00G00', 'NLS_NUMERIC_CHARACTERS=''.:''')

Upvotes: 0

imperial
imperial

Reputation: 9

Try casting integer column to date/time format. You can use this in case of your issue:

select to_char(to_date(164741, 'hh24miss'),'hh:mi')

Upvotes: 0

Related Questions