.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.43) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" ======================================================================== .\" .IX Title "DBIx::Class::Helper::ResultSet::DateMethods1 3pm" .TH DBIx::Class::Helper::ResultSet::DateMethods1 3pm "2022-12-06" "perl v5.36.0" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" DBIx::Class::Helper::ResultSet::DateMethods1 \- Work with dates in your RDBMS nicely .SH "SYNOPSIS" .IX Header "SYNOPSIS" .Vb 1 \& package MySchema::ResultSet::Bar; \& \& use strict; \& use warnings; \& \& use parent \*(AqDBIx::Class::ResultSet\*(Aq; \& \& _\|_PACKAGE_\|_\->load_components(\*(AqHelper::ResultSet::DateMethods1\*(Aq); \& \& # in code using resultset \& \& # get count per year/month \& $rs\->search(undef, { \& columns => { \& count => \*(Aq*\*(Aq, \& year => $rs\->dt_SQL_pluck({ \-ident => \*(Aq.start\*(Aq }, \*(Aqyear\*(Aq), \& month => $rs\->dt_SQL_pluck({ \-ident => \*(Aq.start\*(Aq }, \*(Aqmonth\*(Aq), \& }, \& group_by => [ \& $rs\->dt_SQL_pluck({ \-ident => \*(Aq.start\*(Aq }, \*(Aqyear\*(Aq), \& $rs\->dt_SQL_pluck({ \-ident => \*(Aq.start\*(Aq }, \*(Aqmonth\*(Aq), \& ], \& }); \& \& # 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(\*(Aq%m\*(Aq, "me"."start"), STRFTIME(\*(Aq%Y\*(Aq, "me"."start") FROM "HasDateOps" "me" GROUP BY STRFTIME(\*(Aq%Y\*(Aq, "me"."start"), STRFTIME(\*(Aq%m\*(Aq, "me"."start")) .Ve .SH "DESCRIPTION" .IX Header "DESCRIPTION" See \*(L"\s-1NOTE\*(R"\s0 in DBIx::Class::Helper::ResultSet for a nice way to apply it to your entire schema. .PP This ResultSet component gives the user tools to do \fBmostly\fR portable date manipulation in the database. Before embarking on a cross database project, take a look at \*(L"\s-1IMPLEMENTATION\*(R"\s0 to see what might break on switching databases. .PP This package has a few types of methods. .IP "Search Shortcuts" 4 .IX Item "Search Shortcuts" These, like typical ResultSet methods, return another ResultSet. See \&\*(L"dt_before\*(R", \*(L"dt_on_or_before\*(R", \*(L"dt_on_or_after\*(R", and \*(L"dt_after\*(R". .IP "The date helper" 4 .IX Item "The date helper" There is only one: \*(L"utc\*(R". Makes searching with dates a little easier. .IP "\s-1SQL\s0 generators" 4 .IX Item "SQL generators" These help generate more complex queries. The can be used in many different parts of \*(L"search\*(R" in DBIx::Class::ResultSet. See \*(L"utc_now\*(R", \*(L"dt_SQL_pluck\*(R", and \*(L"dt_SQL_add\*(R". .SH "TYPES" .IX Header "TYPES" Because these methods are so limited in scope they can be a bit more smart than typical \f(CW\*(C`SQL::Abstract\*(C'\fR trees. .PP There are \*(L"smart types\*(R" that this package supports. .IP "\(bu" 4 vanilla scalars (\f(CW1\fR, \f(CW\*(C`2012\-12\-12 12:12:12\*(C'\fR) .Sp bound directly as untyped values .IP "\(bu" 4 hashrefs with an \f(CW\*(C`\-ident\*(C'\fR (\f(CW\*(C`{ \-ident => \*(Aq.foo\*(Aq }\*(C'\fR) .Sp 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 \&\*(L"current_source_alias\*(R" in DBIx::Class::ResultSet. .IP "\(bu" 4 DateTime objects .Sp \&\f(CW\*(C`DateTime\*(C'\fR objects work as if they were passed to \*(L"utc\*(R". .IP "\(bu" 4 \&\f(CW\*(C`ScalarRef\*(C'\fR (\f(CW\*(C`\e\*(AqNOW()\*(Aq\*(C'\fR) .Sp As usual in \f(CW\*(C`DBIx::Class\*(C'\fR, \f(CW\*(C`ScalarRef\*(C'\fR's will be flattened into regular \s-1SQL.\s0 .IP "\(bu" 4 \&\f(CW\*(C`ArrayRefRef\*(C'\fR (\f(CW\*(C`\e["SELECT foo FROM bar WHERE id = ?", [{}, 1]]\*(C'\fR) .Sp As usual in \f(CW\*(C`DBIx::Class\*(C'\fR, \f(CW\*(C`ArrayRefRef\*(C'\fR's will be flattened into \s-1SQL\s0 with bound values. .PP Anything not mentioned in the above list will explode, one way or another. .SH "IMPLEMENTATION" .IX Header "IMPLEMENTATION" The exact details for the functions your database engine provides. .PP 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. .ie n .SS """SQL Server""" .el .SS "\f(CWSQL Server\fP" .IX Subsection "SQL Server" .IP "\(bu" 4 \&\*(L"utc_now\*(R" \- \s-1GETUTCDATE\s0 .IP "\(bu" 4 \&\*(L"dt_SQL_pluck\*(R" \- \s-1DATEPART\s0 .Sp Supported units .RS 4 .IP "\(bu" 4 year .IP "\(bu" 4 quarter .IP "\(bu" 4 month .IP "\(bu" 4 day_of_year .IP "\(bu" 4 day_of_month .IP "\(bu" 4 week .IP "\(bu" 4 day_of_week .IP "\(bu" 4 hour .IP "\(bu" 4 minute .IP "\(bu" 4 second .IP "\(bu" 4 millisecond .IP "\(bu" 4 nanosecond ⚠ .IP "\(bu" 4 non_iso_day_of_week .Sp \&\s-1SQL\s0 Server offers both \f(CW\*(C`ISO_WEEK\*(C'\fR and \f(CW\*(C`weekday\*(C'\fR. For interop reasons \&\f(CW\*(C`weekday\*(C'\fR uses the \f(CW\*(C`ISO_WEEK\*(C'\fR version. .IP "\(bu" 4 timezone_as_minutes ⚠ .RE .RS 4 .RE .IP "\(bu" 4 \&\*(L"dt_SQL_add\*(R" \- \s-1DATEADD\s0 .Sp Supported units .RS 4 .IP "\(bu" 4 year .IP "\(bu" 4 quarter .IP "\(bu" 4 month .IP "\(bu" 4 day .IP "\(bu" 4 week .IP "\(bu" 4 hour .IP "\(bu" 4 minute .IP "\(bu" 4 second .IP "\(bu" 4 millisecond .IP "\(bu" 4 nanosecond ⚠ .IP "\(bu" 4 iso_day_of_week .IP "\(bu" 4 timezone_as_minutes ⚠ .RE .RS 4 .RE .ie n .SS """SQLite""" .el .SS "\f(CWSQLite\fP" .IX Subsection "SQLite" .IP "\(bu" 4 \&\*(L"utc_now\*(R" \- \s-1DATETIME\s0('now') .IP "\(bu" 4 \&\*(L"dt_SQL_pluck\*(R" \- \s-1STRFTIME\s0 .Sp Note: \f(CW\*(C`SQLite\*(C'\fR'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. .Sp Available units .RS 4 .IP "\(bu" 4 month .IP "\(bu" 4 day_of_month .IP "\(bu" 4 year .IP "\(bu" 4 hour .IP "\(bu" 4 day_of_year .IP "\(bu" 4 minute .IP "\(bu" 4 second .IP "\(bu" 4 day_of_week .IP "\(bu" 4 week .IP "\(bu" 4 julian_day ⚠ .IP "\(bu" 4 seconds_since_epoch .IP "\(bu" 4 fractional_seconds ⚠ .RE .RS 4 .RE .IP "\(bu" 4 \&\*(L"dt_SQL_add\*(R" \- \s-1DATETIME\s0 .Sp Available units .RS 4 .IP "\(bu" 4 day .IP "\(bu" 4 hour .IP "\(bu" 4 minute .IP "\(bu" 4 second .IP "\(bu" 4 month .IP "\(bu" 4 year .RE .RS 4 .RE .ie n .SS """PostgreSQL""" .el .SS "\f(CWPostgreSQL\fP" .IX Subsection "PostgreSQL" .IP "\(bu" 4 \&\*(L"utc_now\*(R" \- \s-1CURRENT_TIMESTAMP\s0 .IP "\(bu" 4 \&\*(L"dt_SQL_pluck\*(R" \- date_part .Sp Available units .RS 4 .IP "\(bu" 4 century ⚠ .IP "\(bu" 4 decade ⚠ .IP "\(bu" 4 day_of_month .IP "\(bu" 4 day_of_week .IP "\(bu" 4 day_of_year .IP "\(bu" 4 seconds_since_epoch .IP "\(bu" 4 hour .IP "\(bu" 4 iso_day_of_week .IP "\(bu" 4 iso_year .IP "\(bu" 4 microsecond .IP "\(bu" 4 millenium ⚠ .IP "\(bu" 4 millisecond .IP "\(bu" 4 minute .IP "\(bu" 4 month .IP "\(bu" 4 quarter .IP "\(bu" 4 second .IP "\(bu" 4 timezone ⚠ .IP "\(bu" 4 timezone_hour ⚠ .IP "\(bu" 4 timezone_minute ⚠ .IP "\(bu" 4 week .IP "\(bu" 4 year .RE .RS 4 .RE .IP "\(bu" 4 \&\*(L"dt_SQL_add\*(R" \- Addition and interval .Sp To be clear, it ends up looking like: \&\f(CW\*(C`("some_column" + 5 * interval \*(Aq1 minute\*(Aq)\*(C'\fR .Sp Available units .RS 4 .IP "\(bu" 4 century ⚠ .IP "\(bu" 4 decade ⚠ .IP "\(bu" 4 day .IP "\(bu" 4 hour .IP "\(bu" 4 microsecond ⚠ .IP "\(bu" 4 millisecond .IP "\(bu" 4 minute .IP "\(bu" 4 month .IP "\(bu" 4 second .IP "\(bu" 4 week .IP "\(bu" 4 year .RE .RS 4 .RE .ie n .SS """MySQL""" .el .SS "\f(CWMySQL\fP" .IX Subsection "MySQL" .IP "\(bu" 4 \&\*(L"utc_now\*(R" \- \s-1UTC_TIMESTAMP\s0 .IP "\(bu" 4 \&\*(L"dt_SQL_pluck\*(R" \- \s-1EXTRACT\s0 .Sp Available units .RS 4 .IP "\(bu" 4 microsecond .IP "\(bu" 4 second .IP "\(bu" 4 minute .IP "\(bu" 4 hour .IP "\(bu" 4 day_of_month .IP "\(bu" 4 week .IP "\(bu" 4 month .IP "\(bu" 4 quarter .IP "\(bu" 4 year .IP "\(bu" 4 second_microsecond ⚠ .IP "\(bu" 4 minute_microsecond ⚠ .IP "\(bu" 4 minute_second ⚠ .IP "\(bu" 4 hour_microsecond ⚠ .IP "\(bu" 4 hour_second ⚠ .IP "\(bu" 4 hour_minute ⚠ .IP "\(bu" 4 day_microsecond ⚠ .IP "\(bu" 4 day_second ⚠ .IP "\(bu" 4 day_minute ⚠ .IP "\(bu" 4 day_hour ⚠ .IP "\(bu" 4 year_month ⚠ .RE .RS 4 .RE .IP "\(bu" 4 \&\*(L"dt_SQL_add\*(R" \- \s-1DATE_ADD\s0 .Sp Available units .RS 4 .IP "\(bu" 4 microsecond .IP "\(bu" 4 second .IP "\(bu" 4 minute .IP "\(bu" 4 hour .IP "\(bu" 4 day .IP "\(bu" 4 week .IP "\(bu" 4 month .IP "\(bu" 4 quarter .IP "\(bu" 4 year .RE .RS 4 .RE .ie n .SS """Oracle""" .el .SS "\f(CWOracle\fP" .IX Subsection "Oracle" .IP "\(bu" 4 \&\*(L"utc_now\*(R" \- sys_extract_utc(\s-1SYSTIMESTAMP\s0) .IP "\(bu" 4 \&\*(L"dt_SQL_pluck\*(R" \- \s-1EXTRACT\s0 .Sp Available units .RS 4 .IP "\(bu" 4 second .IP "\(bu" 4 minute .IP "\(bu" 4 hour .IP "\(bu" 4 day_of_month .IP "\(bu" 4 month .IP "\(bu" 4 year .RE .RS 4 .RE .IP "\(bu" 4 \&\*(L"dt_SQL_add\*(R" \- Addition and \s-1NUMTODSINTERVAL\s0 .Sp To be clear, it ends up looking like: \&\f(CW\*(C`("some_column" + NUMTODSINTERVAL(4, \*(AqMINUTE\*(Aq)\*(C'\fR .Sp Available units .RS 4 .IP "\(bu" 4 second .IP "\(bu" 4 minute .IP "\(bu" 4 hour .IP "\(bu" 4 day .RE .RS 4 .RE .SH "CONTRIBUTORS" .IX Header "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: .IP "Alexander Hartmaier (abraxxa) for Oracle implementation details" 4 .IX Item "Alexander Hartmaier (abraxxa) for Oracle implementation details" .PD 0 .IP "Devin Austin (dhoss) for Pg implementation details" 4 .IX Item "Devin Austin (dhoss) for Pg implementation details" .IP "Rafael Kitover (caelum) for providing a test environment with lots of DBs" 4 .IX Item "Rafael Kitover (caelum) for providing a test environment with lots of DBs" .PD .SH "WHENCE dt_SQL_diff?" .IX Header "WHENCE dt_SQL_diff?" The original implementation of these date helpers (originally dubbed date operators) included a third operator called \f(CW"diff"\fR. 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 \f(CW\*(C`dt_SQL_diff\*(C'\fR, but I worry that it will be very unportable and generally not very useful. .SH "METHODS" .IX Header "METHODS" .SS "utc" .IX Subsection "utc" .Vb 3 \& $rs\->search({ \& \*(Aqsome_date\*(Aq => $rs\->utc($datetime), \& })\->all .Ve .PP Takes a DateTime object, updates the \f(CW\*(C`time_zone\*(C'\fR to \f(CW\*(C`UTC\*(C'\fR, and formats it according to whatever database engine you are using. .PP Dies if you pass it a date with a \f(CW\*(C`floating time_zone\*(C'\fR. .SS "utc_now" .IX Subsection "utc_now" Returns a \f(CW\*(C`ScalarRef\*(C'\fR representing the way to get the current date and time in \f(CW\*(C`UTC\*(C'\fR for whatever database engine you are using. .SS "dt_before" .IX Subsection "dt_before" .Vb 1 \& $rs\->dt_before({ \-ident => \*(Aq.start\*(Aq }, { \-ident => \*(Aq.end\*(Aq })\->all .Ve .PP Takes two values, each an expression of \*(L"\s-1TYPES\*(R"\s0. .SS "dt_on_or_before" .IX Subsection "dt_on_or_before" .Vb 1 \& $rs\->dt_on_or_before({ \-ident => \*(Aq.start\*(Aq }, DateTime\->now)\->all .Ve .PP Takes two values, each an expression of \*(L"\s-1TYPES\*(R"\s0. .SS "dt_on_or_after" .IX Subsection "dt_on_or_after" .Vb 1 \& $rs\->dt_on_or_after(DateTime\->now, { ident => \*(Aq.end\*(Aq })\->all .Ve .PP Takes two values, each an expression of \*(L"\s-1TYPES\*(R"\s0. .SS "dt_after" .IX Subsection "dt_after" .Vb 1 \& $rs\->dt_after({ ident => \*(Aq.end\*(Aq }, $rs\->get_column(\*(Aqdatecol\*(Aq)\->as_query)\->all .Ve .PP Takes two values, each an expression of \*(L"\s-1TYPES\*(R"\s0. .SS "dt_SQL_add" .IX Subsection "dt_SQL_add" .Vb 5 \& # which ones start in 3 minutes? \& $rs\->dt_on_or_after( \& { ident => \*(Aq.start\*(Aq }, \& $rs\->dt_SQL_add($rs\->utc_now, \*(Aqminute\*(Aq, 3) \& )\->all .Ve .PP Takes three arguments: a date conforming to \*(L"\s-1TYPES\*(R"\s0, a unit, and an amount. The idea is to add the given unit to the datetime. See your \*(L"\s-1IMPLEMENTATION\*(R"\s0 for what units are accepted. .SS "dt_SQL_subtract" .IX Subsection "dt_SQL_subtract" Same as dt_SQL_add, but subtracts the amount. .PP Only confirmed to work with Postgres, MySQL and SQLite. It should work with Oracle and \s-1MSSQL,\s0 but due to lack of access to those \s-1DB\s0 engines the implementation was done only based on docs. .PP This method was implemented by Thomas Klausner and sponsored by Ctrl O . .SS "dt_SQL_pluck" .IX Subsection "dt_SQL_pluck" .Vb 8 \& # get count per year \& $rs\->search(undef, { \& columns => { \& count => \*(Aq*\*(Aq, \& year => $rs\->dt_SQL_pluck({ \-ident => \*(Aq.start\*(Aq }, \*(Aqyear\*(Aq), \& }, \& group_by => [$rs\->dt_SQL_pluck({ \-ident => \*(Aq.start\*(Aq }, \*(Aqyear\*(Aq)], \& })\->hri\->all .Ve .PP Takes two arguments: a date conforming to \*(L"\s-1TYPES\*(R"\s0 and a unit. The idea is to pluck a given unit from the datetime. See your \*(L"\s-1IMPLEMENTATION\*(R"\s0 for what units are accepted. .SH "AUTHOR" .IX Header "AUTHOR" Arthur Axel \*(L"fREW\*(R" Schmidt .SH "COPYRIGHT AND LICENSE" .IX Header "COPYRIGHT AND LICENSE" This software is copyright (c) 2020 by Arthur Axel \*(L"fREW\*(R" Schmidt. .PP This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.