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

Download:
DBIx-Class-Helpers-2.022000.tar.gz

Dependencies

Annotate this POD

Website

View/Report Bugs
Module Version: 2.022000   Source   Latest Release: DBIx-Class-Helpers-2.023007

NAME ^

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

VERSION ^

version 2.022000

DESCRIPTION ^

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

METHODS ^

utc

 $rs->search({
   'some_date' => $rs->utc($datetime),
 })->all

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.

utc_now

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

dt_before

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

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

dt_on_or_before

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

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

dt_on_or_after

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

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

dt_after

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

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

dt_SQL_add

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

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.

dt_SQL_pluck

 # 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')],
 )->hri->all

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.

TYPES ^

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.

IMPLEMENTATION ^

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

SQLite

PostgreSQL

MySQL

Oracle

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 https://index.docker.io/u/wnameless/oracle-xe-11g/ and https://github.com/dbsrgits/dbix-class/commit/003e97c53e065e7497a4946c29d5a94e7cf34389.

CONTRIBUTORS ^

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.

AUTHOR ^

Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>

COPYRIGHT AND LICENSE ^

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: