change two files with different date format

Question

I want to compare two files using diff command. However, due to different date format in both the files it not compared

File1

394|a101|2019-04-08 09:45:01|2019-04-08 09:45:01
389|a102|2019-04-08 09:46:02|2019-04-08 09:46:02

Files2

394|a101|20190408094501000000|20190408094501000000
389|a102|20190408094602000000|20190408094602000000

Above both the files are same but only date format is different.

I have tried to remove Hyphen & Colon from the string and then compare but it might possible that same symbol might available in other columns

Plz suggest how can I compare both files. (Date columns comes on any position in file)

Answer 1

general thoughts:

• better optimize your data sources
• verify both sides work on UTC or same time zone

How you might easily do it:

either rectify values A or values B:

( here the example for converting A ,since that's more reliable when you can anchor on : and - )

cat filea|sed 's/\([0-9][0-9][0-9][0-9]\)-\([0-9][0-9]\)-\([0-9][0-9]\) \([0-9][0-9]\):\([0-9][0-9]\):\([0-9][0-9]\)/\1\2\3\4\5\6000000/g'

OUTPUT:

394|a101|20190408094501000000|20190408094501000000
389|a102|20190408094602000000|20190408094602000000

---

example snippet:

#!/bin/bash
replacetime() { sed 's/\([0-9][0-9][0-9][0-9]\)-\([0-9][0-9]\)-\([0-9][0-9]\) \([0-9][0-9]\):\([0-9][0-9]\):\([0-9][0-9]\)/\1\2\3\4\5\6000000/g' ; } ;
cat FILEA | replacetime > /tmp/FILEA.tmp
diff /tmp/FILEA.tmp FILEB