Re: DBI INTERVAL Error

[erix]

DBI doesn't 'implement' SQL --- DBMS systems implement SQL. DBI just passes the SQL to the backend. That backend could be one of a whole range of DBMS systems (Oracle, Oracle MySql, Sybase, DB2, PostgreSQL, etc.).

INTERVAL is in the SQL standard but that doesn't mean automatically that all systems implement it.

Which backend (or backends) are you using?

UPDATE: Oh wait, the error says: 'DBD::CSV'. So, reading files, which means no INTERVAL I don't think.

It seems DBD::CSV implements this SQL ("a small but useful subset"): SQL::Statement

(and no, I don't see INTERVAL in there.)

[Anonymous Monk]

Hi, I am not using any backend DB, I am open a bunch of .csv files and using DBI to create reports.
And as part of my SQL query is to use


my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
         f_dir   => "mkdir",
         f_ext   => ".csv",
         f_enc   => "utf-8",
         csv_eol => "\n",
         RaiseError => 1,
       });
...
WHERE mydate>= (CURRENT_DATE() - interval(6) DAY)
...

Any other alternatives?
Thanks!

[poj]

Any other alternatives?

#!perl
use strict;
use DBI;
use Time::Piece;
use Time::Seconds 'ONE_DAY';

my $t = Time::Piece->new();
my $t1 = $t - 6 * ONE_DAY;
my $t2 = $t + 1 * ONE_DAY;

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
  f_dir   => "mkdir",
  f_ext   => ".csv",
  f_enc   => "utf-8",
  csv_eol => "\n",
  RaiseError => 1,
});

my $sql = "SELECT name,city, COUNT(*) AS rows 
           FROM file.csv
           WHERE (mydate >= ?) AND (mydate < ?)
           GROUP BY name,city";

my $sth = $dbh->prepare($sql);
$sth->execute($t1->ymd,$t2->ymd);

my $ar = $sth->fetchall_arrayref;
print join ",",@$_,"\n" for @$ar;

poj

[Anonymous Monk]

Why wouldn't this work with the code sample you posted?

my $sql = " SELECT * 
              FROM
                 ( SELECT name, city, COUNT(*) AS Rows 
                  FROM file.csv
                  WHERE ( mydate >= ? ) 
                  AND  ( mydate   < ? ) ) t1 
              INNER JOIN
                 ( SELECT name, city, COUNT(*) AS Rows 
                   FROM file.csv 
                   WHERE ( mydate >= ? )
                  AND ( mydate < ? ) ) t2 
              ON t1.mydate = t2.mydate";

From here:
http://search.cpan.org/~jzucker/SQL-Statement-1.14/lib/SQL/Statement/Syntax.pod

Docs:
Explict Join Qualifiers: NATURAL, INNER, OUTER, LEFT, RIGHT, FULL
Any thoughts?