OpenAQ Data Compare Queries

openaq_data_compare.sql

-- PostgreSQL
select 
  row_number() over (order by date_utc asc, location, parameter), 
  location, 
  value, 
  parameter, 
  country, 
  city, 
  date_utc 
from measurements 
where 
  date_utc between '2020-07-01T00:00:00Z' and '2020-07-07T00:00:00Z' 
  and location='Abbotsford' 
order by date_utc asc, location, parameter;


-- Athena
SELECT location,
         value,
         parameter,
         country,
         city,
         from_iso8601_timestamp(date.utc) as date_utc
FROM openaq
WHERE 
location = 'Abbotsford' AND
from_iso8601_timestamp(date.utc)
    BETWEEN from_iso8601_timestamp('2020-07-01T00:00:00Z')
        AND from_iso8601_timestamp('2020-07-07T00:00:00Z')
ORDER BY  from_iso8601_timestamp(date.utc) asc, lower(location), parameter
;