search

terraref / reference-data

Coordination of Data Products and Standards for TERRA reference data
https://terraref.org
BSD 3-Clause "New" or "Revised" License
9 stars 2 forks source link

Change maria's bulk uploaded data from UTC to Arizona/Phoenix time zone #274

Open dlebauer opened 5 years ago

dlebauer commented 5 years ago

Migrating https://github.com/pecanProject/bety/issues/670 to here

It looks like all of these can be corrected by shifting the time zone from UTC to America/Pheonix

This applies to all data w/ Newcomb as citation and uploaded before June 18 2018 where the time is 05:00:00 UTC (i.e. no time stamp). Season 4 data for sure, perhaps also seasons 1 and 2.

gsrohde commented 5 years ago

@dlebauer I'm not sure what you mean by "shifting the time zone from UTC to America/Phoenix". The date column of the traits table has type timestamp(6) without time zone, but the convention is that this is always the time of the trait measurement in UTC time, or, in the case where dateloc is 5 ("day"), it will be midnight site time translated into UTC time. For the Arizona sites, which if I understand correctly, are always 7 hours behind UTC time, the timestamps for traits with dateloc 5 should always end in "07:00:00".

I imagine the SQL statement to fix this would be something like

update traits set date = date + interval '2 hours' where date::text ~ '05:00:00$' and dateloc = 5 and (select time_zone from sites s where s.id = site_id) = 'America/Phoenix';

There could possibly be some more conjuncts in the WHERE clause if you want to be more cautious or conservative.