match and replace one pattern multiple times in th

Question

Given the input of the form:

select * from foo where ts>@(-2 days) and ts < @(-1 hour)

(this is of course just an example. I want to support any query construct and any expression in @())

I want to replace the expression in every @() with the result of evaluating date --date=exp +%s, where 'exp' is the expression (so in the example above the result will be

select * from foo where ts>1436694136 and ts < 1436863336

More generally, how do I replace a pattern with the result of invoking a shell command with captures from the pattern as arguments?

(I've tagged the question with perl, and bash, but am open to anything)

Answer 1

Using gnu sed:

$ sql_expr="select * from foo where ts>@(-2 days) and ts < @(-1 hour)"

$ sed -r 's|@\(([^)]*)\)|$(date +%s --date "now\1")|g; s|.*|echo "&"|e' <<< "$sql_expr"

Gave:
select * from foo where ts>1436696209 and ts < 1436865409

Note that this code assumes that the contents of @(...) are valid expressions, for date calculations.

Explanation:

1. First expression will replace all @() occurrences with $(date +%s --date "now _expression_") where _expression_ is going to be something like -2 days.
2. Second replace expression adds echo "..." around entire string & executes the generated line. You can remove the e flag in second expression to find out what command gets executed actually.

Answer 2

Something like below (untested)

#!/usr/bin/perl
use strict;
use warnings;
my $date1;
my $date2;
my $expr = q{select * from foo where ts>@(-2 days) and ts < @(-1 hour)};
if( $expr =~ m#\@\((.*?)\) and .*? \@\((.*?)\)# ){
    $date1 = $1;
    $date2 = $2;
}
my $expr1 = `date --date=$date1 +%s`;
my $expr2 = `date --date=$date2 +%s`;

#now search and replace the $expr

$expr =~ s#\@\((.*?)\)#$expr1;
$expr =~ s#< \@\((.*?)\)#<.$expr2;

print "Final expression is $expr\n";