Home » SQL & PL/SQL » SQL & PL/SQL » Converting Minutes to Quarter Hours
Converting Minutes to Quarter Hours [message #319030] Thu, 08 May 2008 14:55 Go to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
Looking for input on how to convert minutes to quarter hours.
For example, if the data retrieved is:

12:00, 12:15, 12:30, 12:45

then my user wants the data I am dumping for them to show as:

12.00 12.25, 12.50, 12.75

The column that I am pulling from is stored in total seconds, thus it is coming from for 12:15 is stored in the database as 44100.

I am using the following to convert the total seconds into a standard hours:minutes format:

to_char(to_date(TIMEINSECONDS,'sssss'),'hh24:mi')

but I need the minutes actually converted into quarter hours

Any thoughts or ideas?

Thanks!
Re: Converting Minutes to Quarter Hours [message #319033 is a reply to message #319030] Thu, 08 May 2008 15:04 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
But what about all the other times that do not fall into :00, :15, :30 and :45?
Re: Converting Minutes to Quarter Hours [message #319034 is a reply to message #319030] Thu, 08 May 2008 15:05 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
It looks like the systme that this data gets generated from only writes it in 15 minute increments. Thus it would always be :00, :15, :30, :45
Re: Converting Minutes to Quarter Hours [message #319036 is a reply to message #319034] Thu, 08 May 2008 15:17 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Then I think a simple DECODE or CASE on the minutes part of the date might be the best way to go here.
Re: Converting Minutes to Quarter Hours [message #319038 is a reply to message #319030] Thu, 08 May 2008 15:22 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
How would I go about inserting the CASE statement for this?

Below is the code I'm currently running:

 
select PERSONFULLNAME, 
       PERSONCSTMDATATXT, 
       HOMELABORLEVELNM3,
       DESCRIPTION,
       to_char(to_date(TIMEINSECONDS,'sssss'),'hh24:mi'),
  case when HOMELABORLEVELNM3 = LABORLEVELNAME3
       then ' '
       else   'X'
   end as TRANSFER
from kronread.vwuabnurses
Re: Converting Minutes to Quarter Hours [message #319039 is a reply to message #319030] Thu, 08 May 2008 15:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the data are generated every 15 minutes then what is your problem?

Regards
Michel


Re: Converting Minutes to Quarter Hours [message #319041 is a reply to message #319030] Thu, 08 May 2008 15:39 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As you store 'time' as number of seconds, you may show hours unchanged (only 'hh24' format mask). Alternatively it may be the integer result of division by 60 (FLOOR function).

Counting minutes is simple mathematics.
First, calculate, how many minutes are in the last hour. As each hour has 60 minutes, it is simple Modulo by 60 (MOD function).
Converting it to desired figure can be done by using Rule of three method.

00 min -> 00
30 min -> 50
c min -> ...

Alternatively you may use CASE/DECODE instead of this (but this approach is not more complex).
Re: Converting Minutes to Quarter Hours [message #319045 is a reply to message #319030] Thu, 08 May 2008 16:09 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
OK, I created the following code, and the CASE statement for converting the MINUTES into QUARTER HOURS works fine by itself

select PERSONFULLNAME, 
       PERSONCSTMDATATXT, 
       HOMELABORLEVELNM3,
       DESCRIPTION,
       to_char(to_date(TIMEINSECONDS,'sssss'),'hh24:') as HOURS,
       CASE WHEN to_char(to_date              
               (TIMEINSECONDS,'sssss'),'mi') = '15' THEN '25' 
	        WHEN to_char(to_date 
               (TIMEINSECONDS,'sssss'),'mi') = '30' THEN '50' 
	        WHEN to_char(to_date 
               (TIMEINSECONDS,'sssss'),'mi') = '45' THEN '75' 
	ELSE                                             '00' 
	END AS MINS
	CASE WHEN HOMELABORLEVELNM3 = LABORLEVELNAME3 THEN ' '
	ELSE                                               'X'
	END AS TRANSFERED
from kronread.vwuabnurses


however, I am getting an error message:

ORA-923 FROM keyword not found where expected

this occurs when I include the second CASE statement <where it's comparing HOMELABORLEVELNM3 = LABORLEVELNAME3>, if i remove either of the CASE statements and only have 1, then it works fine. can I only have 1 CASE statement
Re: Converting Minutes to Quarter Hours [message #319046 is a reply to message #319030] Thu, 08 May 2008 16:15 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> if i remove either of the CASE statements and only have 1, then it works fine.

If 1 is followed by comma (,), it shall be fine.
If case statement would be followed by comma ...
Re: Converting Minutes to Quarter Hours [message #319047 is a reply to message #319030] Thu, 08 May 2008 16:18 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
yeah I just saw that.
missing , will do it every time!
thanks!
Re: Converting Minutes to Quarter Hours [message #319074 is a reply to message #319047] Thu, 08 May 2008 23:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why restrict yourself now to the four quarters? Maybe in the future your code will allow a 20 minutes entry.

Divide the number of minutes by 60, multiply by 100, round it and you got your desired result!
SQL> select to_char(sysdate, 'hh24:mi') current_time
  2  ,      round(to_number(to_char(sysdate, 'mi'))/.6, 0) minutes_fraction
  3  from   dual;

CURRE MINUTES_FRACTION
----- ----------------
06:40               67
Previous Topic: generate fixed format file
Next Topic: Cursor error
Goto Forum:
  


Current Time: Sat Dec 03 04:12:10 CST 2016

Total time taken to generate the page: 0.04939 seconds