How to add a column in a file whose values are equivalent to epoch conversion of one of the date colmn already in that file using unix commands?

Question

Suppose I have a .txt file in my unix database : events.txt with 3 columns and 4 records as follow:

Name     DateofBirth                           City
Alex     2016-02-22 15:12:21.244000            London
John     2016-02-24 15:12:21.244000            Paris
Harry    2016-02-23 16:17:21.254000            Moscow
Karry    2016-02-23 11:12:21.271000            Berlin

I want to edit this table or create a new table which must contain a 4th column named as DOB_epoch (as epoch value of 2nd column DateofBirth) as follow:

Name     DateofBirth                           City       DOB_epoch
Alex     2016-02-22 15:12:21.244000            London     9632587410
John     2016-02-24 15:12:21.244000            Paris      9871236540
Harry    2016-02-23 16:17:21.254000            Moscow     9478523690
Karry    2016-02-23 11:12:21.271000            Berlin     9321456870

What should be the command in unix to create such table column?

What are your column separators? Is it tabs? Or does the file have fixed width columns? Assuming you have GNU awk, see https://www.gnu.org/software/gawk/manual/html_node/Time-Functions.html#Time-Functions — glenn jackman, Apr 11 '16 at 14:02
`/me` is staggered that you need to record time of birth right down to the millisecond! — Toby Speight, Apr 11 '16 at 15:45

score 0 · Answer 1 · answered Apr 11 '16 at 15:05

Quite easy with a "while" loop. Assuming you want to generate an "events2.txt" file :

For Linux :

#!/bin/bash

while read line;
do
    timedate=`echo $line | egrep -o "20.*[0-9]" | cut -d '.' -f 1`
    if [[ $timedate ]]
    then
        timestamp=`date -d"$timedate" +%s`
        echo -e "$line\t$timestamp" >> events2.txt
    else
        echo -e "$line\tDOB_epoch" >> events2.txt
    fi
done < events.txt

For BSD, just replace the timestamp line with :

timestamp=`date -j -f "%Y-%m-%d %H:%M:%S" "$timedate" +%s`

score 0 · Answer 2 · answered Apr 11 '16 at 15:32

You can do it fairly simply in a short shell script using read, date and printf. For example:

#!/bin/sh

fname=${1:-/dev/stdin}

c=0
while read -r nm dt tm ci; do
    [ "$c" -eq '0' ] && {
        printf "%-8s %-30s %-10s DOB_epoch\n" "$nm" "$dt" "$tm"
        c=1
        continue
    }
    epoch=$(date -d "${dt} ${tm}" +%s)
    printf "%-8s %-30s %-10s %s\n" "$nm" "$dt $tm" "$ci" "$epoch"
done < "$fname"

Usage/Output

$ sh epochbd.sh dat/epochbd.txt
Name     DateofBirth                    City       DOB_epoch
Alex     2016-02-22 15:12:21.244000     London     1456175541
John     2016-02-24 15:12:21.244000     Paris      1456348341
Harry    2016-02-23 16:17:21.254000     Moscow     1456265841
Karry    2016-02-23 11:12:21.271000     Berlin     1456247541

I didn't count characters for spacing, so you may need to adjust the printf field-width modifiers by a character or two. Check to make sure your version of read supports the -r options (otherwise, remove it).

How to add a column in a file whose values are equivalent to epoch conversion of one of the date colmn already in that file using unix commands?

2 Answers2