This is an old revision of the document!


Migrating time_received to UTC

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

You can use something like this to change all rows in a table:

UPDATE totals 
   SET total = total + 1
WHERE name = 'bill';

What we want to do is convert the original time_received timestamp to UTC as we update it. We can use something like this:

at time zone 'Pacific/Honolulu' at time zone 'utc'

So, combined:

UPDATE apple 
   SET time_received = time_received at time zone 'Pacific/Honolulu' at time zone 'utc'

Operational notes

  1. The production gateway should be stopped first
  2. Then, the table should be fixed with the query above
  3. Run some queries to check the data is what you expect it
  4. Then, the production gateway should be updated so that it writes using UTC (pull from master)
  5. Then, the production gateway should be started again
  • user/kluong/migrating_time_received_to_utc.1636684641.txt.gz
  • Last modified: 2021/11/12 02:37
  • by kluong