Multi-Time Zone Installations

For Evergreen consortia that span more than one time zone, the following query will adjust all historical, unaged circulations so that if their due date field is pushed to the end of the day, it is done in the circulating library’s time zone, and not the server time zone.

It is safe to run this after any change to library time zones.

Running this is not required, as no code before this change has depended on the time string of 23:59:59. It is also not necessary if all of your libraries are in the same time zone, and that time zone is the same as the database’s configured time zone.

DO $$
    new_tz  text;
    ou_id   int;
    for ou_id in select id from actor.org_unit loop
        for new_tz in select oils_json_to_text(value) from actor.org_unit_ancestor_setting('lib.timezone',ou_id) loop
            if new_tz is not null then
                update  action.circulation
                  set   due_date = (due_date::timestamp || ' ' || new_tz)::timestamptz
                  where circ_lib = ou_id
                        and substring((due_date at time zone new_tz)::time::text from 1 for 8) <> '23:59:59';
            end if;
        end loop;
    end loop;