Convert date to epoch time using AWK in linux

Question

I have a comma-separated file, with the first column as a date of the format 01/31/2010 that I want to change into epoch time, such that the file "file.csv":

 01/30/2010,1,"hi"
 01/31/2010,3,"bye"

will change into "output.csv":

 1264809600,1,"hi"
 1264896000,3,"bye"

I know the command line date -d "01/30/2010" +%s will work, but only on a single date, and I need to feed it into a table, so, is there a way to use awk with some func():

cat file.csv | awk -F, 'print func($1)","$2","$3}'

Since I don't really care how I do this, alternatively, how would I change a date in excel into epoch, when the string is mm/dd/yyyy...

Answer 1

TZ=PST awk -F, '{split($1,date,"/");
                 $1=mktime(date[3] " " date[1] " " date[2] " " "00 00 00");
                 print}'

Or, invoking date:

TZ=PST awk -F, '{ OFS = FS;
                  command="date -d" $1 " +%s";
                  command | getline $1;
                  close(command);
                  print}'

Answer 2

On excel, say a1 has 1/30/2010. then have =(A1-DATE(1970,1,1))*86400 somewhere and then change number format to be General

Alternatively, I dont know awk, but with python, (fairly recent version is needed for .total_seconds())

import datetime as DT
f = [
'01/30/2010,1,"hi"',
'01/31/2010,3,"bye"',
]
e0 = DT.date(1970,1,1)
for line in f:
    mm,dd,yyyy=[int(x) for x in line[:2],line[3:5],line[6:10]]
    e=int((DT.date(yyyy,mm,dd)-e0).total_seconds())
    print str(e) + line[10:]

Answer 3

echo "2016-01-31 23:47:27" | awk -F, '{ OFS = FS;command="date -d " "\"" $1 "\""  " +%s";command | getline $1;close(command);print}'

We need to pass double quotes because we need to execute below:

date -d"2016-01-31 23:47:27" +%s