Change historical_data.date to timestamptz data type

What does this MR do?

1. Add a new column recorded_at of type timestamp with timezone to historical_data table as a replacement for the historical_data.date column which has type date.

   The HistoricalDataWorker cronjob runs once a day at 12:00, and adds a single row to the historical_data table. The sidekiq-cron gem evaluates crons in the Rails timezone (Time.zone).

   The created_at column is the best indicator of when a row was created. So, populate recorded_at with the value of the created_at column. Since the created_at column does not have a not null constraint, we also need to fallback to the date column if created_at is null.

   Set a not null constraint on recorded_at.

   Remove the not null constraint on date since we won't be populating this column anymore. The column will be kept for now in case we need to go back and check old values. The column can be removed after a sufficient amount of time has passed with no issues.

2. Modify the HistoricalData model and specs to use recorded_at instead of date.

3. Update all callers of HistoricalData methods to pass in Time parameters rather than Date.

   Previously, with HistoricalData.date as a date datatype, when doing date range calculations such as HistoricalData.where(date: Date.parse('2020-10-20')..Date.parse('2020-10-23')), any records containing 20th or 23rd are included in the result.

   However, since recorded_at is a timestamp, HistoricalData.where(recorded_at: Date.parse('2020-10-20')..Date.parse('2020-10-23')) would result in 20th being included (since the time component is assumed to be 00:00), but 23rd would be excluded.

   In order to include both days, we need to use .beginning_of_day and .end_of_day making the above example HistoricalData.where(recorded_at: Date.parse('2020-10-20').beginning_of_day..Date.parse('2020-10-23').end_of_day). Both methods are time zone aware and will use the Time.zone if available.

Migration output

== 20201022094845 AddHistoricalDataRecordedAt: migrating ======================
-- add_column(:historical_data, :recorded_at, :timestamptz)
   -> 0.0009s
== 20201022094845 AddHistoricalDataRecordedAt: migrated (0.0010s) =============


== 20201022094846 UpdateHistoricalDataRecordedAt: migrating ===================
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"historical_data\" WHERE \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0007s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1")
   -> 0.0005s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 1 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0005s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 1 AND \"historical_data\".\"id\" < 3 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0021s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 3 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0005s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 3 AND \"historical_data\".\"id\" < 5 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0017s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 5 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0005s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 5 AND \"historical_data\".\"id\" < 7 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0016s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 7 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0006s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 7 AND \"historical_data\".\"id\" < 9 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0018s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 9 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0006s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 9 AND \"historical_data\".\"id\" < 11 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0017s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 11 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0006s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 11 AND \"historical_data\".\"id\" < 13 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0017s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 13 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0007s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 13 AND \"historical_data\".\"id\" < 15 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0022s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 15 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0009s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 15 AND \"historical_data\".\"id\" < 17 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0023s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 17 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0010s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 17 AND \"historical_data\".\"id\" < 19 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0018s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 19 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0007s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 19 AND \"historical_data\".\"id\" < 21 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0018s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 21 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0007s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 21 AND \"historical_data\".\"id\" < 23 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0018s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 23 AND \"historical_data\".\"recorded_at\" IS NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0007s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = COALESCE(created_at, date + '12:00'::time AT TIME ZONE 'Etc/UTC') WHERE \"historical_data\".\"id\" >= 23 AND \"historical_data\".\"recorded_at\" IS NULL")
   -> 0.0024s
-- current_schema()
   -> 0.0014s
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0010s
-- execute("ALTER TABLE historical_data\nADD CONSTRAINT check_640e8cf66c\nCHECK ( recorded_at IS NOT NULL )\nNOT VALID;\n")
   -> 0.0017s
-- current_schema()
   -> 0.0005s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- execute("ALTER TABLE historical_data VALIDATE CONSTRAINT check_640e8cf66c;")
   -> 0.0024s
-- execute("RESET ALL")
   -> 0.0007s
-- change_column_null(:historical_data, :date, true)
   -> 0.0024s
== 20201022094846 UpdateHistoricalDataRecordedAt: migrated (0.0938s) ==========

Revert migration output:

== 20201022094846 UpdateHistoricalDataRecordedAt: reverting ===================
-- change_column_null(:historical_data, :date, false)
   -> 0.0028s
-- execute("ALTER TABLE historical_data\nDROP CONSTRAINT IF EXISTS check_640e8cf66c\n")
   -> 0.0007s
-- transaction_open?()
   -> 0.0000s
-- exec_query("SELECT COUNT(*) AS count FROM \"historical_data\" WHERE \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0005s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1")
   -> 0.0004s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 1 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0004s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 1 AND \"historical_data\".\"id\" < 3 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0025s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 3 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0004s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 3 AND \"historical_data\".\"id\" < 5 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0017s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 5 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0005s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 5 AND \"historical_data\".\"id\" < 7 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0017s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 7 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0006s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 7 AND \"historical_data\".\"id\" < 9 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0019s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 9 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0006s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 9 AND \"historical_data\".\"id\" < 11 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0017s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 11 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0008s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 11 AND \"historical_data\".\"id\" < 13 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0031s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 13 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0017s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 13 AND \"historical_data\".\"id\" < 15 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0017s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 15 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0007s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 15 AND \"historical_data\".\"id\" < 17 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0018s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 17 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0006s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 17 AND \"historical_data\".\"id\" < 19 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0018s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 19 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0007s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 19 AND \"historical_data\".\"id\" < 21 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0018s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 21 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0007s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 21 AND \"historical_data\".\"id\" < 23 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0021s
-- exec_query("SELECT \"historical_data\".\"id\" FROM \"historical_data\" WHERE \"historical_data\".\"id\" >= 23 AND \"historical_data\".\"recorded_at\" IS NOT NULL ORDER BY \"historical_data\".\"id\" ASC LIMIT 1 OFFSET 2")
   -> 0.0006s
-- execute("UPDATE \"historical_data\" SET \"recorded_at\" = NULL WHERE \"historical_data\".\"id\" >= 23 AND \"historical_data\".\"recorded_at\" IS NOT NULL")
   -> 0.0019s
== 20201022094846 UpdateHistoricalDataRecordedAt: reverted (0.0481s) ==========


== 20201022094845 AddHistoricalDataRecordedAt: reverting ======================
-- remove_column(:historical_data, :recorded_at)
   -> 0.0012s
== 20201022094845 AddHistoricalDataRecordedAt: reverted (0.0013s) =============

Related to #271240 (closed)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers
• Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team