0

I have a table in a SQLite database where a column stores file mtimes in epoch seconds.

I would now like to search in that table files that were modified in a certain month?

In raw SQL I would do:

 select * from my_table where strftime('%Y-%m', mtime, "unixepoch") = "2009-08"

Is there a way to do this efficiently via DBIx::Class? Is it possible to do

 $m->search({ \'strftime('%Y-%m', mtime, "unixepoch")' => "2009-08" })

I tried understanding if there's a way with DBIx::Class::InflateColumn::DateTime but I didn't find it.

Thanks

Simone

simone
  • 4,667
  • 4
  • 25
  • 47
  • As a workaround, you could select timestamps `>= Y-m-1 0:00` and `< Y-(m+1)-1 00:00`. `Time::Local` can be used to translate dates to seconds. – Dallaylaen Apr 10 '11 at 13:38

2 Answers2

5

The syntax you're looking for is:

$m->search(
  \[ q{strftime('%Y-%m', mtime, "unixepoch") = ?}, "2009-08" ]
);

Yes, that's an array-ref-ref. The first element is literal SQL which is permitted to use ? placeholders, and the remaining elements are values to bind to those placeholders, and DBIC will make sure to reorder everything internally so that those values get put into the right place in the bind list when the query runs.

If you need to combine one of these with other criteria, since an arrayrefref isn't a valid hash key, you need to use the -and syntax:

$m->search({ 
  -and => [
    foo => 'bar',
    \[ q{ SQL }, $bind ],
  ],
});

Here is the section of the SQL::Abstract docs for this construct.

hobbs
  • 223,387
  • 19
  • 210
  • 288
0

I would suggest you using search_literal instead:

# assuming $m isa DBIx::Class::ResultSet
$m->search_literal('strftime("%Y%m", mtime, "unixepoch") = ?', '200908');

EDIT: I stand corrected. Please refer to @hobbs' comment and answer.

yibe
  • 3,939
  • 2
  • 24
  • 17
  • 1
    Please don't, it's a broken interface, as the doc you linked to says, and there's another way to do it that actually works properly. – hobbs Apr 10 '11 at 16:44