minder icon indicating copy to clipboard operation
minder copied to clipboard

Add time zone to all DB timestamps

Open blkt opened this issue 1 year ago • 0 comments

Most of the tables in Minder database have type timestamp, which expands to timestamp without time zone, thus leaving them without time zone information.

This is not a problem at the moment as most of the timestamps are not used in business logic, but letting the system determine the time zone often leads to errors.

Write a migration redefining all columns as timestamp with time zone.

For reference, here's the list of timestamps in Minder DB

minder=# select table_name, column_name, data_type from information_schema.columns where data_type like 'timestamp%' and table_schema = 'public';
            table_name             |    column_name     |          data_type
-----------------------------------+--------------------+-----------------------------
 users                             | created_at         | timestamp without time zone
 users                             | updated_at         | timestamp without time zone
 providers                         | created_at         | timestamp without time zone
 providers                         | updated_at         | timestamp without time zone
 provider_access_tokens            | expiration_time    | timestamp without time zone
 provider_access_tokens            | created_at         | timestamp without time zone
 provider_access_tokens            | updated_at         | timestamp without time zone
 repositories                      | created_at         | timestamp without time zone
 repositories                      | updated_at         | timestamp without time zone
 repositories                      | reminder_last_sent | timestamp without time zone
 session_store                     | created_at         | timestamp without time zone
 entity_profiles                   | created_at         | timestamp without time zone
 entity_profiles                   | updated_at         | timestamp without time zone
 profiles                          | created_at         | timestamp without time zone
 profiles                          | updated_at         | timestamp without time zone
 rule_type                         | created_at         | timestamp without time zone
 rule_type                         | updated_at         | timestamp without time zone
 artifacts                         | created_at         | timestamp without time zone
 artifacts                         | updated_at         | timestamp without time zone
 profile_status                    | last_updated       | timestamp without time zone
 projects                          | created_at         | timestamp without time zone
 projects                          | updated_at         | timestamp without time zone
 pull_requests                     | created_at         | timestamp without time zone
 pull_requests                     | updated_at         | timestamp without time zone
 features                          | created_at         | timestamp without time zone
 features                          | updated_at         | timestamp without time zone
 entitlements                      | created_at         | timestamp without time zone
 entity_execution_lock             | last_lock_time     | timestamp without time zone
 flush_cache                       | queued_at          | timestamp without time zone
 user_invites                      | created_at         | timestamp without time zone
 user_invites                      | updated_at         | timestamp without time zone
 profiles_with_entity_profiles     | created_at         | timestamp without time zone
 profiles_with_entity_profiles     | updated_at         | timestamp without time zone
 provider_github_app_installations | created_at         | timestamp without time zone
 provider_github_app_installations | updated_at         | timestamp without time zone
 rule_instances                    | created_at         | timestamp without time zone
 rule_instances                    | updated_at         | timestamp without time zone
 evaluation_statuses               | evaluation_time    | timestamp with time zone
 remediation_events                | created_at         | timestamp without time zone
 alert_events                      | created_at         | timestamp without time zone
 entity_instances                  | created_at         | timestamp with time zone
 properties                        | updated_at         | timestamp with time zone
(42 rows)

blkt avatar Nov 20 '24 12:11 blkt