3 SELECT evergreen.upgrade_deps_block_check('1054', :eg_version);
5 INSERT into config.org_unit_setting_type
6 ( name, grp, label, description, datatype ) VALUES
8 ( 'lib.timezone', 'lib',
9 oils_i18n_gettext('lib.timezone',
12 oils_i18n_gettext('lib.timezone',
13 'Define the time zone in which a library physically resides',
14 'coust', 'description'),
17 ALTER TABLE actor.org_unit_closed ADD COLUMN full_day BOOLEAN DEFAULT FALSE;
18 ALTER TABLE actor.org_unit_closed ADD COLUMN multi_day BOOLEAN DEFAULT FALSE;
20 UPDATE actor.org_unit_closed SET multi_day = TRUE
21 WHERE close_start::DATE <> close_end::DATE;
23 UPDATE actor.org_unit_closed SET full_day = TRUE
24 WHERE close_start::DATE = close_end::DATE
25 AND SUBSTRING(close_start::time::text FROM 1 FOR 8) = '00:00:00'
26 AND SUBSTRING(close_end::time::text FROM 1 FOR 8) = '23:59:59';
28 CREATE OR REPLACE FUNCTION action.push_circ_due_time () RETURNS TRIGGER AS $$
30 proper_tz TEXT := COALESCE(
33 FROM actor.org_unit_ancestor_setting('lib.timezone',NEW.circ_lib)
36 CURRENT_SETTING('timezone')
40 IF (EXTRACT(EPOCH FROM NEW.duration)::INT % EXTRACT(EPOCH FROM '1 day'::INTERVAL)::INT) = 0 -- day-granular duration
41 AND SUBSTRING((NEW.due_date AT TIME ZONE proper_tz)::TIME::TEXT FROM 1 FOR 8) <> '23:59:59' THEN -- has not yet been pushed
42 NEW.due_date = ((NEW.due_date AT TIME ZONE proper_tz)::DATE + '1 day'::INTERVAL - '1 second'::INTERVAL) || ' ' || proper_tz;
51 \qecho The following query will adjust all historical, unaged circulations so
52 \qecho that if their due date field is pushed to the end of the day, it is done
53 \qecho in the circulating library''''s time zone, and not the server time zone.
55 \qecho It is safe to run this after any change to library time zones.
57 \qecho Running this is not required, as no code before this change has
58 \qecho depended on the time string of '''23:59:59'''. It is also not necessary
59 \qecho if all of your libraries are in the same time zone, and that time zone
60 \qecho is the same as the database''''s configured time zone.
64 \qecho ' new_tz text;'
67 \qecho ' for ou_id in select id from actor.org_unit loop'
68 \qecho ' for new_tz in select oils_json_to_text(value) from actor.org_unit_ancestor_setting('''lib.timezone''',ou_id) loop'
69 \qecho ' if new_tz is not null then'
70 \qecho ' update action.circulation'
71 \qecho ' set due_date = (due_date::timestamp || ''' ''' || new_tz)::timestamptz'
72 \qecho ' where circ_lib = ou_id'
73 \qecho ' and substring((due_date at time zone new_tz)::time::text from 1 for 8) <> '''23:59:59''';'