user:kluong:migrating_time_received_to_utc [Smart Campus Energy Lab Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Migrating time_received to UTC ======

===== Background =====

Reference from chat:

<code>
yeah - the prod gateway is running a pretty old version, so i found some difference between the prod and staging environments
the staging gateway writes timestamps in UTC time, while the prod writes in whatever timezone is local to the computer
so we'll have to figure out a way to migrate the existing data to convert all of the timestamps to UTC
i can write up something for that tho
we can handle it later, shouldn't stop you guys from wrapping up the other staging stuff
this is the commit that changes the behavior: https://github.com/scel-hawaii/control-tower/commit/e79c8efa76a08a6a21a44d7c215817f6ce7c5d49
</code>

In the production database we need to modify the "time_received" column for multiple table. 

If we had migrations for the service setup, it would be a bit easier to make this modification, but running manal commands shouldn't be too bad. Something like this would work:

<code>
ALTER TABLE apple ALTER COLUMN time_received TYPE timestamp WITH TIME ZONE;
</code>

Here's what it would look like in the postgres terminal, making sure to check the table before and after:

<code>
postgres-# \d apple;
                               Table "public.apple"
       Column       |            Type             | Collation | Nullable | Default
--------------------+-----------------------------+-----------+----------+---------
 time_received      | timestamp without time zone |           | not null |
 schema             | integer                     |           |          |
 node_addr          | integer                     |           |          |
 uptime_ms          | bigint                      |           |          |
 batt_mv            | integer                     |           |          |
 panel_mv           | integer                     |           |          |
 press_pa           | integer                     |           |          |
 temp_c             | integer                     |           |          |
 humidity_centi_pct | integer                     |           |          |
 apogee_w_m2        | double precision            |           |          |

postgres=# ALTER TABLE apple ALTER COLUMN time_received TYPE timestamp WITH TIME ZONE;
ALTER TABLE
postgres=# \d apple;
                              Table "public.apple"
       Column       |           Type           | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
 time_received      | timestamp with time zone |           | not null |
 schema             | integer                  |           |          |
 node_addr          | integer                  |           |          |
 uptime_ms          | bigint                   |           |          |
 batt_mv            | integer                  |           |          |
 panel_mv           | integer                  |           |          |
 press_pa           | integer                  |           |          |
 temp_c             | integer                  |           |          |
 humidity_centi_pct | integer                  |           |          |
 apogee_w_m2        | double precision         |           |          |

</code>


It would need to be repeated for all other tables. Looking at the tables that should be available:

<code>
postgres=# \d+
                           List of relations
 Schema |      Name       | Type  |  Owner   |    Size    | Description
--------+-----------------+-------+----------+------------+-------------
 public | apple           | table | postgres | 8192 bytes |
 public | cranberry       | table | postgres | 8192 bytes |
 public | dragonfruit     | table | postgres | 8192 bytes |
 public | heartbeat       | table | postgres | 8192 bytes |
</code>

We'll need to repeat this process for the apple, cranberry and dragonfruit tables.
 

===== Operational notes =====

  - The production gateway should be stopped first
  - Then, the table should be fixed with the query above
  - Run some queries to check the data is what you expect it
  - Then, the production gateway should be updated so that it writes using UTC (pull from master)
  - Then, the production gateway should be started again


It might also be good to get into the routine of running a backup of the database prior to doing large commands: this might be a good way to do this: https://www.tecmint.com/backup-and-restore-postgresql-database/