[Dbix-class] Announcing DBIx::Class::Helper::ResultSet::DateMethods1

[fREW Schmidt]

Announcing DBIx::Class::Helper::ResultSet::DateMethods1
=======================================================

::DateMethods1 is a nice little DSL on top of DBIC that allows you to
do "date stuff" in SQL Server, SQLite, MySQL, PostgreSQL, and Oracle
(untested.)

Do you store dates in your database?  Do you ever want to manipulate them
efficiently?  Well here's your solution!

First, how do you search in a more comprehensible way?

 $rs->dt_on_or_before(
   { -ident => '.when_created' },
   DateTime->now->subtract(days => 7),
 );

`dt_on_or_before` (as well as `dt_before`, `dt_on_or_after`, or `dt_after`)
merely aliases `<=`, `<`, `>=`, and `>`, respectively.
Instead of trying to think about the numerical meaning of a date on a timeline,
just use these named methods.  In addition to the nicer name, they can take
DateTime object (which are automatically converted to UTC), and autoprepend
DBIx::Class::ResultSet::current_source_alias when passed an `-ident` that
starts with a `.`.  You can pass any of a value, a column (via `-ident`), a
subquery, literal sql, or a `DateTime` object to either parameter slots of
these methods.

Second, how do I really leverage this module to do stuff with dates in my
database?

Here's a query I originally wrote with date ops.  Basically it groups some
columns by some "date parts" like year, month, day, etc.  You can use it to make
nice reports of things like how many things have been done per month, or maybe
find out if the system is more busy in the summer:

 $rs->search(undef, {
    columns => {
       count => '*',
       year  => $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'),
       month => $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'),
    },
    group_by => [
      $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'),
      $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'),
    ],
 )->hri->all

I use that exact query (though I give the user a UI for which dateparts
to include) in my system, and it works on SQL Server and SQLite, and
it's fast.  Awesome.

Or how about a query to discover how many issues were resolved before the next
full day after their creation?  Check it out:

 # note that 'day', 1 should also work
 $rs->dt_before(
   { -ident => '.resolution' },
   $rs->dt_SQL_add({ -ident => '.creation' }, 'hour', 24),
 )->all

Both of the above queries work on all of the supported datebases!

Third, some little helpers to extend the above.

On top of those things, I also throw in a couple other handy methods.  One,
`utc` converts a DateTime object to a string, in the UTC timezone.  Hopefully
you shouldn't need it directly, but I've already ended up using it in places
where our code forced me to return a simple hash to get merged into a search
query, instead of letting me call methods on an RS.

Another lagniappe is `utc_now` which returns some literal sql that resolves to
the current date and time in UTC on your database.  You can pass it in to search
just like you would datetime.  So if your server and your database have in sync
clocks, these would do the same thing:

 $rs->dt_on_or_before(
   { -ident => '.when_created' },
   DateTime->now->subtract(days => 7),
 );

 $rs->dt_on_or_before(
   { -ident => '.when_created' },
   $rs->dt_SQL_add($rs->utc->now, 'day', -7),
 );

(Aside: many people seem to hold suspect the idea that the clock is correct on a
given server.  If you can't trust the clock of a server, you probably can't
trust the server.  Use NTP.)

And that's it.  I hope you can use and enjoy these helpers!

(original blog post here http://blog.afoolishmanifesto.com/archives/1926)

-- 
fREW Schmidt
http://blog.afoolishmanifesto.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 836 bytes
Desc: not available
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20140304/32cb3857/attachment.pgp>