0

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
1

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)" '
  BEGIN{OFS = 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 
0

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
  • 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 '18 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 '18 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 '18 at 12:05
  • Any attempt / idea / thought from your side? – RudiC Aug 17 '18 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.