OK, I've now got a working pre-alpha of datetime as SQL. There's no
installer, docs or anything. Just read the code. You'll need to install
SQL::Statement to get it to work. Table and Select columns don't work,
all that currently matters is the WHERE clause. So now you can get a
spanset with:
SELECT spanset FROM eternity WHERE year>=1901 AND year < 2001 AND
month=12 and day=25
Which will return the spanset of 20th Century Christmases!
Have fun, I'm posting this now because I might not get back onto it for
a few days or a week. What do people think? Could it actually be useful?
Cheers!
Rick
use SQL::Statement;
use DateTime::Span;
use DateTime;
# Turn SQL into a data structure
my $parser = SQL::Parser->new('AnyData');
my $stmt = SQL::Statement->new("SELECT spanset FROM eternity WHERE year <= 2004 AND year > 2001 AND NOT year = 2003 AND day = 12", $parser);
# Turn the Data structure into a span set
my $set = get_a_set($stmt->{where_clause});
# Output the start-of-spans
my $iter = $set->iterator;
while ($dt = $iter->next) {
print $dt->start->datetime . "\n";
}
# The workings
sub get_a_set {
my $stmt = shift;
my $set;
if ($stmt->{arg1}{arg1}) {
my $set1 = get_a_set($stmt->{arg1});
my $set2 = get_a_set($stmt->{arg2});
my $op = $stmt->{op};
if ($op eq 'AND') {
$set = $set1->intersection($set2);
}
elsif ($op eq 'OR') {
$set = $set1->union($set2);
}
} else {
#print Dumper($stmt);
my $column = ($stmt->{arg1}->{type} eq 'column')
? $stmt->{arg1}->{value}
: ($stmt->{arg2}->{type} eq 'column')
? $stmt->{arg2}->{value}
: undef;
die("No column specified") unless $column;
my $value = ($stmt->{arg1}->{type} eq 'number')
? $stmt->{arg1}->{value}
: ($stmt->{arg2}->{type} eq 'number')
? $stmt->{arg2}->{value}
: undef;
die("No value specified") unless $value;
my $op = $stmt->{op};
if ($op eq '>') {
$set = nan_set($value+1, 100_000_000) if $column eq 'NANOSECOND';
$set = sec_set($value+1, 61) if $column eq 'SECOND';
$set = min_set($value+1, 60) if $column eq 'MINUTE';
$set = hur_set($value+1, 24) if $column eq 'HOUR';
$set = dow_set($value+1, 7) if $column eq 'DOW';
$set = day_set($value+1, 31) if $column eq 'DAY';
$set = mon_set($value+1, 12) if $column eq 'MONTH';
$set = qtr_set($value+1, 4) if $column eq 'QUARTER';
$set = DateTime::Span->from_datetimes(start => DateTime->new( year => $value+1 )) if $column eq 'YEAR';
}
elsif ($op eq '<') {
$set = nan_set(0, $value-1) if $column eq 'NANOSECOND';
$set = sec_set(0, $value-1) if $column eq 'SECOND';
$set = min_set(0, $value-1) if $column eq 'MINUTE';
$set = hur_set(0, $value-1) if $column eq 'HOUR';
$set = dow_set(1, $value-1) if $column eq 'DOW';
$set = day_set(1, $value-1) if $column eq 'DAY';
$set = mon_set(1, $value-1) if $column eq 'MONTH';
$set = qtr_set(1, $value-1) if $column eq 'QUARTER';
$set = DateTime::Span->from_datetimes(before => DateTime->new( year => $value )) if $column eq 'YEAR';
}
elsif ($op eq '=') {
$set = nan_set($value) if $column eq 'NANOSECOND';
$set = sec_set($value) if $column eq 'SECOND';
$set = min_set($value) if $column eq 'MINUTE';
$set = hur_set($value) if $column eq 'HOUR';
$set = dow_set($value) if $column eq 'DOW';
$set = day_set($value) if $column eq 'DAY';
$set = mon_set($value) if $column eq 'MONTH';
$set = qtr_set($value) if $column eq 'QUARTER';
$set = DateTime::Span->from_datetimes(start => DateTime->new( year => $value ), before => DateTime->new( year => $value+1 )) if $column eq 'YEAR';
}
elsif ($op eq '>=') {
$set = nan_set($value, 100_000_000) if $column eq 'NANOSECOND';
$set = sec_set($value, 61) if $column eq 'SECOND';
$set = min_set($value, 60) if $column eq 'MINUTE';
$set = hur_set($value, 24) if $column eq 'HOUR';
$set = dow_set($value, 7) if $column eq 'DOW';
$set = day_set($value, 31) if $column eq 'DAY';
$set = mon_set($value, 12) if $column eq 'MONTH';
$set = qtr_set($value, 4) if $column eq 'QUARTER';
$set = DateTime::Span->from_datetimes(start => DateTime->new( year => $value )) if $column eq 'YEAR';
}
elsif ($op eq '<=') {
$set = nan_set(0, $value) if $column eq 'NANOSECOND';
$set = sec_set(0, $value) if $column eq 'SECOND';
$set = min_set(0, $value) if $column eq 'MINUTE';
$set = hur_set(0, $value) if $column eq 'HOUR';
$set = dow_set(1, $value) if $column eq 'DOW';
$set = day_set(1, $value) if $column eq 'DAY';
$set = mon_set(1, $value) if $column eq 'MONTH';
$set = qtr_set(1, $value) if $column eq 'QUARTER';
$set = DateTime::Span->from_datetimes(before => DateTime->new( year => $value+1 )) if $column eq 'YEAR';
} else {
die "Unknown operator: $op";
}
#print Dumper($set);
if($stmt->{neg}) {
$set = $set->complement();
}
}
return $set
}
sub nan_set { part_set('nanosecond', 'second', @_); }
sub sec_set { part_set('second', 'minute', @_); }
sub min_set { part_set('minute', 'hour', @_); }
sub min_set { part_set('minute', 'hour', @_); }
sub hur_set { part_set('hour', 'day', @_); }
sub day_set { part_set('day', 'month', @_); }
sub mon_set { part_set('month', 'year', @_); }
sub part_set {
my ($unit1, $unit2, $from, $to) = @_;
$to ||= $from;
my $set = DateTime::Set->from_recurrence(
recurrence => sub {
#warn "PRE: " . $_[0]->datetime . "\n";
my $dt = shift;
my $next = $dt->clone;
$next->add( $unit1.'s' => 1 );
#print "POTENTIAL: This: " . $dt->ymd . ", Next: " . $next->ymd . "\n";
if ($next->$unit1 > $to or $next->$unit2 != $dt->$unit2) {
# print "ROLLOVER: " . $dt->ymd . "\n";
$dt->set( $unit1 => $from );
# print " TO: " . $dt->ymd . "\n";
$dt->add( $unit2.'s' => 1);
# print " AND: " . $dt->ymd . "\n";
} else {
$dt->add( $unit1.'s' => 1);
}
#warn "POST: " . $dt->datetime . "\n";
return $dt;
}
);
return $set;
}
