5 Replies Latest reply on Jun 21, 2013 9:05 AM by user3257267

    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
            Karthick2003

            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
              Karthick2003

              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