5 Replies Latest reply: Jun 21, 2013 4:05 AM by user3257267 RSS

    Incremental time based on date column

    947561

      Hi,

       

      My database is 11g. I want to generate incremental time column as per date column. Please help:

      eg:

      SQL> select date from emp order by 1;
      
      
      DATE
      ----------
      02/12/2012
      02/12/2012
      02/12/2012
      02/12/2012
      24/12/2012
      24/12/2012
      24/12/2012
      

      I want to add values to a time column containing 6 digit values (hhmmss) as follows:


      SQL> select date, time from emp;
      
      DATE               TIME
      ----------------------------------------
      02/12/2012        000000
      02/12/2012        000001
      02/12/2012        000002
      02/12/2012        000003
      24/12/2012        000000
      24/12/2012        000001
      24/12/2012        000002
      .
      .
      .
      24/12/2012      000059
      24/12/2012        000100
      

       

      The time should reset as the date changes.

        • 1. Re: Incremental time based on date column
          Purvesh K

          Not sure if I understand the actual requirement.

           

          But perhaps, you are trying to assign Row numbers to each Occurance of Date.

           

           

           

          with data as
          (
            select to_date('02-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('02-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('02-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('02-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual union all
            select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
          )
          select dt, to_char(row_number() over (partition by dt order by dt), 'FM099999') rn
            from data;
          
          DT                        RN      
          ------------------------- ------- 
          02-DEC-12                 000001  
          02-DEC-12                 000002  
          02-DEC-12                 000003  
          02-DEC-12                 000004  
          24-DEC-12                 000001  
          24-DEC-12                 000002  
          24-DEC-12                 000003  
          24-DEC-12                 000004  
          24-DEC-12                 000005  
          24-DEC-12                 000006  
          24-DEC-12                 000007  
          24-DEC-12                 000008  
          24-DEC-12                 000009  
          24-DEC-12                 000010  
          24-DEC-12                 000011  
           15 rows selected 
          

           

           

          
          
          • 2. Re: Incremental time based on date column
            Karthick_Arp

            Why are you storing DATE and TIME as two different columns? So is TIME column is of VARCHAR2 datatype?

             

            You can do this

             

            merge into emp e

            using (

                    select empid

                         , to_char(extract(hour   from numtodsinterval(rno, 'second')), 'fm09')

                        || to_char(extract(minute from numtodsinterval(rno, 'second')), 'fm09')

                        || to_char(extract(second from numtodsinterval(rno, 'second')), 'fm09') time_value

                      from (

                              select empid

                                   , row_number() over(partition by date_column order by empid) rno

                                from emp

                           )

                  ) t

               on (

                     e.empid = t.empid

                  )

            when matched then

                update set e.time_column = t.time_value;

            • 3. Re: Incremental time based on date column
              Karthick_Arp

              PurveshK wrote:

               

              Not sure if I understand the actual requirement.

               

              But perhaps, you are trying to assign Row numbers to each Occurance of Date.

               

               with data as 
              (  
              select to_date('02-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('02-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('02-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('02-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              union all   select to_date('24-12-2012', 'DD-MM-YYYY') dt from dual
              )
              select dt
                , to_char(row_number() over (partition by dt order by dt), 'FM099999') rn  
                from data;

              DT                        RN     
              ------------------------- -------
              02-DEC-12                 000001 
              02-DEC-12                 000002 
              02-DEC-12                 000003 
              02-DEC-12                 000004 
              24-DEC-12                 000001 
              24-DEC-12                 000002 
              24-DEC-12                 000003 
              24-DEC-12                 000004 
              24-DEC-12                 000005 
              24-DEC-12                 000006 
              24-DEC-12                 000007 
              24-DEC-12                 000008 
              24-DEC-12                 000009 
              24-DEC-12                 000010 
              24-DEC-12                 000011 

              15 rows selected

              OP wants to assign TIME. For example row number value 150 will be represented like this

               

              select to_char(extract(hour   from numtodsinterval(150, 'second')), 'fm09')
                  || to_char(extract(minute from numtodsinterval(150, 'second')), 'fm09')
                  || to_char(extract(second from numtodsinterval(150, 'second')), 'fm09') time_value
                from dual;

               

              TIME_VALUE
              ----------
              000230

              • 4. Re: Incremental time based on date column
                947561

                Hi, The solution by Karthick worked well. Yes, TIME column was of VARCHAR2 datatype.


                Thanks Everyone.

                • 5. Re: Incremental time based on date column
                  user3257267

                  Let see whether the query below fulfill your requirements.

                   

                   

                  select to_char(to_date('21-JUN-2013 03:44:55','DD-MON-YYYY HH:MI:SS')+rownum/24/60/60,'HHMISS') t_date

                  from a_table