Change start/end times to complete timestamps.

The way we had this until now with a date and separate start and end
times does not really work. There are cases when a screening starts on
one day, but ends on the next. By keeping a complete timestamp for both
the start and end times, we don't fall into this problem.

2 files changed, 17 insertions, 0 deletions

diff --git a/migrations/2020-08-23-140315_fix_screening_timestamps/down.sql b/migrations/2020-08-23-140315_fix_screening_timestamps/down.sql
new file mode 100644
index 0000000..49ceb2c
--- /dev/null
+++ b/migrations/2020-08-23-140315_fix_screening_timestamps/down.sql
@@ -0,0 +1,7 @@
+-- Revert date and timestamps back to separate state.
+-- There should be no reason to do this, but included for completeness-
+-- This will re-add the 'date' column, but not set the proper date.
+ALTER TABLE screenings
+    ADD COLUMN date TYPE DATE NOT NULL,
+    ALTER COLUMN start_time TYPE TIME,
+    ALTER COLUMN end_time TYPE TIME;
diff --git a/migrations/2020-08-23-140315_fix_screening_timestamps/up.sql b/migrations/2020-08-23-140315_fix_screening_timestamps/up.sql
new file mode 100644
index 0000000..fd08e80
--- /dev/null
+++ b/migrations/2020-08-23-140315_fix_screening_timestamps/up.sql
@@ -0,0 +1,10 @@
+-- Change the type of start and end times to include data and timezone.
+-- Drop the now obsolete date table
+ALTER TABLE screenings
+    ALTER COLUMN start_time TYPE timestamp with time zone
+    USING date::TIMESTAMP + start_time,
+
+    ALTER COLUMN end_time TYPE timestamp with time zone
+    USING date::TIMESTAMP + end_time,
+
+    DROP COLUMN date;