[icinga-checkins] icinga.org: icinga-core/mfriedrich/ido: idoutils: fix pgsql unix_timestamp cannot handle timstamp with timezone #2203

git at icinga.org git at icinga.org
Thu Apr 19 16:12:02 CEST 2012


Module: icinga-core
Branch: mfriedrich/ido
Commit: d179dea0323132e83fe7db81576a759dee6a960e
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=d179dea0323132e83fe7db81576a759dee6a960e

Author: Michael Friedrich <michael.friedrich at univie.ac.at>
Date:   Thu Apr 19 16:05:30 2012 +0200

idoutils: fix pgsql unix_timestamp cannot handle timstamp with timezone #2203

as a matter of fact, the columns were changed to
understand timestamp with timezone, but not the
conversion functions, still ignoring the timezone.

this patch fixes that accordingly, replacing
from_unixtime and unix_timestamp with timezone
aware functionality.

this possibly affects the sla and tackle problems
with postgresql in icinga-web, as well as other
instance cronk time calculations too (#2293)

refs #2203

refs #2393

---

 Changelog                                          |    3 +++
 module/idoutils/db/pgsql/pgsql.sql                 |   11 +++++------
 .../db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql       |   13 +++++++++++++
 3 files changed, 21 insertions(+), 6 deletions(-)

diff --git a/Changelog b/Changelog
index 4c4abb1..a1b0eaf 100644
--- a/Changelog
+++ b/Changelog
@@ -78,6 +78,7 @@ FIXES
 * idoutils: fix sample shell db create script for mysql #2358
 * idoutils: fix OCI-21500 when freeing lobs #2509
 * idoutils: add note to "if process data is not dumped via idomod, ido2db won't clean tables and set objects inactive" #2237
+* idoutils: fix pgsql unix_timestamp cannot handle timstamp with timezone #2203
 
 * install: Fix notification template installation (Todd Zullinger) #2235
 
@@ -121,6 +122,8 @@ CHANGES
 
 * idoutils: Oracle only->change method retrieving lastid, introduce individual sequence caching sizes #2510 
 
+* idoutils: fix pgsql unix_timestamp cannot handle timstamp with timezone #2203
+
 
 1.6.1 - 02/12/2011
 
diff --git a/module/idoutils/db/pgsql/pgsql.sql b/module/idoutils/db/pgsql/pgsql.sql
index bd0e483..609ea0c 100644
--- a/module/idoutils/db/pgsql/pgsql.sql
+++ b/module/idoutils/db/pgsql/pgsql.sql
@@ -5,7 +5,7 @@
 -- Copyright (c) 2009-2012 Icinga Development Team (http://www.icinga.org)
 --
 -- initial version: 2009-05-13 Markus Manzke
--- current version: 2010-07-20 Michael Friedrich <michael.friedrich at univie.ac.at>
+-- current version: 2012-04-19 Michael Friedrich <michael.friedrich at univie.ac.at>
 --
 -- --------------------------------------------------------
 
@@ -13,13 +13,12 @@
 -- Functions
 --
 
-CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp AS '
-	 SELECT to_timestamp($1)::timestamp AS result
+CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp with time zone AS '
+         SELECT to_timestamp($1) AS result
 ' LANGUAGE 'SQL';
 
--- timestamp without time zone (i.e. 1973-11-29 21:33:09)
-CREATE OR REPLACE FUNCTION unix_timestamp(timestamp) RETURNS bigint AS '
-	SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;
+CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS bigint AS '
+        SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;
 ' LANGUAGE 'SQL';
 
 
diff --git a/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql
index 9c73090..a945257 100644
--- a/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql
+++ b/module/idoutils/db/pgsql/upgrade/pgsql-upgrade-1.7.0.sql
@@ -13,6 +13,19 @@
 create index statehist_state_idx on icinga_statehistory(object_id,state);
 
 -- -----------------------------------------
+--#2203 cannot handle timstamp with timezone
+-- -----------------------------------------
+
+DROP FUNCTION from_unixtime(integer);
+CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp with time zone AS '
+         SELECT to_timestamp($1) AS result
+' LANGUAGE 'SQL';
+
+CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS bigint AS '
+        SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;
+' LANGUAGE 'SQL';
+
+-- -----------------------------------------
 -- update dbversion
 -- -----------------------------------------
 





More information about the icinga-checkins mailing list