Replace time column with the current time

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP











up vote
0
down vote

favorite












I have data in file portals.csv like



filename is portals.csv



ip,time,name
1.1.1.1,2018-08-15 11:05:28:268813353,1.13.0-0007
1.1.1.2,2018-08-16 11:05:32:016469121,1.13.0-0007
1.1.1.3,2018-08-16 11:06:42:316469121,1.13.0-0007
1.1.2.5,2018-08-16 11:15:52:416469121,1.13.0-0007


Need the output like this, the time column data should take the current time with different second for each and every rows



ip,time,name
1.1.1.1,2018-08-17 15:00:01,1.13.0-0007
1.1.1.2,2018-08-17 15:00:02,1.13.0-0007
1.1.1.3,2018-08-17 15:00:03,1.13.0-0007
1.1.2.5,2018-08-17 15:00:04,1.13.0-0007









share|improve this question



























    up vote
    0
    down vote

    favorite












    I have data in file portals.csv like



    filename is portals.csv



    ip,time,name
    1.1.1.1,2018-08-15 11:05:28:268813353,1.13.0-0007
    1.1.1.2,2018-08-16 11:05:32:016469121,1.13.0-0007
    1.1.1.3,2018-08-16 11:06:42:316469121,1.13.0-0007
    1.1.2.5,2018-08-16 11:15:52:416469121,1.13.0-0007


    Need the output like this, the time column data should take the current time with different second for each and every rows



    ip,time,name
    1.1.1.1,2018-08-17 15:00:01,1.13.0-0007
    1.1.1.2,2018-08-17 15:00:02,1.13.0-0007
    1.1.1.3,2018-08-17 15:00:03,1.13.0-0007
    1.1.2.5,2018-08-17 15:00:04,1.13.0-0007









    share|improve this question

























      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      I have data in file portals.csv like



      filename is portals.csv



      ip,time,name
      1.1.1.1,2018-08-15 11:05:28:268813353,1.13.0-0007
      1.1.1.2,2018-08-16 11:05:32:016469121,1.13.0-0007
      1.1.1.3,2018-08-16 11:06:42:316469121,1.13.0-0007
      1.1.2.5,2018-08-16 11:15:52:416469121,1.13.0-0007


      Need the output like this, the time column data should take the current time with different second for each and every rows



      ip,time,name
      1.1.1.1,2018-08-17 15:00:01,1.13.0-0007
      1.1.1.2,2018-08-17 15:00:02,1.13.0-0007
      1.1.1.3,2018-08-17 15:00:03,1.13.0-0007
      1.1.2.5,2018-08-17 15:00:04,1.13.0-0007









      share|improve this question















      I have data in file portals.csv like



      filename is portals.csv



      ip,time,name
      1.1.1.1,2018-08-15 11:05:28:268813353,1.13.0-0007
      1.1.1.2,2018-08-16 11:05:32:016469121,1.13.0-0007
      1.1.1.3,2018-08-16 11:06:42:316469121,1.13.0-0007
      1.1.2.5,2018-08-16 11:15:52:416469121,1.13.0-0007


      Need the output like this, the time column data should take the current time with different second for each and every rows



      ip,time,name
      1.1.1.1,2018-08-17 15:00:01,1.13.0-0007
      1.1.1.2,2018-08-17 15:00:02,1.13.0-0007
      1.1.1.3,2018-08-17 15:00:03,1.13.0-0007
      1.1.2.5,2018-08-17 15:00:04,1.13.0-0007






      text-processing






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Sep 7 at 10:02









      Rui F Ribeiro

      36.7k1271116




      36.7k1271116










      asked Aug 17 at 10:36









      Syed Rizvi

      104




      104




















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          1
          down vote













          Here's an option using GNU Awk, using NR to increment an epoch timestamp passed in from the date command:



          gawk -F, -v ts="$(date +%s)" '
          BEGINOFS = FS
          NR > 1 $2 = strftime("%Y-%m-%d %H:%M:%S", ts + NR - 1)
          1' portals.csv
          ip,time,name
          1.1.1.1,2018-08-17 09:47:17,1.13.0-0007
          1.1.1.2,2018-08-17 09:47:18,1.13.0-0007
          1.1.1.3,2018-08-17 09:47:19,1.13.0-0007
          1.1.2.5,2018-08-17 09:47:20,1.13.0-0007


          See The GNU Awk User's Guide: Time functions




          Similar approach in Perl:



          perl -MPOSIX -F, -lne '
          BEGIN$ts = time();
          $F[1] = strftime("%Y-%m-%d %H:%M:%S", localtime($ts + $. - 1)) if $. > 1;
          print join ",", @F
          ' portals.csv





          share|improve this answer





























            up vote
            0
            down vote













            Dealing with date / time fields never is an easy thing, so for a full blown solution, way more effort must be spent. If your input file has less than 60 lines, try



            awk -F, -vDT="$(date +"%F %T")" 'NR > 1 sub (/:..$/, sprintf (":%02d", NR-1), DT); $2 = DT 1' OFS=, file
            ip,time,name
            1.1.1.1,2018-08-17 12:47:01,1.13.0-0007
            1.1.1.2,2018-08-17 12:47:02,1.13.0-0007
            1.1.1.1,2018-08-17 12:47:03,1.13.0-0007
            1.1.1.2,2018-08-17 12:47:04,1.13.0-0007
            1.1.1.3,2018-08-17 12:47:05,1.13.0-0007
            1.1.2.5,2018-08-17 12:47:06,1.13.0-0007


            or, slightly shorter,



            awk -F, -vDT="$(date +"%F %H:%M")" 'NR > 1 $2 = DT sprintf (":%02d", NR-1) 1' OFS=, file


            EDIT: For your additional request to extend this for up to 99 lines in the input file, try



            awk -F, -vDT="$(date +"%F %H:")" 'NR > 1 $2 = DT sprintf ("%02d:%02d", int((NR-1)/60), (NR-1)%60) 1' OFS=, file





            share|improve this answer






















            • Thanks RudiC, it is working but the file is having more than 60 lines, so it is showing seconds value after 59th rows showing incorrect. Can't we increase the value as time, like after 00:00:59 it show 00:01:00 likewise for all the coming new rows.
              – Syed Rizvi
              Aug 17 at 10:58










            • Yes, and after 3600 seconds roll over to the hours, and after 86400 seconds to days. What to do at year end, and what in leap years? There are algorithms dealing with date / time arithmetics in these and other fora.
              – RudiC
              Aug 17 at 11:39











            • The total rows coming in file is 99 only, is there any way so that it will work like that.
              – Syed Rizvi
              Aug 17 at 12:05










            • Any attempt / idea / thought from your side?
              – RudiC
              Aug 17 at 12:14










            Your Answer







            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "106"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            convertImagesToLinks: false,
            noModals: false,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













             

            draft saved


            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f463161%2freplace-time-column-with-the-current-time%23new-answer', 'question_page');

            );

            Post as a guest






























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            1
            down vote













            Here's an option using GNU Awk, using NR to increment an epoch timestamp passed in from the date command:



            gawk -F, -v ts="$(date +%s)" '
            BEGINOFS = FS
            NR > 1 $2 = strftime("%Y-%m-%d %H:%M:%S", ts + NR - 1)
            1' portals.csv
            ip,time,name
            1.1.1.1,2018-08-17 09:47:17,1.13.0-0007
            1.1.1.2,2018-08-17 09:47:18,1.13.0-0007
            1.1.1.3,2018-08-17 09:47:19,1.13.0-0007
            1.1.2.5,2018-08-17 09:47:20,1.13.0-0007


            See The GNU Awk User's Guide: Time functions




            Similar approach in Perl:



            perl -MPOSIX -F, -lne '
            BEGIN$ts = time();
            $F[1] = strftime("%Y-%m-%d %H:%M:%S", localtime($ts + $. - 1)) if $. > 1;
            print join ",", @F
            ' portals.csv





            share|improve this answer


























              up vote
              1
              down vote













              Here's an option using GNU Awk, using NR to increment an epoch timestamp passed in from the date command:



              gawk -F, -v ts="$(date +%s)" '
              BEGINOFS = FS
              NR > 1 $2 = strftime("%Y-%m-%d %H:%M:%S", ts + NR - 1)
              1' portals.csv
              ip,time,name
              1.1.1.1,2018-08-17 09:47:17,1.13.0-0007
              1.1.1.2,2018-08-17 09:47:18,1.13.0-0007
              1.1.1.3,2018-08-17 09:47:19,1.13.0-0007
              1.1.2.5,2018-08-17 09:47:20,1.13.0-0007


              See The GNU Awk User's Guide: Time functions




              Similar approach in Perl:



              perl -MPOSIX -F, -lne '
              BEGIN$ts = time();
              $F[1] = strftime("%Y-%m-%d %H:%M:%S", localtime($ts + $. - 1)) if $. > 1;
              print join ",", @F
              ' portals.csv





              share|improve this answer
























                up vote
                1
                down vote










                up vote
                1
                down vote









                Here's an option using GNU Awk, using NR to increment an epoch timestamp passed in from the date command:



                gawk -F, -v ts="$(date +%s)" '
                BEGINOFS = FS
                NR > 1 $2 = strftime("%Y-%m-%d %H:%M:%S", ts + NR - 1)
                1' portals.csv
                ip,time,name
                1.1.1.1,2018-08-17 09:47:17,1.13.0-0007
                1.1.1.2,2018-08-17 09:47:18,1.13.0-0007
                1.1.1.3,2018-08-17 09:47:19,1.13.0-0007
                1.1.2.5,2018-08-17 09:47:20,1.13.0-0007


                See The GNU Awk User's Guide: Time functions




                Similar approach in Perl:



                perl -MPOSIX -F, -lne '
                BEGIN$ts = time();
                $F[1] = strftime("%Y-%m-%d %H:%M:%S", localtime($ts + $. - 1)) if $. > 1;
                print join ",", @F
                ' portals.csv





                share|improve this answer














                Here's an option using GNU Awk, using NR to increment an epoch timestamp passed in from the date command:



                gawk -F, -v ts="$(date +%s)" '
                BEGINOFS = FS
                NR > 1 $2 = strftime("%Y-%m-%d %H:%M:%S", ts + NR - 1)
                1' portals.csv
                ip,time,name
                1.1.1.1,2018-08-17 09:47:17,1.13.0-0007
                1.1.1.2,2018-08-17 09:47:18,1.13.0-0007
                1.1.1.3,2018-08-17 09:47:19,1.13.0-0007
                1.1.2.5,2018-08-17 09:47:20,1.13.0-0007


                See The GNU Awk User's Guide: Time functions




                Similar approach in Perl:



                perl -MPOSIX -F, -lne '
                BEGIN$ts = time();
                $F[1] = strftime("%Y-%m-%d %H:%M:%S", localtime($ts + $. - 1)) if $. > 1;
                print join ",", @F
                ' portals.csv






                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Aug 17 at 14:10

























                answered Aug 17 at 13:16









                steeldriver

                32.2k34979




                32.2k34979






















                    up vote
                    0
                    down vote













                    Dealing with date / time fields never is an easy thing, so for a full blown solution, way more effort must be spent. If your input file has less than 60 lines, try



                    awk -F, -vDT="$(date +"%F %T")" 'NR > 1 sub (/:..$/, sprintf (":%02d", NR-1), DT); $2 = DT 1' OFS=, file
                    ip,time,name
                    1.1.1.1,2018-08-17 12:47:01,1.13.0-0007
                    1.1.1.2,2018-08-17 12:47:02,1.13.0-0007
                    1.1.1.1,2018-08-17 12:47:03,1.13.0-0007
                    1.1.1.2,2018-08-17 12:47:04,1.13.0-0007
                    1.1.1.3,2018-08-17 12:47:05,1.13.0-0007
                    1.1.2.5,2018-08-17 12:47:06,1.13.0-0007


                    or, slightly shorter,



                    awk -F, -vDT="$(date +"%F %H:%M")" 'NR > 1 $2 = DT sprintf (":%02d", NR-1) 1' OFS=, file


                    EDIT: For your additional request to extend this for up to 99 lines in the input file, try



                    awk -F, -vDT="$(date +"%F %H:")" 'NR > 1 $2 = DT sprintf ("%02d:%02d", int((NR-1)/60), (NR-1)%60) 1' OFS=, file





                    share|improve this answer






















                    • Thanks RudiC, it is working but the file is having more than 60 lines, so it is showing seconds value after 59th rows showing incorrect. Can't we increase the value as time, like after 00:00:59 it show 00:01:00 likewise for all the coming new rows.
                      – Syed Rizvi
                      Aug 17 at 10:58










                    • Yes, and after 3600 seconds roll over to the hours, and after 86400 seconds to days. What to do at year end, and what in leap years? There are algorithms dealing with date / time arithmetics in these and other fora.
                      – RudiC
                      Aug 17 at 11:39











                    • The total rows coming in file is 99 only, is there any way so that it will work like that.
                      – Syed Rizvi
                      Aug 17 at 12:05










                    • Any attempt / idea / thought from your side?
                      – RudiC
                      Aug 17 at 12:14














                    up vote
                    0
                    down vote













                    Dealing with date / time fields never is an easy thing, so for a full blown solution, way more effort must be spent. If your input file has less than 60 lines, try



                    awk -F, -vDT="$(date +"%F %T")" 'NR > 1 sub (/:..$/, sprintf (":%02d", NR-1), DT); $2 = DT 1' OFS=, file
                    ip,time,name
                    1.1.1.1,2018-08-17 12:47:01,1.13.0-0007
                    1.1.1.2,2018-08-17 12:47:02,1.13.0-0007
                    1.1.1.1,2018-08-17 12:47:03,1.13.0-0007
                    1.1.1.2,2018-08-17 12:47:04,1.13.0-0007
                    1.1.1.3,2018-08-17 12:47:05,1.13.0-0007
                    1.1.2.5,2018-08-17 12:47:06,1.13.0-0007


                    or, slightly shorter,



                    awk -F, -vDT="$(date +"%F %H:%M")" 'NR > 1 $2 = DT sprintf (":%02d", NR-1) 1' OFS=, file


                    EDIT: For your additional request to extend this for up to 99 lines in the input file, try



                    awk -F, -vDT="$(date +"%F %H:")" 'NR > 1 $2 = DT sprintf ("%02d:%02d", int((NR-1)/60), (NR-1)%60) 1' OFS=, file





                    share|improve this answer






















                    • Thanks RudiC, it is working but the file is having more than 60 lines, so it is showing seconds value after 59th rows showing incorrect. Can't we increase the value as time, like after 00:00:59 it show 00:01:00 likewise for all the coming new rows.
                      – Syed Rizvi
                      Aug 17 at 10:58










                    • Yes, and after 3600 seconds roll over to the hours, and after 86400 seconds to days. What to do at year end, and what in leap years? There are algorithms dealing with date / time arithmetics in these and other fora.
                      – RudiC
                      Aug 17 at 11:39











                    • The total rows coming in file is 99 only, is there any way so that it will work like that.
                      – Syed Rizvi
                      Aug 17 at 12:05










                    • Any attempt / idea / thought from your side?
                      – RudiC
                      Aug 17 at 12:14












                    up vote
                    0
                    down vote










                    up vote
                    0
                    down vote









                    Dealing with date / time fields never is an easy thing, so for a full blown solution, way more effort must be spent. If your input file has less than 60 lines, try



                    awk -F, -vDT="$(date +"%F %T")" 'NR > 1 sub (/:..$/, sprintf (":%02d", NR-1), DT); $2 = DT 1' OFS=, file
                    ip,time,name
                    1.1.1.1,2018-08-17 12:47:01,1.13.0-0007
                    1.1.1.2,2018-08-17 12:47:02,1.13.0-0007
                    1.1.1.1,2018-08-17 12:47:03,1.13.0-0007
                    1.1.1.2,2018-08-17 12:47:04,1.13.0-0007
                    1.1.1.3,2018-08-17 12:47:05,1.13.0-0007
                    1.1.2.5,2018-08-17 12:47:06,1.13.0-0007


                    or, slightly shorter,



                    awk -F, -vDT="$(date +"%F %H:%M")" 'NR > 1 $2 = DT sprintf (":%02d", NR-1) 1' OFS=, file


                    EDIT: For your additional request to extend this for up to 99 lines in the input file, try



                    awk -F, -vDT="$(date +"%F %H:")" 'NR > 1 $2 = DT sprintf ("%02d:%02d", int((NR-1)/60), (NR-1)%60) 1' OFS=, file





                    share|improve this answer














                    Dealing with date / time fields never is an easy thing, so for a full blown solution, way more effort must be spent. If your input file has less than 60 lines, try



                    awk -F, -vDT="$(date +"%F %T")" 'NR > 1 sub (/:..$/, sprintf (":%02d", NR-1), DT); $2 = DT 1' OFS=, file
                    ip,time,name
                    1.1.1.1,2018-08-17 12:47:01,1.13.0-0007
                    1.1.1.2,2018-08-17 12:47:02,1.13.0-0007
                    1.1.1.1,2018-08-17 12:47:03,1.13.0-0007
                    1.1.1.2,2018-08-17 12:47:04,1.13.0-0007
                    1.1.1.3,2018-08-17 12:47:05,1.13.0-0007
                    1.1.2.5,2018-08-17 12:47:06,1.13.0-0007


                    or, slightly shorter,



                    awk -F, -vDT="$(date +"%F %H:%M")" 'NR > 1 $2 = DT sprintf (":%02d", NR-1) 1' OFS=, file


                    EDIT: For your additional request to extend this for up to 99 lines in the input file, try



                    awk -F, -vDT="$(date +"%F %H:")" 'NR > 1 $2 = DT sprintf ("%02d:%02d", int((NR-1)/60), (NR-1)%60) 1' OFS=, file






                    share|improve this answer














                    share|improve this answer



                    share|improve this answer








                    edited Aug 17 at 12:20

























                    answered Aug 17 at 10:49









                    RudiC

                    1,2167




                    1,2167











                    • Thanks RudiC, it is working but the file is having more than 60 lines, so it is showing seconds value after 59th rows showing incorrect. Can't we increase the value as time, like after 00:00:59 it show 00:01:00 likewise for all the coming new rows.
                      – Syed Rizvi
                      Aug 17 at 10:58










                    • Yes, and after 3600 seconds roll over to the hours, and after 86400 seconds to days. What to do at year end, and what in leap years? There are algorithms dealing with date / time arithmetics in these and other fora.
                      – RudiC
                      Aug 17 at 11:39











                    • The total rows coming in file is 99 only, is there any way so that it will work like that.
                      – Syed Rizvi
                      Aug 17 at 12:05










                    • Any attempt / idea / thought from your side?
                      – RudiC
                      Aug 17 at 12:14
















                    • Thanks RudiC, it is working but the file is having more than 60 lines, so it is showing seconds value after 59th rows showing incorrect. Can't we increase the value as time, like after 00:00:59 it show 00:01:00 likewise for all the coming new rows.
                      – Syed Rizvi
                      Aug 17 at 10:58










                    • Yes, and after 3600 seconds roll over to the hours, and after 86400 seconds to days. What to do at year end, and what in leap years? There are algorithms dealing with date / time arithmetics in these and other fora.
                      – RudiC
                      Aug 17 at 11:39











                    • The total rows coming in file is 99 only, is there any way so that it will work like that.
                      – Syed Rizvi
                      Aug 17 at 12:05










                    • Any attempt / idea / thought from your side?
                      – RudiC
                      Aug 17 at 12:14















                    Thanks RudiC, it is working but the file is having more than 60 lines, so it is showing seconds value after 59th rows showing incorrect. Can't we increase the value as time, like after 00:00:59 it show 00:01:00 likewise for all the coming new rows.
                    – Syed Rizvi
                    Aug 17 at 10:58




                    Thanks RudiC, it is working but the file is having more than 60 lines, so it is showing seconds value after 59th rows showing incorrect. Can't we increase the value as time, like after 00:00:59 it show 00:01:00 likewise for all the coming new rows.
                    – Syed Rizvi
                    Aug 17 at 10:58












                    Yes, and after 3600 seconds roll over to the hours, and after 86400 seconds to days. What to do at year end, and what in leap years? There are algorithms dealing with date / time arithmetics in these and other fora.
                    – RudiC
                    Aug 17 at 11:39





                    Yes, and after 3600 seconds roll over to the hours, and after 86400 seconds to days. What to do at year end, and what in leap years? There are algorithms dealing with date / time arithmetics in these and other fora.
                    – RudiC
                    Aug 17 at 11:39













                    The total rows coming in file is 99 only, is there any way so that it will work like that.
                    – Syed Rizvi
                    Aug 17 at 12:05




                    The total rows coming in file is 99 only, is there any way so that it will work like that.
                    – Syed Rizvi
                    Aug 17 at 12:05












                    Any attempt / idea / thought from your side?
                    – RudiC
                    Aug 17 at 12:14




                    Any attempt / idea / thought from your side?
                    – RudiC
                    Aug 17 at 12:14

















                     

                    draft saved


                    draft discarded















































                     


                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f463161%2freplace-time-column-with-the-current-time%23new-answer', 'question_page');

                    );

                    Post as a guest













































































                    Popular posts from this blog

                    How to check contact read email or not when send email to Individual?

                    Displaying single band from multi-band raster using QGIS

                    How many registers does an x86_64 CPU actually have?