Solved

Minutes(Number Column) to HH24MM

Posted on 2000-05-15
8
646 Views
Last Modified: 2008-03-17
Good Morning

Have a table with a defined column elapsed_time(number).

example 90 minutes

I want to convert it to HH24MM.

example 0130

Is this possible in Oracle 8i.

Thanks
0
Comment
Question by:tomatocans
8 Comments
 
LVL 5

Expert Comment

by:sbenyo
ID: 2810472
There is no time datatype in oracle but you can change the field to a date field like this:

alter table <table-name> modify (elapsed_time date);

Then insert values like this:

insert into <table-name> values (to_date('0130','HH24MI'));

This will store the time value.
If you want to make comparisions on it also use the same format:

select * from <table-name> where elapsed_time > to_date('0230','HH24MI');


0
 

Author Comment

by:tomatocans
ID: 2810632
what if u have 60,000 records with elapsed time
fields u need to update
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 2810802
There is also another issue.  Oracle date columns always contain a date value, whether they contain the time protion or not.  If you succeed in storing values when you have supplied only the time (I am not sure that Oracle will accept that) you may still have problems doing comparisons or arithmetic based on those values if the days are different.  I am assuming that Oracle will use the current sysdate for the date portion, so a record created just before midnight one day will have a different date than a records created moments later after midnight.

Have you tested this?
0
 

Expert Comment

by:erezal
ID: 2811308
Why do you want to convert a field
containing elapsed time to a "HH24:MM"
mask?

In what ways are you using the field
(Comparisons,calculations,showing only)?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:nitinpai
ID: 2811458
Hi,
  The problem definition is not clear. But from what I have interpreted, u have a column of number datatype which is actually storing the elapsed time. Now, u want to update the values so that it will be in the format hhmi. Ok. Then u can run the following statement:
update tmp
set elapsed_time = floor(elapsed_time/60)||mod(elapsed_time,60);

Note that u cannot have the format 0130 bcos the datatype is a number and not a character.

The above statement will update the value elapsed_time = 90 to elapsed_time = 130. However, if the elapsed_time = 1440 the value will be converted to 240. Now, if the elapsed_time is greater than 24 hrs, what format do u want it to be in?? Pl. give examples. The solution is always available.

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 2811701
Have you considered creating a view using a calculation like the one suggested by nitinpai, instead of altering your table?  This has the advantage of not requiring an update, and being able to display all of the records as if they had been updated.

Can you explain the advantage(s) you see in having the data stored as something other than a number?
0
 
LVL 1

Accepted Solution

by:
sohill earned 50 total points
ID: 2816420
use this sql :
select to_char(to_date(lpad(trunc(&1/60),2,'0')||lpad(MOD(&1,60),2,'0'),'HH24MI'),'HH24:MI')  
from dual

and subsitute &1 by your parameter, e.g. 90
0
 

Author Comment

by:tomatocans
ID: 2830674
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now