I have recently been asked about "old prices" that persist at a station for items that are no longer bought or sold at a station. The following is an explanation of the scenario and some advice on how to resolve it.
Firstly, the situation doesn't have to do with crowdsourced data - it can happen with just local data that is updated using the import command. Using the import command, a user adds data for a station - this might be from EliteOCR, ED:Market Connector or Maddavo Market Share data. Then at some later time data is updated for a station via the import command. But a commodity that was listed the first time is now missing. This means that prices get updated for all the commodities except the missing one. The price for the missing item remains in the local data with the original timestamp. This situation can lead to some undesirable trade routes being generated where you are told to deliver a load of something that you can no longer sell for a profit at the station or worse it may be illegal. Then you have to work out what to do with your cargo and generate a whole new trade run. But then the trade run generation may be tainted again causing you to try to buy/sell non-existent/unwanted items.
The TD import command used to delete this old price - essentially overwriting data for a station. But since a few versions ago the import command merges data and there is no option to clobber old data.
So, the price database needs to be 'cleaned' of old prices. On Maddavo Market Share, there is a routine that runs every 3 hours that deletes any price that is 2days older than the latest station price. This has the effect that prices for a station are all timestamped within a 2-day window. Ages ago this timeframe was 14days, then it was reduced to 7days and now it is 2days. This is because we initially were updating data completely manually and people didn't update all the prices for a station so a 14-day window was good. But now the vast majority of price updates are automated so even a 2-day window is probably stretching it. But I digress...
The price 'cleaning' on MMS just makes sure that anyone using that data isn't downloading old prices. BUT it doesn't do anything to the old prices in your local database - you still need to 'clean' your local data. There is no command that does this automatically. You can manually UPDATE prices for a station - I use this command (trade update "SYSTEM/Station" --sub -T) to open Sublime and look at the timestamp column to see if there are any prices that are old compared to the other timestamps. This is extremely tedious and has to be done station-by-station.
Sick of this, I created the following SQL query to 'clean' the local database. It will delete all prices that are 2 days older than the latest price at their respective stations. I use DBBrowser for SQLite to open the TradeDangerous.db file and run the query. The query is:
The "-2 day" at the end might be substituted with some other timeframe like "-6 hour".
I am hopeless at Python. Please can someone make a Python command that will do that OR better modify the import command to have the option of running that after an import (or having an option to clobber previous data for a station would achieve the same thing - actually better).
Cheers,
Maddavo
Firstly, the situation doesn't have to do with crowdsourced data - it can happen with just local data that is updated using the import command. Using the import command, a user adds data for a station - this might be from EliteOCR, ED:Market Connector or Maddavo Market Share data. Then at some later time data is updated for a station via the import command. But a commodity that was listed the first time is now missing. This means that prices get updated for all the commodities except the missing one. The price for the missing item remains in the local data with the original timestamp. This situation can lead to some undesirable trade routes being generated where you are told to deliver a load of something that you can no longer sell for a profit at the station or worse it may be illegal. Then you have to work out what to do with your cargo and generate a whole new trade run. But then the trade run generation may be tainted again causing you to try to buy/sell non-existent/unwanted items.
The TD import command used to delete this old price - essentially overwriting data for a station. But since a few versions ago the import command merges data and there is no option to clobber old data.
So, the price database needs to be 'cleaned' of old prices. On Maddavo Market Share, there is a routine that runs every 3 hours that deletes any price that is 2days older than the latest station price. This has the effect that prices for a station are all timestamped within a 2-day window. Ages ago this timeframe was 14days, then it was reduced to 7days and now it is 2days. This is because we initially were updating data completely manually and people didn't update all the prices for a station so a 14-day window was good. But now the vast majority of price updates are automated so even a 2-day window is probably stretching it. But I digress...
The price 'cleaning' on MMS just makes sure that anyone using that data isn't downloading old prices. BUT it doesn't do anything to the old prices in your local database - you still need to 'clean' your local data. There is no command that does this automatically. You can manually UPDATE prices for a station - I use this command (trade update "SYSTEM/Station" --sub -T) to open Sublime and look at the timestamp column to see if there are any prices that are old compared to the other timestamps. This is extremely tedious and has to be done station-by-station.
Sick of this, I created the following SQL query to 'clean' the local database. It will delete all prices that are 2 days older than the latest price at their respective stations. I use DBBrowser for SQLite to open the TradeDangerous.db file and run the query. The query is:
Code:
DELETE FROM StationItem WHERE StationItem.station_id || '/' || StationItem.item_id IN
(SELECT StationItem.station_id || '/' || StationItem.item_id AS Thing FROM StationItem INNER JOIN
(SELECT MAX(modified) AS Stationtimestamp, station_id FROM StationItem GROUP BY station_id) AS MyQ
ON MyQ.station_id = StationItem.station_id WHERE datetime(modified)<datetime(Stationtimestamp,'-2 day'))The "-2 day" at the end might be substituted with some other timeframe like "-6 hour".
I am hopeless at Python. Please can someone make a Python command that will do that OR better modify the import command to have the option of running that after an import (or having an option to clobber previous data for a station would achieve the same thing - actually better).
Cheers,
Maddavo
