diff options
| -rw-r--r-- | Bugzilla/DB.pm | 7 | ||||
| -rw-r--r-- | Bugzilla/DB/Mysql.pm | 6 | ||||
| -rw-r--r-- | Bugzilla/DB/Pg.pm | 6 | ||||
| -rwxr-xr-x | collectstats.pl | 14 |
4 files changed, 26 insertions, 7 deletions
diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 09c941ec8..1da845942 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -1124,6 +1124,13 @@ formatted SQL command have prefix C<sql_>. All other methods have prefix C<bz_>. Returns: formatted SQL for limiting number of rows returned from query with optional offset (e.g. LIMIT 1, 1) (scalar) +=item C<sql_from_days> + + Description: Outputs SQL syntax for converting Julian days to date. + Abstract method, should be overriden by database specific code. + Params: $days = days to convert to date + Returns: formatted SQL for returning Julian days in dates. (scalar) + =item C<sql_to_days> Description: Outputs SQL syntax for converting date to Julian days. diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index 71bb97588..c006e5fd0 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -133,6 +133,12 @@ sub sql_istring { return $string; } +sub sql_from_days { + my ($self, $days) = @_; + + return "FROM_DAYS($days)"; +} + sub sql_to_days { my ($self, $date) = @_; diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm index 4424e65fd..a8f54f903 100644 --- a/Bugzilla/DB/Pg.pm +++ b/Bugzilla/DB/Pg.pm @@ -111,6 +111,12 @@ sub sql_limit { } } +sub sql_from_days { + my ($self, $days) = @_; + + return "TO_TIMESTAMP(${days}::int, 'J')::date"; +} + sub sql_to_days { my ($self, $date) = @_; diff --git a/collectstats.pl b/collectstats.pl index e087c28d0..2d7d3d258 100755 --- a/collectstats.pl +++ b/collectstats.pl @@ -286,7 +286,7 @@ sub regenerate_stats { $dbh->sql_to_days('current_date') . " AS end, " . $dbh->sql_to_days("'1970-01-01'") . " FROM bugs $from_product WHERE " . - $dbh->sql_to_days('creation_ts') . " != 'NULL'" . + $dbh->sql_to_days('creation_ts') . " IS NOT NULL " . $and_product . " ORDER BY start " . $dbh->sql_limit(1)); @@ -316,8 +316,8 @@ FIN # Get a list of bugs that were created the previous day, and # add those bugs to the list of bugs for this product. SendSQL("SELECT bug_id FROM bugs $from_product " . - "WHERE bugs.creation_ts < from_days(" . ($day - 1) . ") " . - "AND bugs.creation_ts >= from_days(" . ($day - 2) . ") " . + " WHERE bugs.creation_ts < " . $dbh->sql_from_days($day - 1) . + " AND bugs.creation_ts >= " . $dbh->sql_from_days($day - 2) . $and_product . " ORDER BY bug_id"); @@ -354,8 +354,8 @@ FIN " ON bugs_activity.fieldid = fielddefs.fieldid " . " WHERE fielddefs.name = 'bug_status' " . " AND bugs_activity.bug_id = $bug " . - " AND bugs_activity.bug_when >= from_days($day) " . - "ORDER BY bugs_activity.bug_when " . + " AND bugs_activity.bug_when >= " . $dbh->sql_from_days($day) . + " ORDER BY bugs_activity.bug_when " . $dbh->sql_limit(1)); my $status; @@ -377,8 +377,8 @@ FIN " ON bugs_activity.fieldid = fielddefs.fieldid " . " WHERE fielddefs.name = 'resolution' " . " AND bugs_activity.bug_id = $bug " . - " AND bugs_activity.bug_when >= from_days($day) " . - "ORDER BY bugs_activity.bug_when " . + " AND bugs_activity.bug_when >= " . $dbh->sql_from_days($day) . + " ORDER BY bugs_activity.bug_when " . $dbh->sql_limit(1)); if (@row = FetchSQLData()) { |