From d9aee9d9366bdd037a09fd6a7c54b21d9749fd55 Mon Sep 17 00:00:00 2001
From: "lpsolit%gmail.com" <>
Date: Tue, 15 Nov 2005 17:52:55 +0000
Subject: Bug 301141: [PostgreSQL] New charts throw an error about
 FROM_UNIXTIME - Patch by Frédéric Buclin <LpSolit@gmail.com> r=mkanat
 a=justdave
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 Bugzilla/Chart.pm | 23 +++++++++++++++++------
 Bugzilla/DB/Pg.pm |  2 +-
 2 files changed, 18 insertions(+), 7 deletions(-)

diff --git a/Bugzilla/Chart.pm b/Bugzilla/Chart.pm
index 0cc41f9a4..f161d1555 100644
--- a/Bugzilla/Chart.pm
+++ b/Bugzilla/Chart.pm
@@ -33,6 +33,8 @@ package Bugzilla::Chart;
 use Bugzilla::Util;
 use Bugzilla::Series;
 
+use Date::Format;
+
 sub new {
     my $invocant = shift;
     my $class = ref($invocant) || $invocant;
@@ -233,14 +235,18 @@ sub readData {
         $dateto = $self->{'dateto'};
     }
 
+    # Convert UNIX times back to a date format usable for SQL queries.
+    my $sql_from = time2str('%Y-%m-%d', $datefrom);
+    my $sql_to = time2str('%Y-%m-%d', $dateto);
+
     # Prepare the query which retrieves the data for each series
-    my $query = "SELECT " . $dbh->sql_to_days('series_date') . " - " . 
-                            $dbh->sql_to_days("FROM_UNIXTIME($datefrom)") .
-                ", series_value FROM series_data " .
+    my $query = "SELECT " . $dbh->sql_to_days('series_date') . " - " .
+                            $dbh->sql_to_days('?') . ", series_value " .
+                "FROM series_data " .
                 "WHERE series_id = ? " .
-                "AND series_date >= FROM_UNIXTIME($datefrom)";
+                "AND series_date >= ?";
     if ($dateto) {
-        $query .= " AND series_date <= FROM_UNIXTIME($dateto)";
+        $query .= " AND series_date <= ?";
     }
     
     my $sth = $dbh->prepare($query);
@@ -256,7 +262,12 @@ sub readData {
         foreach my $series (@$line) {
 
             # Get the data for this series and add it on
-            $sth->execute($series->{'series_id'});
+            if ($dateto) {
+                $sth->execute($sql_from, $series->{'series_id'}, $sql_from, $sql_to);
+            }
+            else {
+                $sth->execute($sql_from, $series->{'series_id'}, $sql_from);
+            }
             my $points = $sth->fetchall_arrayref();
 
             foreach my $point (@$points) {
diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm
index f4569b9fd..4424e65fd 100644
--- a/Bugzilla/DB/Pg.pm
+++ b/Bugzilla/DB/Pg.pm
@@ -114,7 +114,7 @@ sub sql_limit {
 sub sql_to_days {
     my ($self, $date) = @_;
 
-    return "TO_CHAR($date, 'J')::int";
+    return "TO_CHAR(${date}::date, 'J')::int";
 }
 
 sub sql_date_format {
-- 
cgit v1.2.3-24-g4f1b