bcgov
DATAFIX expiration_date column - REQUESTS Table - NAMEX DB
- We need to update the time portion of the expiration date from all the NR's that were created before this fix went in.
https://app.zenhub.com/workspaces/entities-team-space-6143567664fb320019b81f39/issues/bcgov/entity/10280
Developer should write SQL to update the expiration_date column from the REQUESTS Table in NAMEX DB.
- update the database where time is 00:00:00 to 06:59:00 IN DEV verify
- Apply the same SQL in TEST - VERIFY
- Run Query in PROD and Verify with Staff
executed the following pl/sql block in DEV and TEST environment:
do $$
declare
v_rec record;
v_expiration_date TIMESTAMPtz;
curs cursor for
select id,
nr_num,
expiration_date,
extract(year from expiration_date)::int yy,
extract(month from expiration_date)::int mm,
extract(day from expiration_date)::int dd
from requests
where extract(hour from expiration_date) = 0
and extract(minute from expiration_date) = 0
and extract(minute from expiration_date) = 0
and extract(year from expiration_date) >= 2022;
begin
open curs;
loop
fetch from curs into v_rec;
EXIT WHEN not found;
update requests
set expiration_date = (make_timestamptz(year=>v_rec.yy , month=>v_rec.mm, mday=>v_rec.dd, hour=>23, min=>59, sec=>0, timezone=>'Canada/Pacific')
- INTERVAL '1 day')
where id=v_rec.id;
select (expiration_date) into v_expiration_date from requests where id=v_rec.id;
RAISE NOTICE '%, %, %, %', v_rec.id,v_rec.nr_num,v_rec.expiration_date,v_expiration_date;
end loop;
commit;
end $$;
Executed the pl/sql block in DEV and TEST. Sample results in TEST:
ID NR_NUM BEFORE AFTER
NOTICE: 8952, NR 8506245, 2022-06-08 00:00:00+00, 2022-06-08 06:59:00+00
NOTICE: 8964, NR 5751226, 2022-06-15 00:00:00+00, 2022-06-15 06:59:00+00
NOTICE: 8979, NR 6606913, 2024-06-16 00:00:00+00, 2024-06-16 06:59:00+00
NOTICE: 8992, NR 8523975, 2022-06-21 00:00:00+00, 2022-06-21 06:59:00+00
NOTICE: 8993, NR 2916299, 2022-06-21 00:00:00+00, 2022-06-21 06:59:00+00
NOTICE: 8580, NR 2351944, 2022-02-05 00:00:00+00, 2022-02-05 07:59:00+00
NOTICE: 8589, NR 3762816, 2022-02-08 00:00:00+00, 2022-02-08 07:59:00+00
In TEST environment
Before:
After:
Data: NOTICE: 8964, NR 5751226, 2022-06-15 00:00:00+00, 2022-06-15 06:59:00+00
@lmcclung We need to know the expiration date window we are going to fix, thanks
@lmcclung @Mihai-QuickSilverDev Please check above Eve's question and let us know your feedback, thanks
Make it visible on entities board, and assign to David and Mihai for entities' input
@davemck513 @Mihai-QuickSilverDev Please check above Eve's question and let us know your feedback, thanks cc: @jdyck-fw
@davemck513 @jdyck-fw This is stuck in Waiting on the SRE board and Review on the Entities board. Would it be possible to address Eve's questions such that we night move this along please?
@MeganFedora does it mean that there is no need to apply the script to production?
@eve-git can you please call me next week to discuss? I need further details before I can advise.
NR 5655852 is still shows incorrect expiry time. The transaction history below shows different time
Hi Jennifer, Megan, Jeff,
I counted the NRs with APPROVED or CONDITIONAL state and the expiration_date is on today or after: There are 31 requests with expiration time at 00:01 (pacific time). Their request time is earlier than 2022-02-03 and the request type are Restorations There are 7343 requests with expiration time at 23:59 (pacific time).
It seems that the bug has been fixed for all new requests. For Restorations (RCR and RSO), I am not sure if it is special rules apply to them.
@eve-git from Jenn: I can confirm that all NR’s should have the same expiry time of 11:59pm. All NR’s have a 56 day expiry date, with the exception of a reinstatement or restoration for any type, which has the standard 56 days, plus a year.
Can we please get Jenn added to this board?
@jennweldon @MeganFedora Thanks for answering my question. Please let me know if anything I can be help.