[dev.icinga.com #2106] change timestamp to use time zone by default in postgresql

[icinga-migration]

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

Created by jmosshammer on 2011-11-22 10:38:42 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2011-11-23 14:12:12 +00:00)
Target Version: 1.6
Last Update: 2014-12-08 14:37:28 +00:00 (in Redmine)

---

The pgsql timestamp fields don't respect timezones (without being explicitly asked at querying) if they aren't flagged as 'with time zone'

See:
http://www.postgresql.org/docs/8.0/static/datatype-datetime.html

Fix is simple:
For example
...
CREATE TABLE icinga_acknowledgements (
acknowledgement_id bigserial,
instance_id bigint default 0,
entry_time timestamp default '1970-01-01 00:00:00',
....

should be
CREATE TABLE icinga_acknowledgements (
acknowledgement_id bigserial,
instance_id bigint default 0,
entry_time timestamp with time zone default '1970-01-01 00:00:00',

I tested this on my development machine and it works, timestamps are then returned in the current timezone with the timezone offset appended.

Regards,
Jannis

Also see: #1954

Changesets

2011-11-22 20:47:36 +00:00 by mfriedrich cc0270a

idoutils: change timestamp to use time zone by default in postgresql (thx Florian Helmberger) #2106

changing in pgsql.sql is trivial, the upgrade
scripts require some specialities - thanks to
Florian Helmberger on providing those.

refs #2106

---

Relations:

• relates #2106
• relates #1954
• relates #2203

[icinga-migration]

Updated by mfriedrich on 2011-11-22 13:57:24 +00:00

• Target Version set to 1.6

is this true only for the sla table or do we need to adapt this for all tables?

[icinga-migration]

Updated by jmosshammer on 2011-11-22 14:03:23 +00:00

Unfortunately it's required for all tables, otherwise #1954 occurs.

[icinga-migration]

Updated by mfriedrich on 2011-11-22 14:05:27 +00:00

can you please provide me some test queries, then i'll do the change myself while preparing the beta for tomorrow.

[icinga-migration]

Updated by mfriedrich on 2011-11-22 15:39:32 +00:00

changing this in the initial pgsql.sql is easy. but the upgrade on timestamps with timezones needs a using clause which mocks around on implicite type casts and the provided examples in the docs do not mention that.

http://www.postgresql.org/docs/8.4/interactive/sql-altertable.html

icinga=> alter table icinga_acknowledgements
icinga->         alter column entry_time drop default,
icinga->         alter column entry_time type timestamp with time zone
icinga->         using timestamp with time zone 'epoch' + entry_time * interval '1 second',
icinga->         alter column entry_time set default now();
ERROR:  operator does not exist: timestamp without time zone * interval
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

http://grokbase.com/t/postgresql.org/pgsql-bugs/2008/04/operator-does-not-exist-timestamp-w-out-timezone-similar-to-bug-3807/12ib3vkzs3k462nix56jpvc52vom

ideas?

[icinga-migration]

Updated by mfriedrich on 2011-11-22 19:23:47 +00:00

got a hint from florian helmberger, the docs example just takes an integer and not a timestamp type. in order to extract the epoch from entry_time, a special routine is needed.

icinga=> alter table icinga_acknowledgements
        alter column entry_time set data type timestamp with time zone
        using timestamp with time zone 'epoch' + extract(epoch from entry_time) * interval '1 second';

this needs to be applied for all current timestamp columns.

[icinga-migration]

Updated by mfriedrich on 2011-11-22 20:43:00 +00:00

• Tracker changed from Bug to Feature
• Subject changed from Pgsql timestamp fields have missing timezones to change timestamp to use time zone by default in postgresql
• Category set to 24
• Status changed from New to Assigned
• Assigned to set to mfriedrich

[icinga-migration]

Updated by mfriedrich on 2011-11-23 14:12:12 +00:00

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

now, all timestamps in postgresql respect the timezone by default and do not require special external treatment, from scratch import as well as in upgrade scripts.

[icinga-migration]

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

• Project changed from 18 to Core, Classic UI, IDOUtils
• Category changed from 24 to IDOUtils