Show last update

[dbrgn]

It would be nice if we could see the date and time of the last database update, to see how up to date the database is.

[das-g]

That would certainly be useful. A complication is that we use materialized views for some of the PotMs, which (currently) need to be updated separately from the changeset import, so not all parts of the database are currently always equally up-to-date. AFAIK we're working on automating this, so that the base tables and the materialized views will be more in sync.

@lbuchli Can you comment?

[lbuchli]

The materialized views currently get updated about once per day, whereas the data gets updated once per hour. In the future (hopefully) the materialized views will be refreshed automatically after every data sync. It should thus be enough for now and the future to just show when the materialized views were updated.

I did a test with just showing the time of the last change, although a real implementation would use an additional database table (which would result in an additional superset dataset) storing the time of the last refresh. The most difficult part of implementing this is probably to make it visually pleasing, as Superset does not have many options for showing just one text value. The best I've been able to come up with is:

which is just a superset table with only one entry.

[sfkeller]

Main goal is that a user of the Dashboard want's to know either changes of which datetime are reflected in the billboard and/or when the last update was of the data ("view").

There are other - more parsimonious - potential solutions around besides maintaining "last update" in a table, like

1. Retrieve max(datetime) from the materialized view of a PotM (or changeset table...).
2. Retrieve timestamp from this Node which has max. OSM_id, like this select osm_timestamp from osm_point where osm_id = (select max(osm_id) from osm_point);
3. At end of cron job script, touch a file to set the current datetime.
4. At end of cron job script, put datetime into table DESCRIPTION. See e.g. https://stackoverflow.com/questions/109325/postgresql-describe-table
5. Other options (while Trigger probaly is not a good idea because it slows down the processes): https://dba.stackexchange.com/questions/58214/getting-last-modification-date-of-a-postgresql-database-table

[datendelphin]

It is a single script currently that updates the materialized views after updating with the new changes. I can put a timestamp into the database when that script is run if that solution is chosen