Arthur Axel "fREW" Schmidt > DBIx-Class-Helpers > DBIx::Class::Helper::ResultSet::DateMethods1



Annotate this POD


View/Report Bugs
Module Version: 2.024001   Source  


DBIx::Class::Helper::ResultSet::DateMethods1 - Work with dates in your RDBMS nicely


 package MySchema::ResultSet::Bar;

 use strict;
 use warnings;

 use parent 'DBIx::Class::ResultSet';


 # in code using resultset

 # get count per year/month
 $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'),
 # mysql
 (SELECT `me`.*, EXTRACT(MONTH FROM `me`.`start`), EXTRACT(YEAR FROM `me`.`start`) FROM `HasDateOps` `me` GROUP BY EXTRACT(YEAR FROM `me`.`start`), EXTRACT(MONTH FROM `me`.`start`))

 # SQLite
 (SELECT "me".*, STRFTIME('%m', "me"."start"), STRFTIME('%Y', "me"."start") FROM "HasDateOps" "me" GROUP BY STRFTIME('%Y', "me"."start"), STRFTIME('%m', "me"."start"))


See "NOTE" in DBIx::Class::Helper::ResultSet for a nice way to apply it to your entire schema.

This ResultSet component gives the user tools to do mostly portable date manipulation in the database. Before embarking on a cross database project, take a look at "IMPLEMENTATION" to see what might break on switching databases.

This package has a few types of methods.

Search Shortcuts

These, like typical ResultSet methods, return another ResultSet. See "dt_before", "dt_on_or_before", "dt_on_or_after", and "dt_after".

The date helper

There is only one: "utc". Makes searching with dates a little easier.

SQL generators

These help generate more complex queries. The can be used in many different parts of "search" in DBIx::Class::ResultSet. See "utc_now", "dt_SQL_pluck", and "dt_SQL_add".


Because these methods are so limited in scope they can be a bit more smart than typical SQL::Abstract trees.

There are "smart types" that this package supports.

Anything not mentioned in the above list will explode, one way or another.


The exact details for the functions your database engine provides.

If a piece of functionality is flagged with ⚠, it means that the feature in question is not portable at all, and only supported on that engine.

SQL Server





ORACLE USERS BEWARE: I run all the tests on all of the databases except Oracle. If you have time to help make dockerprove and/or travisci test against Oracle I'll gladly take those patches. For hints look at and


These people worked on the original implementation, and thus deserve some credit for at least providing me a reference to implement this based off of:

Alexander Hartmaier (abraxxa) for Oracle implementation details
Devin Austin (dhoss) for Pg implementation details
Rafael Kitover (caelum) for providing a test environment with lots of DBs

WHENCE dt_SQL_diff? ^

The original implementation of these date helpers (originally dubbed date operators) included a third operator called "diff". It existed to subtract one date from another and return a duration. After using it a few times and getting bitten every time, I decided to stop using it and instead compare against actual dates always. If someone can come up with a good use case I am interested in re-implementing dt_SQL_diff, but I worry that it will be very unportable and generally not very useful.



   'some_date' => $rs->utc($datetime),

Takes a DateTime object, updates the time_zone to UTC, and formats it according to whatever database engine you are using.

Dies if you pass it a date with a floating time_zone.


Returns a ScalarRef representing the way to get the current date and time in UTC for whatever database engine you are using.


 $rs->dt_before({ -ident => '.start' }, { -ident => '.end' })->all

Takes two values, each an expression of "TYPES".


 $rs->dt_on_or_before({ -ident => '.start' }, DateTime->now)->all

Takes two values, each an expression of "TYPES".


 $rs->dt_on_or_after(DateTime->now, { ident => '.end' })->all

Takes two values, each an expression of "TYPES".


 $rs->dt_after({ ident => '.end' }, $rs->get_column('datecol')->as_query)->all

Takes two values, each an expression of "TYPES".


 # which ones start in 3 minutes?
    { ident => '.start' },
    $rs->dt_SQL_add($rs->utc_now, 'minute', 3)

Takes three arguments: a date conforming to "TYPES", a unit, and an amount. The idea is to add the given unit to the datetime. See your "IMPLEMENTATION" for what units are accepted.


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

Takes two arguments: a date conforming to "TYPES" and a unit. The idea is to pluck a given unit from the datetime. See your "IMPLEMENTATION" for what units are accepted.


Arthur Axel "fREW" Schmidt <>


This software is copyright (c) 2014 by Arthur Axel "fREW" Schmidt.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

syntax highlighting: