Provided by: libdbix-class-helpers-perl_2.033004-1_all bug

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 ⚠, 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 ⚠

           ·   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 ⚠

       ·   "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 ⚠

           ·   iso_day_of_week

           ·   timezone_as_minutes ⚠

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

           ·   seconds_since_epoch

           ·   fractional_seconds ⚠

       ·   "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 ⚠

           ·   decade ⚠

           ·   day_of_month

           ·   day_of_week

           ·   day_of_year

           ·   seconds_since_epoch

           ·   hour

           ·   iso_day_of_week

           ·   iso_year

           ·   microsecond

           ·   millenium ⚠

           ·   millisecond

           ·   minute

           ·   month

           ·   quarter

           ·   second

           ·   timezone ⚠

           ·   timezone_hour ⚠

           ·   timezone_minute ⚠

           ·   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 ⚠

           ·   decade ⚠

           ·   day

           ·   hour

           ·   microsecond ⚠

           ·   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 ⚠

           ·   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 <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"
       ·   "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) 2018 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.

perl v5.26.1                                2018DBIx::Class::Helper::ResultSet::DateMethods1(3pm)