Skip to content
This repository has been archived by the owner on Jan 15, 2019. 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
Closed

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

icinga-migration opened this issue Dec 16, 2011 · 12 comments
Labels
bug IDOUtils
Milestone
1.7

Comments

@icinga-migration
Copy link

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
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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

  • Target Version changed from 1.6.2 to 1.7

@icinga-migration
Copy link
Author

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

any feedback on that?

@icinga-migration
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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
Copy link
Author

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.
Assignees
No one assigned
Labels
bug IDOUtils
Projects
None yet
Milestone
1.7
Development

No branches or pull requests
1 participant
@icinga-migration