Convert Epoch to timestamp in a file

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hi Team,

Could you please let me know ,how to convert Epoch column to timestamp in a flat file.

"57894"|"1454247163111"|"""HH"""
"57897"|"1454247163111"|"""HH"""
"7906"|"1454247163111"|"""ss"""

I want second field as timestamp.

2

Hello shabeena,

Could you please try following and let me know if this helps.

echo "1458628242" | awk '{ printf "%s -- %s\n", strftime("%c",$1), $0 }'

Output will be as follows.

Tue 22 Mar 2016 02:30:42 AM EDT -- 1458628242

EDIT: Seems you have added Input_file now, then following may help you in same now.

awk -F"|" '{print strftime("%c",$2)}'  Input_file

Output will be as follows.

Wed 31 Dec 1969 07:00:00 PM EST
Wed 31 Dec 1969 07:00:00 PM EST
Wed 31 Dec 1969 07:00:00 PM EST

In case you have any other requirements other than this, then request you to please show us complete Input_file with sample output too. Hope this helps.

Thanks,
R. Singh

3

Hi Ravinder,

it is working fine. I want it to change in whole file for 2nd column.

Note: I have to load data into Teradata table, so I required it to convert into timestamp(6) format.

4

Hello shabeena,

Could you please try following and let me know if this helps you.

awk -F"|" '{$2=strftime("%c",$2)}; 1' OFS="|"  Input_file > Temp_Input_file
mv Temp_Input_file Input_file

I have given only commands as above, you could use them in script too.

Thanks,
R. Singh

5

Hi Ravinder,

its working :slight_smile:

iam getting <<Thu 01 Jan 1970 10:00:00 AM AEST>>

I want the data as <<1970-01-01 10:00:00>>

6

Hello Shabeena,

Could you please try following and let me know if this helps you.

awk -F"|" '{$2=strftime("%c",$2);num=split("Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec", month," ");for(i=1;i<=num;i++){B[month]=i};split($2, A," ");$2=A[4]"-"B[A[3]]"-"A[2] " " A[5]}; 1' OFS="|"  Input_file

Output will be as follows on same.

"57894"|1969-12-31 07:00:00|"""HH"""
"57897"|1969-12-31 07:00:00|"""HH"""
"7906"|1969-12-31 07:00:00|"""ss"""

Thanks,
R. Singh

7

Are you sure 1969-12-31 is a correct conversion of an epoch time like 1458628242 ? The former is one day before the epoch:

date -d@-86400
Mi 31. Dez 01:00:00 CET 1969

---------- Post updated at 13:03 ---------- Previous update was at 13:00 ----------

And, BTW, the "epoch times" in post#1 are ambitious as well:

date -d@1454247163111
Fr 4. Apr 11:18:31 CEST 48053