Nahrazení bigint timestampů
Jako vývojář chci nahradit bigint
timestamp sloupce ve všech tabulkách za timestamptz
, jelikož práce s biginty v JS je problematická
Domluveno na Node.js chapteru
Akceptační kritéria
-
Nový datový typ sloupců je
timestamptz
. Týká se následujících tabulek -
air_quality_stations - airqualitystations_indexes
- measured_from
- measured_to
- airqualitystations_measurements
- measured_from
- measured_to
- airqualitystations_indexes
-
bicycle_counters - bicyclecounters_detections
- measured_from
- measured_to
- bicyclecounters_temperatures
- measured_from
- measured_to
- bicyclecounters_detections
-
energetics - vpalac_measurement
- time_measurement
- vpalac_measurement
-
flow - counters_detections
- measured_from
- measured_to
- flow_measurements
- end_timestamp
- start_timestamp
- flow_od_measurements
- end_timestamp
- start_timestamp
- flow_sinks
- history_start_timestamp
- counters_detections
-
municipal_authorities (+ history) - skodapalace_queues
- last_updated
- skodapalace_queues
-
parkings parkings#31 (closed) - parkings
- date_modified
- parkings_measurements_actual
- date_modified
- parkings_measurements_part
- date_modified
- parkings
-
pid - vlastní issue pid - nahrazení bigint timestampů (pid#263 - closed)
-
waze_ccp - wazeccp_alerts
- downloaded_at
- pub_millis
- valid_from
- wazeccp_irregularities
- detection_date_millis
- downloaded_at
- update_date_millis
- wazeccp_jams
- downloaded_at
- pub_millis
- wazeccp_alerts
-
wazett - wazett_jams_stats
- update_time
- wazett_route_lives
- update_time
- wazett_routes
- last_update
- wazett_subroute_lives
- update_time
- wazett_jams_stats
Implementační poznámky
- Akceptuje
@seniorvyvojar
- U většiny datových sad, je potřeba součinost datařů v úpravě transformací, aby se nerozbily dashboardy a vizualizace.
- Helper SQL k vypsání tabulek a sloupců
select
table_schema,
table_name,
array_agg(column_name order by column_name)
from
information_schema.columns
where
table_schema not in ('meta', 'pg_catalog', 'python', 'tmp', 'analytic', 'uzis', 'keboola', 'praha_dopravni', 'praha_dopravni_old')
and table_name not like '%backup%'
and data_type = 'bigint'
and column_name not in ('create_batch_id', 'update_batch_id', 'id')
group by
table_schema,
table_name
order by
table_schema,
table_name;
Edited by Tomáš Benák