2

I have a log file with the following structure

Example:

1522693524403 entity1,sometext
1522693541466 entity2,sometext
1522693547273 entity1,sometext
...

Now I would like to replace the time from epoch milliseconds to DD.MM.YYYY HH:MM:SS in all of the log files with a bash command on a Debian System.

I tried different solutions provided here and on other websites but it did not really work for me.

Can anyone help me please?

Cheers

fastboot

Jeff Schaller
  • 67,283
  • 35
  • 116
  • 255
fastboot
  • 57
  • 1
  • 5
  • Will the timestamp always be the first string on each line? Can we simply take all non-space characters until the first space and treat it as a timestamp? – terdon Dec 05 '20 at 14:33
  • FYI, you can call awk or sed or anything from any shell. – Jeff Schaller Dec 05 '20 at 14:42
  • Do you really want d-m-Y? Y-m-d will be better, both human readable and alphabetically sorted and sortable (the ISO format). Also, do you want the milliseconds or not? Also, if you do have space delimited logs, consider having an underscore so that you have it as one field, not two. – thanasisp Dec 05 '20 at 14:54
  • Yes it will be the first string on each line.

    I tried it with awk and sed as well. same with perl.

    Can be also YYYY.MM.DD. But in my country its just different. we use DD.MM.YYYY. But you are right, when I need to process the data later, its easiert this way

     – fastboot Dec 05 '20 at 15:11

2 Answers2

5

If you use a shell script reading line by line, or an awk script calling system date, it would be very slow, too many processes. You have to use a simple awk, Perl, Python, or anything, script. All languages have standard datetime functions for convertions between formats.

Here and here are some good references. If you want to use GNU awk time functions and strftime(), all you need for your case is to select the epoch substring excluding the milliseconds:

$ awk '{$1 = strftime("%F %T", substr($1,1,10))} 1' file
2018-04-02 21:25:24 entity1,sometext
2018-04-02 21:25:41 entity2,sometext
2018-04-02 21:25:47 entity1,sometext

Or to print the milliseconds together:

$ awk '{$1 = strftime("%F %T", substr($1,1,10)) "." substr($1,11)} 1' file
2018-04-02 21:25:24.403 entity1,sometext
2018-04-02 21:25:41.466 entity2,sometext
2018-04-02 21:25:47.273 entity1,sometext

Or to print the day-month-Year format:

$ awk '{$1 = strftime("%d-%m-%Y %T", substr($1,1,10))} 1' file
02-04-2018 21:25:24 entity1,sometext
02-04-2018 21:25:41 entity2,sometext
02-04-2018 21:25:47 entity1,sometext
thanasisp
  • 8,122
  • This is fantastic! :) your first solution "awk '{$1 = strftime("%F %T", substr($1,10))} 1' file" does not work properly. e.g "1970-01-01 01:58:54 ...." The Second one and the third one worked. Thank you very much :) – fastboot Dec 05 '20 at 15:20
  • Ok I have updated a typo in the first command, @fastboot, welcome. – thanasisp Dec 05 '20 at 15:27
2

This isn't something you can do with a bash command, you need an external program since bash can't do floating point math and can't handle dates. I suspect you just meant "on the command line" though, and you don't actually require a pure bash solution. With that assumption, here's a gawk (GNU awk) solution:

$ gawk '{$1=strftime("%d.%m.%Y %H:%m:%S",sprintf("%.3f", $1 /1000))}1;' file
02.04.2018 19:04:24 entity1,sometext
02.04.2018 19:04:41 entity2,sometext
02.04.2018 19:04:47 entity1,sometext

The strftime function converts the timestamp to a date. Since yours is in milliseconds, we use sprintf to convert that to seconds. The final 1; just tells gawk to print the line after we have assigned the new value to $1 (the first field).

Remember that the output will depend on your timezone. For example, the above was run on a machine set to the GMT timezone. This is why my numbers are different to the ones in @thanasisp's answer which was presumably run on a different timezone. You can control this and change the output by setting the TZ variable when running the command. For example, compare this:

$ gawk '{$1=strftime("%d.%m.%Y %H:%m:%d.%S",sprintf("%.3f", $1 /1000))}1;' file
02.04.2018 19:04:02.24 entity1,sometext
02.04.2018 19:04:02.41 entity2,sometext
02.04.2018 19:04:02.47 entity1,sometext

to this:

$ TZ=America/Los_Angeles gawk '{$1=strftime("%d.%m.%Y %H:%m:%d.%S",sprintf("%.3f", $1 /1000))}1;' file
02.04.2018 11:04:02.24 entity1,sometext
02.04.2018 11:04:02.41 entity2,sometext
02.04.2018 11:04:02.47 entity1,sometext

Or this:

$ TZ=Asia/Tokyo gawk '{$1=strftime("%d.%m.%Y %H:%m:%d.%S",sprintf("%.3f", $1 /1000))}1;' file
03.04.2018 03:04:03.24 entity1,sometext
03.04.2018 03:04:03.41 entity2,sometext
03.04.2018 03:04:03.47 entity1,sometext
terdon
  • 242,166
  • 1
    Good point about the timezone, and how to set it. Also I see you can demand GMT inside strftime with a third argument non zero (e.g. 1) or else TZ is used. – thanasisp Dec 05 '20 at 15:24