Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
DBIx::Class::Helper::RUsertContributedlPerl:Docper::ResultSet::DateMethods1(3)

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.

       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 a , it means	that the
       feature in question is not portable at all, and only supported on that
       engine.

   "SQL	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 a

	   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 a

       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 a

	   o   iso_day_of_week

	   o   timezone_as_minutes a

   "SQLite"
       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 a

	   o   seconds_since_epoch

	   o   fractional_seconds a

       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

   "PostgreSQL"
       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 a

	   o   decade a

	   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 a

	   o   millisecond

	   o   minute

	   o   month

	   o   quarter

	   o   second

	   o   timezone	a

	   o   timezone_hour a

	   o   timezone_minute a

	   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 a

	   o   decade a

	   o   day

	   o   hour

	   o   microsecond a

	   o   millisecond

	   o   minute

	   o   month

	   o   second

	   o   week

	   o   year

   "MySQL"
       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 a

	   o   minute_microsecond a

	   o   minute_second a

	   o   hour_microsecond	a

	   o   hour_second a

	   o   hour_minute a

	   o   day_microsecond a

	   o   day_second a

	   o   day_minute a

	   o   day_hour	a

	   o   year_month a

       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

   "Oracle"
       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) 2017 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.24.1		       DBIx::Class::Helper::ResultSet::DateMethods1(3)

NAME | SYNOPSIS | DESCRIPTION | TYPES | IMPLEMENTATION | CONTRIBUTORS | WHENCE dt_SQL_diff? | METHODS | AUTHOR | COPYRIGHT AND LICENSE

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=DBIx::Class::Helper::ResultSet::DateMethods1&sektion=3&manpath=FreeBSD+12.1-RELEASE+and+Ports>

home | help