Timestamp before Epoch in a CSV file with GAWK

Question

Want to convert humanoid timestamps in an era / Unix time to a CSV file using GAWK to prepare for upload to a MySQL database.

Sample data:

{null};2013-11-26;Text & Device;Location;/file/path/to/;Tuesday, November 26 12:17 PM;1;1385845647 

Looking at column 6, Tuesday, November 26, 12:17 and convert to the era of time for storage. All times shown will be in EST format. I understand that AWK is a tool for this, but it seems that it cannot structure the team. Currently:

 cat FILE_IN.CSV | awk 'BEGIN {FS=OFS=";"}{$6=strftime("%s")} {print}' 

However, this returns:

 {null};2013-11-26;Text & Device;Location;/file/path/to/;1385848848;1;1385845647 

Presumably, this means that I am invoking the current era (1385848848 is the current era at runtime) and not asking strftime convert the string; but I cannot imagine another way to do this.

What is the correct syntax for gawk / strftime to convert an existing timestamp into an era?

Edit: this question seems to be related to How to use awk output in another command?

Answer 1

 $ cat file {null};2013-11-26;Text & Device;Location;/file/path/to/;Tuesday, November 26 12:17 PM;1;1385845647 $ gawk 'BEGIN{FS=OFS=";"} {gsub(/-/," ",$2); $2=mktime($2" 0 0 0")}1' file {null};1385445600;Text & Device;Location;/file/path/to/;Tuesday, November 26 12:17 PM;1;1385845647 

Here, as usual, convert the date from any format in seconds from the era using the current format as an example and with comments to show the conversion process step by step:

 $ cat tst.awk function cvttime(t, a) { split(t,a,/[,: ]+/) # 2013 Tuesday, November 26 10:17 PM # => # a[1] = "2013" # a[2] = "Tuesday" # a[3] = "November" # a[4] = "26" # a[5] = "10" # a[6] = "17" # a[7] = "PM" if ( (a[7] == "PM") && (a[5] < 12) ) { a[5] += 12 } # => a[5] = "22" a[3] = substr(a[3],1,3) # => a[3] = "Nov" match("JanFebMarAprMayJunJulAugSepOctNovDec",a[3]) a[3] = (RSTART+2)/3 # => a[3] = 11 return( mktime(a[1]" "a[3]" "a[4]" "a[5]" "a[6]" 0") ) } BEGIN { mdt ="Tuesday, November 26 10:17 PM" secs = cvttime(2013" "mdt) dt = strftime("%Y-%m-%d %H:%M:%S",secs) print mdt ORS "\t-> " secs ORS "\t\t-> " dt } $ awk -f tst.awk Tuesday, November 26 10:17 PM -> 1385525820 -> 2013-11-26 22:17:00 

I am sure you can change this for the current problem.

Alternatively, if you don't have gawk, you can write the cvttime () function as a string (borrowing @sputnik date ):

 $ cat tst2.awk function cvttime(t, cmd,secs) { cmd = "date -d \"" t "\" '+%s'" cmd | getline secs close(cmd) return secs } BEGIN { mdt ="Tuesday, November 26 10:17 PM" secs = cvttime(mdt) dt = strftime("%Y-%m-%d %H:%M:%S",secs) print mdt ORS "\t-> " secs ORS "\t\t-> " dt } $ $ awk -f tst2.awk Tuesday, November 26 10:17 PM -> 1385525820 -> 2013-11-26 22:17:00 

I left srtftime () just to show that secs is correct - replace date as you like.

For the non-gawk version, you just need to figure out how to get the year in the input month / date / time so that date understands whether this mask suits you - it should not be difficult.

Answer 2

You can convert a date to an era using this snippet:

 $ date -d 'Tuesday, November 26 12:17 PM' +%s 1385464620 

So finally:

 awk -F";" '{system("date -d \""$6"\" '+%s'")}' file 

Thanks @Keiron for the snippet.