NAME¶
DBIx::Class::Helper::ResultSet::DateMethods1 - Work with dates in your RDBMS
nicely
SYNOPSIS¶
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"))
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".
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.
- •
- vanilla scalars (1, "2012-12-12 12:12:12")
bound directly as untyped values
- •
- hashrefs with an "-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".
- •
- "ScalarRef" ("\'NOW()'")
As usual in "DBIx::Class", "ScalarRef"'s will be
flattened into regular SQL.
- •
- "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.
IMPLEMENTATION¶
The exact details for the functions your database engine provides.
If a piece of functionality is flagged with X, it means that the feature in
question is not portable at all, and only supported on that engine.
"SQL Server"¶
- •
- "utc_now" - GETUTCDATE
<http://msdn.microsoft.com/en-us/library/ms178635.aspx>
- •
- "dt_SQL_pluck" - DATEPART
<http://msdn.microsoft.com/en-us/library/ms174420.aspx>
Supported units
- •
- year
- •
- quarter
- •
- month
- •
- day_of_year
- •
- day_of_month
- •
- week
- •
- day_of_week
- •
- hour
- •
- minute
- •
- second
- •
- millisecond
- •
- nanosecond X
- •
- non_iso_day_of_week
SQL Server offers both "ISO_WEEK" and "weekday". For
interop reasons "weekday" uses the "ISO_WEEK"
version.
- •
- timezone_as_minutes X
- •
- "dt_SQL_add" - DATEADD
<http://msdn.microsoft.com/en-us/library/ms186819.aspx>
Supported units
- •
- year
- •
- quarter
- •
- month
- •
- day
- •
- week
- •
- hour
- •
- minute
- •
- second
- •
- millisecond
- •
- nanosecond X
- •
- iso_day_of_week
- •
- timezone_as_minutes X
"SQLite"¶
- •
- "utc_now" - DATETIME('now')
<https://www.sqlite.org/lang_datefunc.html>
- •
- "dt_SQL_pluck" - STRFTIME
<https://www.sqlite.org/lang_datefunc.html>
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
- •
- month
- •
- day_of_month
- •
- year
- •
- hour
- •
- day_of_year
- •
- minute
- •
- second
- •
- day_of_week
- •
- week
- •
- julian_day X
- •
- seconds_since_epoch
- •
- fractional_seconds X
- •
- "dt_SQL_add" - DATETIME
<https://www.sqlite.org/lang_datefunc.html>
Available units
- •
- day
- •
- hour
- •
- minute
- •
- second
- •
- month
- •
- year
"PostgreSQL"¶
- •
- "utc_now" - CURRENT_TIMESTAMP
<http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT>
- •
- "dt_SQL_pluck" - date_part
<http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT>
Available units
- •
- century X
- •
- decade X
- •
- day_of_month
- •
- day_of_week
- •
- day_of_year
- •
- seconds_since_epoch
- •
- hour
- •
- iso_day_of_week
- •
- iso_year
- •
- microsecond
- •
- millenium X
- •
- millisecond
- •
- minute
- •
- month
- •
- quarter
- •
- second
- •
- timezone X
- •
- timezone_hour X
- •
- timezone_minute X
- •
- week
- •
- year
- •
- "dt_SQL_add" - Addition and interval
<http://www.postgresql.org/docs/current/static/functions-datetime.html#OPERATORS-DATETIME-TABLE>
To be clear, it ends up looking like: "("some_column" + 5 *
interval '1 minute')"
Available units
- •
- century X
- •
- decade X
- •
- day
- •
- hour
- •
- microsecond X
- •
- millisecond
- •
- minute
- •
- month
- •
- second
- •
- week
- •
- year
"MySQL"¶
- •
- "utc_now" - UTC_TIMESTAMP
<https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_utc-timestamp>
- •
- "dt_SQL_pluck" - EXTRACT
<https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract>
Available units
- •
- microsecond
- •
- second
- •
- minute
- •
- hour
- •
- day_of_month
- •
- week
- •
- month
- •
- quarter
- •
- year
- •
- second_microsecond X
- •
- minute_microsecond X
- •
- minute_second X
- •
- hour_microsecond X
- •
- hour_second X
- •
- hour_minute X
- •
- day_microsecond X
- •
- day_second X
- •
- day_minute X
- •
- day_hour X
- •
- year_month X
- •
- "dt_SQL_add" - DATE_ADD
<https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add>
Available units
- •
- microsecond
- •
- second
- •
- minute
- •
- hour
- •
- day
- •
- week
- •
- month
- •
- quarter
- •
- year
"Oracle"¶
ORACLE USERS BEWARE: I run all the tests on all of the databases
except "Oracle". If you have time to help make dockerprove
<
https://github.com/frioux/DBIx-Class-Helpers/blob/master/dockerprove>
and/or travisci
<
https://github.com/frioux/DBIx-Class-Helpers/blob/master/.travis.yml>
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>.
- •
- "utc_now" - sys_extract_utc(SYSTIMESTAMP)
<http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions167.htm>
- •
- "dt_SQL_pluck" - EXTRACT
Available units
- •
- second
- •
- minute
- •
- hour
- •
- day_of_month
- •
- month
- •
- year
- •
- "dt_SQL_add" - Addition and NUMTODSINTERVAL
<http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions103.htm>
To be clear, it ends up looking like: "("some_column" +
NUMTODSINTERVAL(4, 'MINUTE')"
Available units
- •
- second
- •
- minute
- •
- hour
- •
- day
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.
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.
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.