GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  DBIX::CLASS::HELPER::RESULTSET::DATEMETHODS1 (3)

.ds Aq ’

NAME

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

CONTENTS

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 <B>mostlyB> 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.
o vanilla scalars (1, 2012-12-12 12:12:12)

bound directly as untyped values

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

o DateTime objects

DateTime objects work as if they were passed to utc.

o ScalarRef (\NOW())

As usual in DBIx::Class, ScalarRef’s will be flattened into regular SQL.

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

CWSQL Server

o utc_now - GETUTCDATE <http://msdn.microsoft.com/en-us/library/ms178635.aspx>
o dt_SQL_pluck - DATEPART <http://msdn.microsoft.com/en-us/library/ms174420.aspx>

Supported units
o year
o quarter
o month
o day_of_year
o day_of_month
o week
o day_of_week
o hour
o minute
o second
o millisecond
o nanosecond X
o non_iso_day_of_week

SQL Server offers both ISO_WEEK and weekday. For interop reasons weekday uses the ISO_WEEK version.

o timezone_as_minutes X

o dt_SQL_add - DATEADD <http://msdn.microsoft.com/en-us/library/ms186819.aspx>

Supported units
o year
o quarter
o month
o day
o week
o hour
o minute
o second
o millisecond
o nanosecond X
o iso_day_of_week
o timezone_as_minutes X

CWSQLite

o utc_now - DATETIME(’now’) <https://www.sqlite.org/lang_datefunc.html>
o 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
o month
o day_of_month
o year
o hour
o day_of_year
o minute
o second
o day_of_week
o week
o julian_day X
o seconds_since_epoch
o fractional_seconds X

o dt_SQL_add - DATETIME <https://www.sqlite.org/lang_datefunc.html>

Available units
o day
o hour
o minute
o second
o month
o year

CWPostgreSQL

o utc_now - CURRENT_TIMESTAMP <http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT>
o dt_SQL_pluck - date_part <http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT>

Available units
o century X
o decade X
o day_of_month
o day_of_week
o day_of_year
o seconds_since_epoch
o hour
o iso_day_of_week
o iso_year
o microsecond
o millenium X
o millisecond
o minute
o month
o quarter
o second
o timezone X
o timezone_hour X
o timezone_minute X
o week
o year

o 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
o century X
o decade X
o day
o hour
o microsecond X
o millisecond
o minute
o month
o second
o week
o year

CWMySQL

o utc_now - UTC_TIMESTAMP <https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_utc-timestamp>
o dt_SQL_pluck - EXTRACT <https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract>

Available units
o microsecond
o second
o minute
o hour
o day_of_month
o week
o month
o quarter
o year
o second_microsecond X
o minute_microsecond X
o minute_second X
o hour_microsecond X
o hour_second X
o hour_minute X
o day_microsecond X
o day_second X
o day_minute X
o day_hour X
o year_month X

o dt_SQL_add - DATE_ADD <https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add>

Available units
o microsecond
o second
o minute
o hour
o day
o week
o month
o quarter
o year

CWOracle

<B>ORACLE USERS BEWAREB>: I run all the tests on all of the databases <B>exceptB> 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>.
o utc_now - sys_extract_utc(SYSTIMESTAMP) <http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions167.htm>
o dt_SQL_pluck - EXTRACT

Available units
o second
o minute
o hour
o day_of_month
o month
o year

o 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
o second
o minute
o hour
o 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) 2015 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.

Search for    or go to Top of page |  Section 3 |  Main Index


perl v5.20.3 DBIX::CLASS::HELPER::RESULTSET::DATEMETHODS1 (3) 2015-11-07

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.