How to convert all unix dates in a file?

Question

I've been searching all over and haven't succeeded yet with this task..

I have some gps data files with multiple dates presented as Unix epoch time in seconds and I need to convert these so that I can insert the file data into a spreadsheet.

date can convert them, e.g.

$ date -d @1441202338 +'%d-%m-%y %H:%M:%S'
02-09-15 21:58:58

The unix dates are ten digits enclosed with quotes, and there are other longer numbers such as longitude and latitude, so to replace only the dates I need to find exactly ten digits in quotes.

I've started building a sed command, but I can't find out how to search for a string in quotes and then send only the string to date

sed "s/\([0-9]{10}\)/$(date -d @\1 +'%d-%m-%y %H:%M:%S')/g" file

would putting \<\" in there denote a quote at the start?

I'm also not sure if date can understand \1

Can someone guide me to a solution?

Edit:

Here's a sample line from a file I'm trying to convert:

type="waypoint" latitude="22.2091608952380115" longitude="44.65250015586757" name="name" altitude="309.18737800000002" unixtime="1441202338"

where unixtime="1441202338" must be changed to unixtime="02-09-15 21:58:58".

(so obviously, the date will not be "unixtime" any more, but that tag can be changed afterwards)

Edit2:

A minimal file example:

'#VIKING GPS Data file http://viking.sf.net/
FILE_VERSION=1

xmpp=4.000000
ympp=4.000000
lat=40.714490
lon=-74.007130
mode=mercator
color=#cccccc
highlightcolor=#eea500
drawscale=t
drawcentermark=t
drawhighlight=t

~Layer TrackWaypoint
name=name
tracks_visible=t
waypoints_visible=t
routes_visible=t
trackdrawlabels=t
trackfontsize=3
drawmode=0
trackcolor=#000000
drawlines=t
line_thickness=1
drawdirections=f
trkdirectionsize=5
drawpoints=t
trkpointsize=2
drawelevation=f
elevation_factor=30
drawstops=f
stop_length=60
bg_line_thickness=0
trackbgcolor=#ffffff
speed_factor=30.000000
tracksortorder=0
drawlabels=t
wpfontsize=3
wpcolor=#000000
wptextcolor=#ffffff
wpbgcolor=#8383c4
wpbgand=f
wpsymbol=0
wpsize=4
wpsyms=t
wpsortorder=1
drawimages=t
image_size=64
image_alpha=255
image_cache_size=300
metadatadesc=
metadataauthor=
metadatatime=2015-10-03T08:11:32.776627Z
metadatakeywords=


~LayerData
type="waypointlist"
type="waypoint" latitude="5.2091608952380115" longitude="101.65250015586757" name="different-names" altitude="309.18737800000002" unixtime="1441202338" symbol="navaid, amber"
type="waypointlistend"
~EndLayerData
~EndLayer'

Answer 1

If GNU sed is available, look at its e flag to the substitute command:

This command allows one to pipe input from a shell command into pattern space. If a substitution was made, the command that is found in pattern space is executed and pattern space is replaced with its output.

Here's my example (the hard part is getting the quoted quotes right):

$ sed -r -e 's/unixtime="([0-9]+)".*/date -d @\1 \"+unixtime=\\"%d-%m-%y %H:%M:%S\\"\"/ge' \
    <<<'type="waypoint" latitude="22.2091608952380115" longitude="44.65250015586757" name="name" altitude="309.18737800000002" unixtime="1441202338" symbol="navaid, amber"'
unixtime="02-09-15 14:58:58"

Note that /e causes the command substitution to replace the entire pattern space, so you may need to make use of hold space to retain the text before and after the substitution. That's left as an exercise for the reader.

Answer 2

A Perl solution, based on my GNU sed answer:

perl -pe 's/unixtime="([0-9]+)"/`date -d \@$1 \"+unixtime=\\"%d-%m-%y %H:%M:%S\\"\"`/ge&&s/\n//'

Small adaptations to Perl syntax; also Perl doesn't strip the newline from date's output, so there's an extra command there. I tested it on your example file, and the relevant part becomes

~LayerData
type="waypointlist"
type="waypoint" latitude="5.2091608952380115" longitude="101.65250015586757" name="different-names" altitude="309.18737800000002" unixtime="02-09-15 14:58:58" symbol="navaid, amber"
type="waypointlistend"
~EndLayerData

Answer 3

Using gawk time functions.

Example

echo 'altitude="309.18737800000002" unixtime="1441202338"'|\
   gawk '$(NF-1)=strftime("%d-%m-%y %H:%M:%S",$(NF-1))' FS=\" OFS=\"

Results

altitude="309.18737800000002" unixtime="02-09-15 15:58:58"

Complete file To filter the input file use:

gawk '/unixtime=/{$12=strftime("%d-%m-%y %H:%M:%S",$12)}1' FS=\" OFS=\" inputfile > targetfile

Results

...
...
...
~LayerData
type="waypointlist"
type="waypoint" latitude="5.2091608952380115" longitude="101.65250015586757" name="different-names" altitude="309.18737800000002" unixtime="02-09-15 15:58:58" comment="29-08-11 8:27:49" symbol="navaid, amber"
type="waypointlistend"

~EndLayerData
~EndLayer

If your gawk supports inplace replacement, check : https://www.gnu.org/software/gawk/manual/html_node/Extension-Sample-Inplace.html