Skip to content
This repository has been archived by the owner. It is now read-only.

[dev.icinga.com #2203] pgsql unix_timestamp cannot handle timstamp with timezone #825

Closed
icinga-migration opened this issue Dec 16, 2011 · 12 comments
Labels
Milestone

Comments

@icinga-migration
Copy link
Member

@icinga-migration icinga-migration commented Dec 16, 2011

This issue has been migrated from Redmine: https://dev.icinga.com/issues/2203

Created by mfriedrich on 2011-12-16 08:43:57 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2012-04-27 17:13:13 +00:00)
Target Version: 1.7
Last Update: 2014-12-08 14:37:29 +00:00 (in Redmine)

Icinga Version: 1.10.0
OS Version: any

the function unix_timestamp is self written and does not respect the timezone, therefore creating an error.

-- 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;
' LANGUAGE 'SQL';

below is the look before, and after applying 1.6.0 upgrade, failing on the query.

icinga=# \d icinga_downtimehistory
                                                   Table "public.icinga_downtimehistory"
         Column         |            Type             |                                      Modifiers
------------------------+-----------------------------+-------------------------------------------------------------------------------------
 downtimehistory_id     | bigint                      | not null default nextval('icinga_downtimehistory_downtimehistory_id_seq'::regclass)
 instance_id            | bigint                      | default 0
 downtime_type          | integer                     | default 0
 object_id              | bigint                      | default 0
 entry_time             | timestamp without time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 author_name            | text                        | default ''::text
 comment_data           | text                        | default ''::text
 internal_downtime_id   | bigint                      | default 0
 triggered_by_id        | bigint                      | default 0
 is_fixed               | integer                     | default 0
 duration               | integer                     | default 0
 scheduled_start_time   | timestamp without time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 scheduled_end_time     | timestamp without time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 was_started            | integer                     | default 0
 actual_start_time      | timestamp without time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 actual_start_time_usec | integer                     | default 0
 actual_end_time        | timestamp without time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 actual_end_time_usec   | integer                     | default 0
 was_cancelled          | integer                     | default 0
Indexes:
    "pk_downtimehistory_id" PRIMARY KEY, btree (downtimehistory_id)
    "uq_downtimehistory" UNIQUE, btree (instance_id, object_id, entry_time, internal_downtime_id)

icinga=# alter table icinga_downtimehistory
icinga-#         alter column entry_time set data type timestamp with time zone
icinga-#                 using timestamp with time zone 'epoch' + extract(epoch from entry_time) * interval '1 second',
icinga-#         alter column scheduled_start_time set data type timestamp with time zone
icinga-#                 using timestamp with time zone 'epoch' + extract(epoch from scheduled_start_time) * interval '1 second',
icinga-#         alter column scheduled_end_time set data type timestamp with time zone
icinga-#                 using timestamp with time zone 'epoch' + extract(epoch from scheduled_end_time) * interval '1 second',
icinga-#         alter column actual_start_time set data type timestamp with time zone
icinga-#                 using timestamp with time zone 'epoch' + extract(epoch from actual_start_time) * interval '1 second',
icinga-#         alter column actual_end_time set data type timestamp with time zone
icinga-#                 using timestamp with time zone 'epoch' + extract(epoch from actual_end_time) * interval '1 second';
ALTER TABLE
icinga=# \d icinga_downtimehistory
                                                  Table "public.icinga_downtimehistory"
         Column         |           Type           |                                      Modifiers
------------------------+--------------------------+-------------------------------------------------------------------------------------
 downtimehistory_id     | bigint                   | not null default nextval('icinga_downtimehistory_downtimehistory_id_seq'::regclass)
 instance_id            | bigint                   | default 0
 downtime_type          | integer                  | default 0
 object_id              | bigint                   | default 0
 entry_time             | timestamp with time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 author_name            | text                     | default ''::text
 comment_data           | text                     | default ''::text
 internal_downtime_id   | bigint                   | default 0
 triggered_by_id        | bigint                   | default 0
 is_fixed               | integer                  | default 0
 duration               | integer                  | default 0
 scheduled_start_time   | timestamp with time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 scheduled_end_time     | timestamp with time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 was_started            | integer                  | default 0
 actual_start_time      | timestamp with time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 actual_start_time_usec | integer                  | default 0
 actual_end_time        | timestamp with time zone | default '1970-01-01 00:00:00'::timestamp without time zone
 actual_end_time_usec   | integer                  | default 0
 was_cancelled          | integer                  | default 0
Indexes:
    "pk_downtimehistory_id" PRIMARY KEY, btree (downtimehistory_id)
    "uq_downtimehistory" UNIQUE, btree (instance_id, object_id, entry_time, internal_downtime_id)

