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'; __PACKAGE__->load_components('Helper::ResultSet::DateMethods1'); # 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.
These, like typical ResultSet methods, return another ResultSet. See "dt_before", "dt_on_or_before", "dt_on_or_after", and "dt_after".
There is only one: "utc". Makes searching with dates a little easier.
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.
SQL::Abstract
There are "smart types" that this package supports.
vanilla scalars (1, 2012-12-12 12:12:12)
1
2012-12-12 12:12:12
bound directly as untyped values
hashrefs with an -ident ({ -ident => '.foo' })
-ident
{ -ident => '.foo' }
As usual this gets flattened into a column. The one special feature in this module is that columns starting with a dot will automatically be prefixed with "current_source_alias" in DBIx::Class::ResultSet.
DateTime objects
DateTime objects work as if they were passed to "utc".
DateTime
ScalarRef (\'NOW()')
ScalarRef
\'NOW()'
As usual in DBIx::Class, ScalarRef's will be flattened into regular SQL.
DBIx::Class
ArrayRefRef (\["SELECT foo FROM bar WHERE id = ?", [{}, 1]])
ArrayRefRef
\["SELECT foo FROM bar WHERE id = ?", [{}, 1]]
As usual in DBIx::Class, ArrayRefRef's will be flattened into SQL with bound values.
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
"utc_now" - GETUTCDATE
"dt_SQL_pluck" - DATEPART
Supported units
year
quarter
month
day_of_year
day_of_month
week
day_of_week
hour
minute
second
millisecond
nanosecond ⚠
non_iso_day_of_week
SQL Server offers both ISO_WEEK and weekday. For interop reasons weekday uses the ISO_WEEK version.
ISO_WEEK
weekday
timezone_as_minutes ⚠
"dt_SQL_add" - DATEADD
day
iso_day_of_week
SQLite
"utc_now" - DATETIME('now')
"dt_SQL_pluck" - STRFTIME
Note: SQLite's pluck implementation pads numbers with zeros, because it is implemented on based on a formatting function. If you want your code to work on SQLite you'll need to strip off (or just numify) what you get out of the database first.
Available units
julian_day ⚠
seconds_since_epoch
fractional_seconds ⚠
"dt_SQL_add" - DATETIME
PostgreSQL
"utc_now" - CURRENT_TIMESTAMP
"dt_SQL_pluck" - date_part
century ⚠
decade ⚠
iso_year
microsecond
millenium ⚠
timezone ⚠
timezone_hour ⚠
timezone_minute ⚠
"dt_SQL_add" - Addition and interval
To be clear, it ends up looking like: ("some_column" + 5 * interval '1 minute')
("some_column" + 5 * interval '1 minute')
microsecond ⚠
MySQL
"utc_now" - UTC_TIMESTAMP
"dt_SQL_pluck" - EXTRACT
second_microsecond ⚠
minute_microsecond ⚠
minute_second ⚠
hour_microsecond ⚠
hour_second ⚠
hour_minute ⚠
day_microsecond ⚠
day_second ⚠
day_minute ⚠
day_hour ⚠
year_month ⚠
"dt_SQL_add" - DATE_ADD
Oracle
"utc_now" - sys_extract_utc(SYSTIMESTAMP)
"dt_SQL_add" - Addition and NUMTODSINTERVAL
To be clear, it ends up looking like: ("some_column" + NUMTODSINTERVAL(4, 'MINUTE')
("some_column" + NUMTODSINTERVAL(4, 'MINUTE')
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:
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.
"diff"
dt_SQL_diff
$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.
time_zone
UTC
Dies if you pass it a date with a floating time_zone.
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
$rs->dt_on_or_after(DateTime->now, { ident => '.end' })->all
$rs->dt_after({ ident => '.end' }, $rs->get_column('datecol')->as_query)->all
# 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.
# 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.
Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
This software is copyright (c) 2016 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.
To install DBIx::Class::Helpers, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Class::Helpers
CPAN shell
perl -MCPAN -e shell install DBIx::Class::Helpers
For more information on module installation, please visit the detailed CPAN module installation guide.