0

I have values in 2 different columns like this

Column picture

How do I gather it in to one single column like this 

Result_column
14:08
09:36
19:07
11:11

Date will remain in its separate column. How to achieve this. I want to do this becoz I have time in other table in this format 19:11:38 and I want to compare both.

PriyalChaudhari
  • 363
  • 1
  • 7
  • 23

3 Answers3

0

Use a group of CONCAT function.

Query

select 
concat(
    concat(
        case when click_hr < 10 
        then concat('0', cast(click_hr  as varchar(100))) 
        else cast(click_hr as varchar(100)) end,
       ':')
   , case when click_min < 10 
   then concat('0', cast(click_min as varchar(100))) 
   else cast(click_min as varchar(100)) end) as result
from your_table_name;
Ullas
  • 11,450
  • 4
  • 33
  • 50
0

If you need the resultant value to be in time format i.e. hh:mi:ss, below could be an option.

SELECT click_date,
       to_char(to_Date(click_hr || to_char(click_min, '09') || 00, 'hh24miss'), 'hh24:mi:ss') AS TIME
FROM table1

Result:

CLICK_DATE              TIME
--------------------------------
17.10.2017 00:00:00     14:08:00
19.10.2017 00:00:00     09:36:00
24.10.2017 00:00:00     19:07:00

DEMO

zarruq
  • 2,445
  • 2
  • 10
  • 19
0

A DATE data type always has a year, month, day, hour, minute and second components - your IDE is probably just not showing them to you.

Do not store the values as separate columns, store them in the date:

CREATE TABLE table_name( click_date ) AS
SELECT TO_DATE( '2017-10-10 14:08:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2017-10-19 09:36:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2017-10-24 19:07:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2017-09-29 11:11:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2017-10-02 18:18:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2017-09-27 12:08:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2017-10-05 10:37:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2017-10-05 11:43:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2017-10-14 12:41:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;

Then you can just do:

SELECT TO_CHAR( click_date, 'HH24:MI' ) AS time
FROM   table_name

or, to get the value as an interval:

SELECT click_date - TRUNC( click_date ) AS time
FROM   table_name

With your current table structure you can get an interval as:

SELECT NUMTODSINTERVAL( click_hr, 'HOUR' )
       + NUMTODSINTERVAL( click_min, 'MINUTE' ) AS time
FROM   table_name

Or, as a string:

SELECT TO_CHAR( click_hr, '00' ) || ':' || TO_CHAR( click_min, '00' ) AS time
FROM   table_name
MT0
  • 143,790
  • 11
  • 59
  • 117