icinga=# SELECT downtimehistory_id, UNIX_TIMESTAMP(actual_start_time) AS actual_start_time, UNIX_TIMESTAMP(actual_end_time) AS actual_end_time, UNIX_TIMESTAMP(scheduled_start_time) AS scheduled_start_time, UNIX_TIMESTAMP(scheduled_end_time) AS scheduled_end_time, is_fixed, duration FROM icinga_downtimehistory WHERE instance_id = '1' AND object_id = '1819' AND ((actual_start_time > FROM_UNIXTIME(1323934831) AND actual_start_time < FROM_UNIXTIME(1324018263)) OR  (actual_end_time > FROM_UNIXTIME(1323934831) AND actual_end_time < FROM_UNIXTIME(1324018263)) OR  (actual_start_time < FROM_UNIXTIME(1323934831) AND actual_end_time > FROM_UNIXTIME(1324018263)) OR  (actual_end_time IS NULL));
ERROR:  function unix_timestamp(timestamp with time zone) does not exist
LINE 1: SELECT downtimehistory_id, UNIX_TIMESTAMP(actual_start_time)...
                                   ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Changesets

2012-04-19 14:05:30 +00:00 by mfriedrich d179dea

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

Relations:

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Dec 16, 2011

Updated by mfriedrich on 2011-12-16 09:29:00 +00:00

corrected function.

icinga=# CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS bigint AS '
icinga'#         SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;
icinga'# ' LANGUAGE 'SQL';
CREATE FUNCTION

working query, respecting timezones

icinga=# SELECT downtimehistory_id, UNIX_TIMESTAMP(actual_start_time) AS actual_start_time, UNIX_TIMESTAMP(actual_end_time) AS actual_end_time, UNIX_TIMESTAMP(scheduled_start_time) AS scheduled_start_time, UNIX_TIMESTAMP(scheduled_end_time) AS scheduled_end_time, is_fixed, duration FROM icinga_downtimehistory WHERE instance_id = '1' AND object_id = '1819' AND ((actual_start_time > FROM_UNIXTIME(1323934831) AND actual_start_time < FROM_UNIXTIME(1324018263)) OR  (actual_end_time > FROM_UNIXTIME(1323934831) AND actual_end_time < FROM_UNIXTIME(1324018263)) OR  (actual_start_time < FROM_UNIXTIME(1323934831) AND actual_end_time > FROM_UNIXTIME(1324018263)) OR  (actual_end_time IS NULL));
 downtimehistory_id | actual_start_time | actual_end_time | scheduled_start_time | scheduled_end_time | is_fixed | duration 
--------------------+-------------------+-----------------+----------------------+--------------------+----------+----------
(0 rows)

some tests.

icinga=# select unix_timestamp('2011-12-16 10:00:00');
 unix_timestamp 
----------------
     1324026000
(1 row)

icinga=# SELECT CURRENT_TIME;
       timetz       
--------------------
 10:00:24.178212+01
(1 row)

icinga=# select from_unixtime(1324026000);
    from_unixtime    
---------------------
 2011-12-16 10:00:00
(1 row)

icinga=# select unix_timestamp('2011-12-16 10:00:00+1');
 unix_timestamp 
----------------
     1324026000
(1 row)

icinga=# select unix_timestamp('2011-12-16 10:00:00+2');
 unix_timestamp 
----------------
     1324022400
(1 row)

icinga=# select unix_timestamp('2011-12-16 10:00:00+0');
 unix_timestamp 
----------------
     1324029600
(1 row)

icinga=# select unix_timestamp('2011-12-16 10:00:00');
 unix_timestamp 
----------------
     1324026000
(1 row)

icinga=# select from_unixtime(1324029600);
    from_unixtime    
---------------------
 2011-12-16 11:00:00
(1 row)

icinga=# select from_unixtime(1324022400);
    from_unixtime    
---------------------
 2011-12-16 09:00:00
(1 row)
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Dec 16, 2011

Updated by mfriedrich on 2011-12-16 11:24:28 +00:00

  • Status changed from New to Feedback
  • Priority changed from Urgent to Low
  • Target Version changed from 1.6.2 to 1.7

ok, this is happening with enable_sla=1 in ido2db.cfg

