experchange > shell

Paul D (10-31-18, 12:48 PM)
I have a sql insert file some 1 million rows in length.

For each line the format is:

INSERT INTO table VALUES(23667421,'STRING1',60.0,'STRING2',60,154028 1600,1540281660,1);

Where the 6th and 7th field (this can assumed to be a constant and CSV delimted) are epoch timestamps.

Does anybody have an eloquent way to replace those epoch timestamps within the same file (like a sed -i for in place) with a timestamp in the same column that is in <YYYY-MM-DD HH:SS> format ?

Thanks in advance for any replies..

Paul
Keith Thompson (10-31-18, 01:38 PM)
Paul D <spectre.vs.rector> writes:
> I have a sql insert file some 1 million rows in length.
> For each line the format is:
> INSERT INTO table VALUES(23667421,'STRING1',60.0,'STRING2',60,154028 1600,1540281660,1);
> Where the 6th and 7th field (this can assumed to be a constant and CSV
> delimted) are epoch timestamps.
> Does anybody have an eloquent way to replace those epoch timestamps
> within the same file (like a sed -i for in place) with a timestamp in
> the same column that is in <YYYY-MM-DD HH:SS> format ?


I presume you mean YYYY-MM-DD HH:MM:SS.

It's reasonably straightforward in Perl. I'll post a solution later
today if nobody else does.
Paul D (10-31-18, 01:58 PM)
On Wednesday, 31 October 2018 11:38:55 UTC, Keith Thompson wrote:
> Paul D <> writes:
> I presume you mean YYYY-MM-DD HH:MM:SS.
> It's reasonably straightforward in Perl. I'll post a solution later
> today if nobody else does.
> --
> Keith Thompson (The_Other_Keith) <http://www.ghoti.net/~kst>
> Will write code for food.
> "We must do something. This is something. Therefore, we must do this."
> -- Antony Jay and Jonathan Lynn, "Yes Minister"


Urgh, yes, me bad - YYYY-MM-DD HH:MM:SS please.. thanks Keith..
Janis Papanagnou (10-31-18, 03:52 PM)
On 31.10.2018 11:48, Paul D wrote:
> I have a sql insert file some 1 million rows in length.
> For each line the format is:
> INSERT INTO table
> VALUES(23667421,'STRING1',60.0,'STRING2',60,154028 1600,1540281660,1);


Can we be sure that STRING{1,2} contains no comma? - Then, with GNU awk,
you can do

awk -v q="'" '
BEGIN{FS=OFS=","} {$6=strftime(q"%F %T"q,$6); $7=strftime(q"%F %T"q,$7)} 1
'

assuming that the ISO time stamps shall be embedded in single quotes.
Otherwise reduce that to just

awk 'BEGIN{FS=OFS=","} {$6=strftime("%F %T",$6); $7=strftime("%F %T",$7)} 1'

Janis
[..]
Paul D (10-31-18, 04:29 PM)
Hi Janis

Yes STRING{1,2} contains no comma, your 2nd solution seems to work perfectly for my needs - thank you ;)

Paul
Ben Bacarisse (11-01-18, 03:49 AM)
Paul D <spectre.vs.rector> writes:

> I have a sql insert file some 1 million rows in length.
> For each line the format is:
> INSERT INTO table VALUES(23667421,'STRING1',60.0,'STRING2',60,154028 1600,1540281660,1);
> Where the 6th and 7th field (this can assumed to be a constant and CSV
> delimted) are epoch timestamps.
> Does anybody have an eloquent way to replace those epoch timestamps
> within the same file (like a sed -i for in place) with a timestamp in
> the same column that is in <YYYY-MM-DD HH:SS> format ?


Just for fun, here is shell solution, though it relies on having a more
sophisticated date program then the minimum POSIX one:

while IFS=',' read -r a b c d e f g h; do
echo "$a,$b,$c,$d,$e,"$(
date "+%Y-%m-%d %H:%M:%S" -d "@$f")","$(
date "+%Y-%m-%d %H:%M:%S" -d "@$g")",$h"
done

Like the others, it replies on the two strings not having any commas.
Similar Threads