.\" 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::Announcement 3pm" .TH DBIx::Class::Helper::ResultSet::DateMethods1::Announcement 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::Announce \- Original Announcement of DBIx::Class::Helper::ResultSet::DateMethods1 .SH "Content" .IX Header "Content" A little over three years ago I got inspired while on vacation to Crested Butte, \s-1CO\s0 and started a branch in \s-1DBIC\s0 called merely, \*(L"date-ops.\*(R" The idea was to allow users to call various date functions, portably, directly in \s-1DBIC.\s0 With some help from some people who use other databases, I got it working with \s-1SQL\s0 Server, SQLite, PostgreSQL, MySQL, and Oracle. .PP Unfortunately after we finished it ( about six months after I started ) it merely languished. There were some technical issues we never got around to ironing out, mostly because it wasn't clear to us what the cost of not taking care of them would be. .PP Fast forward a few more months and I was working on a greenfield project at work. I wanted to do some date math in the database, so far I did all of my development against SQLite but deployed to \s-1SQL\s0 Server, and it looked like the date ops were my solution. I decided that given that I was the primary author of them, I could live with deploying them to production. I did exactly that and had pretty much no problems. Well, no problems until I had to upgrade \s-1DBIC.\s0 Every time I needed to upgrade \s-1DBIC I\s0 had to merge/rebase the branch. It turned out to be much more work than I bargained for, and I ended up just never updating \s-1DBIC.\s0 .PP At some point ( just under a month ago ) I decided that I needed to upgrade \s-1DBIC\s0 and that maintaining these date ops was no longer tenable. Armed with three more years of experience than I had when I started I embarked on converting the date ops to date methods, that would work as Helpers. In addition to not being core, so I could release at my own pace, I could also version the \s-1API,\s0 so if I end up making some critical mistakes or needing to break the \s-1API\s0 for some features in the future, I can merely release \f(CW\*(C`::DateMethods2\*(C'\fR. So without further ado: .ie n .SH "Announcing ""DBIx::Class::Helper::ResultSet::DateMethods1""" .el .SH "Announcing \f(CWDBIx::Class::Helper::ResultSet::DateMethods1\fP" .IX Header "Announcing DBIx::Class::Helper::ResultSet::DateMethods1" Do you store dates in your database? Do you ever want to manipulate them efficiently? Well here's your solution! .PP First, how do you search in a more comprehensible way? .PP .Vb 4 \& $rs\->dt_on_or_before( \& { \-ident => \*(Aq.when_created\*(Aq }, \& DateTime\->now\->subtract(days => 7), \& ); .Ve .PP \&\f(CW\*(C`dt_on_or_before\*(C'\fR (as well as \f(CW\*(C`dt_before\*(C'\fR, \f(CW\*(C`dt_on_or_after\*(C'\fR, or \f(CW\*(C`dt_after\*(C'\fR) merely aliases \f(CW\*(C`<=\*(C'\fR, \f(CW\*(C`<\*(C'\fR, \f(CW\*(C`>=\*(C'\fR, and \f(CW\*(C`>\*(C'\fR, respectively. Instead of trying to think about the numerical meaning of a date on a timeline, just use these named methods. In addition to the nicer name, they can take DateTime object (which are automatically converted to \s-1UTC\s0), and autoprepend \&\*(L"current_source_alias\*(R" in DBIx::Class::ResultSet when passed an \f(CW\*(C`\-ident\*(C'\fR that starts with a \f(CW\*(C`.\*(C'\fR. You can pass any of a value, a column (via \f(CW\*(C`\-ident\*(C'\fR), a subquery, literal sql, or a \f(CW\*(C`DateTime\*(C'\fR object to either parameter slots of these methods. .PP Second, how do I really leverage this module to do stuff with dates in my database? .PP Here's a query I originally wrote with date ops. Basically it groups some columns by some \*(L"date parts\*(R" like year, month, day, etc. You can use it to make nice reports of things like how many things have been done per month, or maybe find out if the system is more busy in the summer: .PP .Vb 11 \& $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), \& ], \& )\->hri\->all .Ve .PP I use that exact query (though I give the user a \s-1UI\s0 for which dateparts to include) in my system, and it works on \s-1SQL\s0 Server and SQLite, and it's fast. Awesome. .PP Or how about a query to discover how many issues were resolved before the next full day after their creation? Check it out: .PP .Vb 5 \& # note that \*(Aqday\*(Aq, 1 should also work \& $rs\->dt_before( \& { \-ident => \*(Aq.resolution\*(Aq }, \& $rs\->dt_SQL_add({ \-ident => \*(Aq.creation\*(Aq }, \*(Aqhour\*(Aq, 24), \& )\->all .Ve .PP Both of the above queries work on all of the supported datebases! .PP Third, some little helpers to extend the above. .PP On top of those things, I also throw in a couple other handy methods. One, \&\f(CW\*(C`utc\*(C'\fR converts a DateTime object to a string, in the \s-1UTC\s0 timezone. Hopefully you shouldn't need it directly, but I've already ended up using it in places where our code forced me to return a simple hash to get merged into a search query, instead of letting me call methods on an \s-1RS.\s0 .PP Another lagniappe is \f(CW\*(C`utc_now\*(C'\fR which returns some literal sql that resolves to the current date and time in \s-1UTC\s0 on your database. You can pass it in to search just like you would datetime. So if your server and your database have in sync clocks, these would do the same thing: .PP .Vb 4 \& $rs\->dt_on_or_before( \& { \-ident => \*(Aq.when_created\*(Aq }, \& DateTime\->now\->subtract(days => 7), \& ); \& \& $rs\->dt_on_or_before( \& { \-ident => \*(Aq.when_created\*(Aq }, \& $rs\->dt_SQL_add($rs\->utc\->now, \*(Aqday\*(Aq, \-7), \& ); .Ve .PP (\s-1NOTE:\s0 many people seem to hold suspect the idea that the clock is correct on a given server. If you can't trust the clock of a server, you probably can't trust the server. Use \s-1NTP.\s0) .PP And that's it. I hope you can use and enjoy these helpers! .SH "SEE ALSO" .IX Header "SEE ALSO" original blog post .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.