Forum Stats

  • 3,872,222 Users
  • 2,266,407 Discussions
  • 7,911,107 Comments

Discussions

Add hours and minutes in char column

SHR_ORAC
SHR_ORAC Member Posts: 15
edited Jan 9, 2018 12:52PM in SQL & PL/SQL

Hi

i hv a char column A

i have time stored in that column like 09:45, 10:01 etc.

Now i want to derive a new column B by adding 08:30 i.e 8 hours and 30 mins.

pls help..

Tagged:
SHR_ORACFrank Kulash

Best Answer

  • mathguy
    mathguy Member Posts: 10,875 Black Diamond
    edited Jan 9, 2018 11:01AM Answer ✓

    I will not comment on the wisdom of this whole thing (if you can't say something good, say nothing at all), but I think you can do this easily, if you really need to.

    TO_DATE with an incomplete format model will use some defaults for year, month and day, but you don't need to know what they are. Then you add 8 hours and 30 minutes, and convert back, with the same (incomplete) format model, 'HH24:MI'.

    with
      inputs ( str ) as (
        select '09:30' from dual union all
        select '18:15' from dual
      )
    select str, to_char(to_date(str, 'hh24:mi') + interval '08:30' hour to minute, 'hh24:mi') as new_str
    from  inputs
    ;STR    NEW_STR
    -----  -------
    09:30  18:00
    18:15  02:45
    SHR_ORAC
«1

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jan 9, 2018 10:28AM
    SHR_ORAC wrote:Hii hv a char column Ai have time stored in that column like 09:45, 10:01 etc.Now i want to derive a new column B by adding 08:30 i.e 8 hours and 30 mins.pls help..

    Your first problem is that you have a serious design flaw in storing "time" in a varchar. It should be stored in a DATE column

    Are these times actually a time of the day, or are they "elapsed time".

  • SHR_ORAC
    SHR_ORAC Member Posts: 15
    edited Jan 9, 2018 10:30AM

    They are time of day in 24:00 hr format

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,720 Red Diamond
    edited Jan 9, 2018 10:35AM

    Hi,

    SHR_ORAC wrote:Hii hv a char column Ai have time stored in that column like 09:45, 10:01 etc.Now i want to derive a new column B by adding 08:30 i.e 8 hours and 30 mins.pls help..

    One way to do that is to use Oracle's Date Arithmetic.

    If d is a DATE, and h and m are NUMBERs, then

    d + (h / 24) + (m / (24 * 60))

    is the DATE h hours and m minutes after d.

    If you're starting with a string (such as CHAR or VARCHAR2), you can use TO_DATE to convert it to a DATE, add the hours and minutes, and then use TO_CHAR to convert the result back to a string.

  • SHR_ORAC
    SHR_ORAC Member Posts: 15
    edited Jan 9, 2018 10:35AM

    It is a char column, i wish  to add hours and minues like 8 hr 30 mins and calculate the new column..

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,720 Red Diamond
    edited Jan 9, 2018 10:39AM

    Hi,

    If you're having trouble, post a little sample data (CREATE TABLE and INSERT statements), and the exact results you want from that sample data.  Include examples of any special cases you need to handle (e.g., NULLs, operations that span days, negative hours, ...).

    See the Forum FAQ:

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Jan 9, 2018 10:40AM

    what happens if the new time is after midnight?

  • SHR_ORAC
    SHR_ORAC Member Posts: 15
    edited Jan 9, 2018 10:42AM

    Normally the time would be like I have given in my question

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Jan 9, 2018 10:46AM

    Please READ the thread -->

    Then provide your table and data (i.e. DDL, and DML) statements and show EXACTLY the "before" and "after" states of your table data. 

    And when people ask you a question (like Cookiemonster76 just did), give MORE information.

    And like Ed and others have mentioned..........WHY are you storing date/time as a string (i.e. VARCHAR)??

  • Mike Kutz
    Mike Kutz Member Posts: 6,251 Gold Crown
    edited Jan 9, 2018 10:48AM
    SHR_ORAC wrote:It is a char column, i wish to add hours and minues like 8 hr 30 mins and calculate the new column..

    Preferred Steps

    • Data is stored in the appropriate format (eg DATE)
    • Calculation is done on the data as-is
    • Data is already in the appropriate format

    Steps for you:

    • Convert the CHAR data type to something that is useful
    • Hope all the data is correct
    • Do the calculation
    • Return the result as a CHAR.
    • Future developers go WTF?

    One method - uses INTERVAL

    with data as (  select '09:45' hr_time from dual union all  select '10:05' hr_time from dual union all  select '21:22' hr_time from dual), converted as (  select hr_time    ,to_dsinterval( '0 ' || hr_time || ':00' ) + to_dsinterval( '0 08:30:00') converted_time  from data)select hr_time,  trim( regexp_substr( converted_time, ' [[:digit:]]{2}:[[:digit:]]{2}') ) new_timefrom converted

    MK

    SHR_ORACSHR_ORAC
  • mathguy
    mathguy Member Posts: 10,875 Black Diamond
    edited Jan 9, 2018 11:01AM Answer ✓

    I will not comment on the wisdom of this whole thing (if you can't say something good, say nothing at all), but I think you can do this easily, if you really need to.

    TO_DATE with an incomplete format model will use some defaults for year, month and day, but you don't need to know what they are. Then you add 8 hours and 30 minutes, and convert back, with the same (incomplete) format model, 'HH24:MI'.

    with
      inputs ( str ) as (
        select '09:30' from dual union all
        select '18:15' from dual
      )
    select str, to_char(to_date(str, 'hh24:mi') + interval '08:30' hour to minute, 'hh24:mi') as new_str
    from  inputs
    ;STR    NEW_STR
    -----  -------
    09:30  18:00
    18:15  02:45
    SHR_ORAC
This discussion has been closed.