$ grep -r UNIX_TIMESTAMP module/idoutils/*
Übereinstimmungen in Binärdatei module/idoutils/src/db.o.
Übereinstimmungen in Binärdatei module/idoutils/src/ido2db.
module/idoutils/src/db.c:               if (asprintf(&buf, "UNIX_TIMESTAMP(%s)", (field == NULL) ? "" : field) == -1)
module/idoutils/src/db.c:               if (asprintf(&buf, "UNIX_TIMESTAMP(%s)", (field == NULL) ? "" : field) == -1)

$ grep -r ido2db_db_sql_to_timet module/idoutils/*
module/idoutils/include/db.h:char *ido2db_db_sql_to_timet(ido2db_idi *,char *);
Übereinstimmungen in Binärdatei module/idoutils/src/sla.o.
Übereinstimmungen in Binärdatei module/idoutils/src/db.o.
module/idoutils/src/sla.c:      start_time_sql = ido2db_db_sql_to_timet(idi,
module/idoutils/src/sla.c:      end_time_sql = ido2db_db_sql_to_timet(idi,
module/idoutils/src/sla.c:      ack_time_sql = ido2db_db_sql_to_timet(idi,
module/idoutils/src/sla.c:      scheduled_start_sql = ido2db_db_sql_to_timet(idi,
module/idoutils/src/sla.c:      scheduled_end_sql = ido2db_db_sql_to_timet(idi,
module/idoutils/src/sla.c:      actual_start_sql = ido2db_db_sql_to_timet(idi,
module/idoutils/src/sla.c:      actual_end_sql = ido2db_db_sql_to_timet(idi,
Übereinstimmungen in Binärdatei module/idoutils/src/ido2db.
module/idoutils/src/db.c:char *ido2db_db_sql_to_timet(ido2db_idi *idi, char *field) {
module/idoutils/src/db.c:       ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "ido2db_db_sql_to_timet(%s) start\n", field);
module/idoutils/src/db.c:       ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "ido2db_db_sql_to_timet(%s) end\n", buf);

fixed function

CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS bigint AS '
        SELECT EXTRACT(EPOCH FROM $1)::bigint AS result;
' LANGUAGE 'SQL';
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Dec 16, 2011

Updated by mfriedrich on 2011-12-16 11:43:35 +00:00

  • Priority changed from Low to Normal
  • Target Version changed from 1.7 to 1.6.2

this affects from_unixtime too.

http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html

by default, to_timestamp will return timestamp with time zone, but as a matter of fact we cast that to ::timezone removing the time zone awareness of this function.

CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp AS '
         SELECT to_timestamp($1)::timestamp AS result
' LANGUAGE 'SQL';

so timestamps passed as utc will not be saved correctly within their given timezone, but default anyways.

since from_unixtime is being used within the most queries, the function must remain. changing a return type will require the function to be dropped and then created again instead if replacing it.

old behaviour.

icinga=# select from_unixtime(1324026000);
    from_unixtime    
---------------------
 2011-12-16 10:00:00
(1 row)

icinga=# SET SESSION TIMEZONE='UTC';
SET
icinga=# select from_unixtime(1324026000);
    from_unixtime    
---------------------
 2011-12-16 09:00:00
(1 row)

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';

in current +1 timezone.

icinga=# select unix_timestamp('2011-12-16 10:00:00');
 unix_timestamp 
----------------
     1324026000
(1 row)

icinga=# select from_unixtime(1324026000);
     from_unixtime      
------------------------
 2011-12-16 10:00:00+01
(1 row)

get onto utc

icinga=# SET SESSION TIMEZONE='UTC';
SET
icinga=# select from_unixtime(1324026000);
     from_unixtime      
------------------------
 2011-12-16 09:00:00+00
(1 row)

so currently timestamps are not timezone aware as they should be. depends on general discussion how and when to fix this.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Dec 16, 2011

Updated by mfriedrich on 2011-12-16 12:59:18 +00:00

fixed version

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';
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Feb 23, 2012

Updated by mfriedrich on 2012-02-23 21:49:11 +00:00

  • Target Version changed from 1.6.2 to 1.7
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 3, 2012

Updated by mfriedrich on 2012-04-03 10:59:44 +00:00

any feedback on that?

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 19, 2012

Updated by mfriedrich on 2012-04-19 13:46:45 +00:00

good, no one gives a f*ck, like usual. thanks for no feedback, then i'll apply that.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 19, 2012

Updated by mfriedrich on 2012-04-19 13:58:48 +00:00

postgres@icinga-dev:~$ psql icinga
psql (9.1.3)
Type "help" for help.

icinga=# select unix_timestamp('2011-12-16 10:00:00');
 unix_timestamp 
----------------
     1324026000
(1 row)

icinga=# select from_unixtime(1324026000);
     from_unixtime      
------------------------
 2011-12-16 10:00:00+01
(1 row)


icinga=# SET SESSION TIMEZONE='UTC';
SET
icinga=# select from_unixtime(1324026000);
     from_unixtime      
------------------------
 2011-12-16 09:00:00+00
(1 row)
@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 26, 2012

Updated by Tommi on 2012-04-26 19:24:57 +00:00

I am sorry, but currently i have only mysql and oracle available. In general it should follow the logic we introduced for the tables timestamp values. There i did the same tests. When connecting set session timezone=UTC' will be executed. Therefore all incoming data should be in UTC, which is also expected from the other databases.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 26, 2012

Updated by mfriedrich on 2012-04-26 20:21:10 +00:00

well i know your rdbms, it was meant for everyone else. anyhow, my tests run fine, i still need to test this against icinga-web if the other related issues i had are gone. maybe tomorrow.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Apr 27, 2012

Updated by mfriedrich on 2012-04-27 17:13:13 +00:00

  • Status changed from Feedback to Resolved
  • Done % changed from 0 to 100

from the normal operations, everything works fine. icinga-web got other issues, which do not harm the resolval here.

@icinga-migration

This comment has been minimized.

Copy link
Member Author

@icinga-migration icinga-migration commented Dec 8, 2014

Updated by mfriedrich on 2014-12-08 14:37:29 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category set to IDOUtils
  • Icinga Version set to 1
  • OS Version set to any
@icinga-migration icinga-migration added this to the 1.7 milestone Jan 17, 2017
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
1 participant
You can’t perform that action at this time.