bgroper has asked for the wisdom of the Perl Monks concerning the following question:
Hey Monks
I have a table of dated transactions, and I'm needing to detect any transactions dated more than 2 months into the future.
The following proof-of-conecpt code snippet is my attempt to achieve this result.
The $date variable will be filled using "SELECT date FROM table ORDER BY date DESC LIMIT 1"
The PoC seems to work, BUT can any monks help me do this a-better-way ?
TIA's for any tips or clues.
#!/usr/bin/perl
use strict;
use warnings;
use DateTime;
my $date = DateTime->new(
time_zone => "UTC",
year => 2019,
month => 8,
day => 10,
);
my $now = DateTime->now;
my $delta = $now - $date;
my $months = $delta->{'months'};
my $days = $delta->{'days'};
print "Delta = $months months, and $days days\n";
print "*** Alert ***\n" if ($months < -2);
exit();
__END__
Re: Months between dates ?
by shadowsong (Pilgrim) on Jun 10, 2019 at 08:04 UTC
|
Hi bgroper
Although we love doing stuff in perl – if it's all the same, why not adjust your query so that your result set contains only that which you're interested in? a la SELECT statement below:
-- table set up
CREATE TABLE test(
test_id INTEGER
,date_tstamp TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP
,CONSTRAINT test_test_id_pkey PRIMARY KEY (test_id)
);
INSERT INTO test
VALUES (1,now())
,(2,now())
,(3,now())
,(4,now() + INTERVAL '60 days')
,(5,now() + INTERVAL '70 days')
;
-- select statement
SELECT date_tstamp
FROM test
WHERE date_tstamp > (now() + INTERVAL '59 days');
Best regards, Shadowsong | [reply] [d/l] |
|
Thanks for the excellent suggestion.
I agree, its much easier to do this with the SELECT statement, and then simply show the *Alert* if SELECT returns any result/s.
Nice. :-)
| [reply] |
Re: Months between dates ?
by haukex (Archbishop) on Jun 10, 2019 at 07:38 UTC
|
First of all, note that by writing $delta->{'months'} you are reaching into the internals of the object, which bypasses any accessors and may break if the field names happen to change. So don't do that and use only the accessors instead. Second, note the section "Math Methods" in DateTime and the documentation of DateTime::Duration: Because the number of days per month is not fixed, IMHO it's better to tell the modules exactly what kind of math you want to do. For instance, in your case, you might want the ->delta_md method. Note that it returns an absolute difference, which is why an additional check of $date>$now is necessary, but personally I don't mind being explicit.
use warnings;
use strict;
use DateTime;
use DateTime::Format::Human::Duration;
my $now = DateTime->new( time_zone => "UTC",
year => 2019, month => 6, day => 10 );
my $fmt = DateTime::Format::Human::Duration->new();
for my $mon ( 4, 6, 8 ) {
for my $day ( 1, 9 , 10, 11, 19 ) {
my $date = DateTime->new( time_zone => "UTC",
year => 2019, month => $mon, day => $day );
my $delta = $now->delta_md($date);
my ($months,$days) = $delta->in_units(qw/ months days /);
print $now->ymd, " to ", $date->ymd, " is ",
$fmt->format_duration($delta,
units=>[qw/months days/]);
if ( $date>$now && ( $months>2 || $months==2 && $days>0 ) ) {
print " *** Alert ***";
}
print "\n";
}
}
__END__
2019-06-10 to 2019-04-01 is 2 months and 9 days
2019-06-10 to 2019-04-09 is 2 months and 1 day
2019-06-10 to 2019-04-10 is 2 months
2019-06-10 to 2019-04-11 is 1 month and 29 days
2019-06-10 to 2019-04-19 is 1 month and 21 days
2019-06-10 to 2019-06-01 is 9 days
2019-06-10 to 2019-06-09 is 1 day
2019-06-10 to 2019-06-10 is no time
2019-06-10 to 2019-06-11 is 1 day
2019-06-10 to 2019-06-19 is 9 days
2019-06-10 to 2019-08-01 is 1 month and 21 days
2019-06-10 to 2019-08-09 is 1 month and 29 days
2019-06-10 to 2019-08-10 is 2 months
2019-06-10 to 2019-08-11 is 2 months and 1 day *** Alert ***
2019-06-10 to 2019-08-19 is 2 months and 9 days *** Alert ***
(Of course it's also possible to do this in SQL.) | [reply] [d/l] [select] |
|
I did read some parts about month maths, however my 2 month test only needs to be approximate, so I didn't dig deeply into that issue.
And I'll certainly look at using the accessors instead of direct key names.
Or just use SQL as per the above.
Regardless, your reply is appreciated. Thanks for the tips.
| [reply] |
Re: Months between dates ?
by hippo (Bishop) on Jun 10, 2019 at 10:13 UTC
|
While I agree with shadowsong that this logic would be better applied in the database, here is an SSCCE using only core modules and thus avoiding the heavy DateTime (which IMHO makes it "a better way" as you requested but that's a subjective call):
use strict;
use warnings;
use Time::Piece;
use Test::More tests => 6;
my $FMT = '%Y-%m-%d'; # ISO dates
my @less = ('2018-03-01', '2019-07-20', '2019-08-07');
my @more = ('2020-03-01', '2019-09-20', '2019-08-20');
for (@less) {
ok within_2_months ($_), "$_ is not more than 2 months in the futu
+re";
}
for (@more) {
ok !within_2_months ($_), "$_ is more than 2 months in the future"
+;
}
sub within_2_months {
my $now = localtime (time);
BAIL_OUT "Ach, you're too late. Should have run this earlier"
if $now > Time::Piece->strptime ('2019-06-20', $FMT);
my $then = Time::Piece->strptime (shift, $FMT);
return $then < $now->add_months(2);
}
| [reply] [d/l] |
Re: Months between dates ?
by thanos1983 (Parson) on Jun 10, 2019 at 11:00 UTC
|
#!/usr/bin/perl
use strict;
use warnings;
use Date::Manip;
use feature 'say';
my $date1 = ParseDate("today");
my $date2 = ParseDate("2019-08-10");
my $delta = DateCalc($date2, $date1, \my $err, 1);
say Delta_Format($delta, "Months: %Mv Days: %dv");
__END__
$ perl test.pl
Months: -2 Days: 0
You can create your own conditions based on the string when you want to have an alarm etc.
Hope this helps, BR.
Seeking for Perl wisdom...on the process of learning...not there...yet!
| [reply] [d/l] [select] |
|
|