1# PODNAME: DBIx::Class::Helper::ResultSet::DateMethods1::Announce 2 3# ABSTRACT: Original Announcement of DBIx::Class::Helper::ResultSet::DateMethods1 4 5__END__ 6 7=pod 8 9=head1 NAME 10 11DBIx::Class::Helper::ResultSet::DateMethods1::Announce - Original Announcement of DBIx::Class::Helper::ResultSet::DateMethods1 12 13=head1 Content 14 15L<A little over three years ago|https://github.com/frioux/dbic-withdates/commit/5e6893d4cb873eca75118061c104bed9b891dee0> 16I got inspired while on vacation to Crested Butte, CO and started a branch 17in DBIC called merely, "date-ops." The idea was to allow users to call 18various date functions, portably, directly in DBIC. With some help from 19some people who use other databases, I got it working with SQL Server, 20SQLite, PostgreSQL, MySQL, and Oracle. 21 22Unfortunately after we finished it ( 23L<about six months after I started|https://github.com/frioux/dbic-withdates/commit/fcb5b33a58426ee9d4b87fd84144d05d7a1cf9e9> 24) it merely languished. There were some technical issues we never got 25around to ironing out, mostly because it wasn't clear to us what the 26cost of not taking care of them would be. 27 28Fast forward a few more months and I was working on a greenfield project 29at work. I wanted to do some date math in the database, so far I did all 30of my development against SQLite but deployed to SQL Server, and it looked 31like the date ops were my solution. I decided that given that I was the 32primary author of them, I could live with deploying them to production. 33I did exactly that and had pretty much no problems. Well, no problems 34until I had to upgrade DBIC. Every time I needed to upgrade DBIC I had 35to merge/rebase the branch. It turned out to be much more work than I 36bargained for, and I ended up just never updating DBIC. 37 38At some point ( 39L<just under a month ago|https://github.com/frioux/DBIx-Class-Helpers/commit/5fefda2e5dafb0b78e9ee5a687fb698899d8d2ff> 40) I decided that I 41needed to upgrade DBIC and that maintaining these date ops was no longer 42tenable. Armed with three more years of experience than I had when I 43started I embarked on converting the date ops to date methods, that would 44work as Helpers. In addition to not being core, so I could release at 45my own pace, I could also version the API, so if I end up making some 46critical mistakes or needing to break the API for some features in the 47future, I can merely release C<::DateMethods2>. So without further ado: 48 49=head1 Announcing C<DBIx::Class::Helper::ResultSet::DateMethods1> 50 51Do you store dates in your database? Do you ever want to manipulate them 52efficiently? Well here's your solution! 53 54First, how do you search in a more comprehensible way? 55 56 $rs->dt_on_or_before( 57 { -ident => '.when_created' }, 58 DateTime->now->subtract(days => 7), 59 ); 60 61C<dt_on_or_before> (as well as C<dt_before>, C<dt_on_or_after>, or C<dt_after>) 62merely aliases C<< <= >>, C<< < >>, C<< >= >>, and C<< > >>, respectively. 63Instead of trying to think about the numerical meaning of a date on a timeline, 64just use these named methods. In addition to the nicer name, they can take 65L<DateTime> object (which are automatically converted to UTC), and autoprepend 66L<DBIx::Class::ResultSet/current_source_alias> when passed an C<-ident> that 67starts with a C<.>. You can pass any of a value, a column (via C<-ident>), a 68subquery, literal sql, or a C<DateTime> object to either parameter slots of 69these methods. 70 71Second, how do I really leverage this module to do stuff with dates in my 72database? 73 74Here's a query I originally wrote with date ops. Basically it groups some 75columns by some "date parts" like year, month, day, etc. You can use it to make 76nice reports of things like how many things have been done per month, or maybe 77find out if the system is more busy in the summer: 78 79 $rs->search(undef, { 80 columns => { 81 count => '*', 82 year => $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'), 83 month => $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'), 84 }, 85 group_by => [ 86 $rs->dt_SQL_pluck({ -ident => '.start' }, 'year'), 87 $rs->dt_SQL_pluck({ -ident => '.start' }, 'month'), 88 ], 89 )->hri->all 90 91I use that exact query (though I give the user a UI for which dateparts 92to include) in my system, and it works on SQL Server and SQLite, and 93it's fast. Awesome. 94 95Or how about a query to discover how many issues were resolved before the next 96full day after their creation? Check it out: 97 98 # note that 'day', 1 should also work 99 $rs->dt_before( 100 { -ident => '.resolution' }, 101 $rs->dt_SQL_add({ -ident => '.creation' }, 'hour', 24), 102 )->all 103 104Both of the above queries work on all of the supported datebases! 105 106Third, some little helpers to extend the above. 107 108On top of those things, I also throw in a couple other handy methods. One, 109C<utc> converts a DateTime object to a string, in the UTC timezone. Hopefully 110you shouldn't need it directly, but I've already ended up using it in places 111where our code forced me to return a simple hash to get merged into a search 112query, instead of letting me call methods on an RS. 113 114Another lagniappe is C<utc_now> which returns some literal sql that resolves to 115the current date and time in UTC on your database. You can pass it in to search 116just like you would datetime. So if your server and your database have in sync 117clocks, these would do the same thing: 118 119 $rs->dt_on_or_before( 120 { -ident => '.when_created' }, 121 DateTime->now->subtract(days => 7), 122 ); 123 124 $rs->dt_on_or_before( 125 { -ident => '.when_created' }, 126 $rs->dt_SQL_add($rs->utc->now, 'day', -7), 127 ); 128 129(NOTE: many people seem to hold suspect the idea that the clock is correct on a 130given server. If you can't trust the clock of a server, you probably can't 131trust the server. Use NTP.) 132 133And that's it. I hope you can use and enjoy these helpers! 134 135=head1 SEE ALSO 136 137L<original blog post|https://blog.afoolishmanifesto.com/posts/announcing-helper-resultset-datemethods1/> 138 139=head1 AUTHOR 140 141Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com> 142 143=head1 COPYRIGHT AND LICENSE 144 145This software is copyright (c) 2020 by Arthur Axel "fREW" Schmidt. 146 147This is free software; you can redistribute it and/or modify it under 148the same terms as the Perl 5 programming language system itself. 149 150=cut 151