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.)

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)
...
[download]

Any other alternatives?
Thanks!

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;
[download]

poj

Question is; is INTERVAL not part of DBI SQL? Any other suggestions?

It's not to do with the DBI, it's the driver you are using. See the error message. DBD::CSV doesn't support date datatypes.
And afaict INTERVAL is only supported by Oracle.

But as the error message also indicates, DBD::CSV uses SQL::Statement as its SQL engine, and you can implement your own SQL functions as explained in Statement/Syntax.pod#Extending_SQL_syntax_using_SQL.

On the other hand, depending on how large your data set is, it might be simpler to implement the filter in Perl and apply it as you fetch rows from the statement handle or in building the query as shown by poj.

Hope this helps!

This doesn't help the OP, but it appears that in addition to Oracle, MySQL also has INTERVAL: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html.

I like the idea of using Perl as a "filter helper" function. I don't use the DBI often, but when I do, I try to make things SQLite compatible and "help" it with Perl when necessary. The result usually will work on many DB's.

As another thought, it is of course possible for Perl to write and then run SQL. Read some row, then construct an SQL statement with dates calculated by Perl, avoiding the INTERVAL gizmo. It is odd to have a program write another program (SQL statement), but this can work. In that case you wind up with different kind of Perl code.

As another weird thought, since we are talking about CSV... Spreadsheets work very well with that format. Its been more than a decade, but I did have one project where I automated a spreadsheet with macros, then automated Word with macros to make a fancy looking management report of the result. This of course was not "efficient" CPU wise, but it was efficient use of my time each week. Of course mileage varies, but sometimes this is just a matter of "getting the result" and to the heck with the MIPs that it takes. How big these .CSV files are and the scope of work is of course unknown to me.

First, you queries are missing the required (but redundant) GROUP BY clause.

INTERVAL is merely a convenience, but it can be expressed otherwise, though it differs by RDBMS. Generally, if trying to remove 6 days, you can say DAYS without the word INTERVAL. For mysql, you might want to check Date and Time Functions.