Work items as defined in the investigation from T314871:
1 - Add the new columns to save the local time, timezone of the local time, and its UTC conversion, and change the code as needed to display the right information for the user.
Acceptance criteria
- Create the new columns on campaign_events table:
- event_start_local
- event_start_utc
- event_end_local
- event_end_utc
- event_timezone
- Change the code as needed to save the data on the new columns
- Change the code as needed to display the right information for the user
- Update documentation of the endpoints:
- Add the timezone to the response of "get details of an event"
- Note that start and end time are local in "get details of an event" and the 2 endpoints for enabling/editing an event
Deployment plan
- Write the patch that changes the schema in the code. Review it, but DO NOT MERGE IT. Only give it a +1, explicitly writing in a comment that the +1 means you are approving the change (and not that you have a working mouse).
- Create the new columns on beta with default values (see below)
- Merge the patch and wait for it to reach beta
- Remove default values on beta
- Update the beta DB with the actual values for the new columns (can be copied from the old columns)
ALTERs to run on beta
Before merging the patch:
-- Add new columns with temporary defaults used until the patch is merged ALTER TABLE campaign_events ADD COLUMN event_timezone VARBINARY(64) NOT NULL DEFAULT 'UTC' AFTER event_status; ALTER TABLE campaign_events ADD COLUMN event_start_local BINARY(14) NOT NULL DEFAULT '20220815100000' AFTER event_timezone; ALTER TABLE campaign_events ADD COLUMN event_start_utc BINARY(14) NOT NULL DEFAULT '20220815110000' AFTER event_start_local; ALTER TABLE campaign_events ADD COLUMN event_end_local BINARY(14) NOT NULL DEFAULT '20220815120000' AFTER event_start_utc; ALTER TABLE campaign_events ADD COLUMN event_end_utc BINARY(14) NOT NULL DEFAULT '20220815130000' AFTER event_end_local; -- Add defaults to the existing columns for later; use a unique value that identifies new rows ALTER TABLE campaign_events ALTER COLUMN event_start SET DEFAULT '00000000000000'; ALTER TABLE campaign_events ALTER COLUMN event_end SET DEFAULT '00000000000000'; CREATE INDEX event_timezone_id ON /*_*/campaign_events (event_timezone, event_id);
(Note: once the patch reaches beta, event times will be wrong until the queries below are executed)
After merging the patch:
-- Copy data for the old rows to the new columns UPDATE campaign_events SET event_start_utc=event_start, event_start_local=event_start, event_end_utc=event_end, event_end_local=event_end WHERE event_start != '00000000000000'; -- Drop old columns ALTER TABLE campaign_events DROP COLUMN event_start; ALTER TABLE campaign_events DROP COLUMN event_end; -- Drop defaults from the new columns ALTER TABLE campaign_events ALTER event_timezone DROP DEFAULT; ALTER TABLE campaign_events ALTER event_start_local DROP DEFAULT; ALTER TABLE campaign_events ALTER event_start_utc DROP DEFAULT; ALTER TABLE campaign_events ALTER event_end_local DROP DEFAULT; ALTER TABLE campaign_events ALTER event_end_utc DROP DEFAULT;
NOTE: Since we're not doing this in a backwards-compatible way, you will have to alter the tables manually on your local once the patch is merged
