search

in3rsha / bitcoin-utxo-dump

Get a list of UTXOs (unspent transaction outputs) from your Bitcoin Core client.
MIT License
241 stars 103 forks source link

Identify spent UTXO #6

Closed seccobit closed 4 years ago

seccobit commented 5 years ago

Hi,

is there a way to identify UTXO that have been spent, and total amount spent for each UTXO? Sorry, not really an issue, but couldn't find any other forum where I could raise this.

in3rsha commented 5 years ago

No problem!

The blockchain contains all the outputs, both the spent ones and the unspent ones (UTXOs). The UTXOs are most interesting in Bitcoin because they need to be accessed quickly when validating new transactions. This is why UTXOs are given their own special database outside the blockchain, which is what this tool accesses.

Therefore, if you want to access the spent outputs you just need to look inside the blockchain, which contains every transaction and the outputs created from them. The vast majority of outputs in the blockchain are going to be spent, as the UTXOs only make up a small percentage of the outputs that haven't been spent yet.

So to get the spent outputs (which there are going to be lots of, probably billions), you need to iterate through the data inside the blockchain. The best tool I've found for doing this is: bitcoin-iterate.

For example, this command will return every single output (spent and unspent) from your blockchain:

$ bitcoin-iterate --output=%th,%oN,%oa > outputs.txt

4a5e1e4baab89f3a32518a88c31bc87f618f76673e2cc77ab2127b7afdeda33b,0,5000000000
0e3e2357e806b6cdb1f70b54c3a3a17b6714ee1f0e68bebb44a74b1efd512098,0,5000000000
9b0fc92260312ce44e74ef369f5c66bbb85848f2eddd5a7a1cde251e54ccfdd5,0,5000000000
999e1c837c76a1b7fbb7e57baf87b309960f5ffefbf2a9b95dd890602272f644,0,5000000000
df2b060fa2e5e9c8ed5eaf6a45c13753ec8c63282b2688322eba40cd98ea067a,0,5000000000
...

Then you could use this tool to identify all the unspent outputs, and subtract them from the results above (based on the output's unique txid:vout) to get all the spent outputs only.

Hope that helps.

seccobit commented 5 years ago

Thanks! and thanks for bitcoin-utxo-dump (very useful). bitcoin-iterate should work, going to try now. I would need to include timestamp (creation) and timestamp (destruction) it seems as it can be added to the output (https://github.com/rustyrussell/bitcoin-iterate/blob/master/doc/bitcoin-iterate.1.txt ). Basically, I am trying to determine the avg. lifespan of a UTXO (spent on its entirety), the issue here is that each time a portion of UTXO is spent, new UTXOs are created (so single UTXO time span is not really a good metrics)... any suggestion is welcomed.

in3rsha commented 5 years ago

No problem.

Well, strictly speaking it's not possible to spend a portion of a UTXO. When you spend a UTXO in a transaction, you are always using it all up to create new outputs from it. Some of those could be used as change and sent back to the same address, but this doesn't always happen (change could be sent to a new address entirely), so it would be very difficult to try and determine if only a "portion" of a UTXO is spent in a transaction.

I would say that the destruction of a UTXO is when it is used as an input in a transaction (when it gets spent). Trying to work out the end time of a UTXO would be very difficult otherwise.

Just thought I'd share my thoughts on that one in case that helps.

seccobit commented 5 years ago

Hey in3rsha, thanks for the above, one more, Do you know by any chance why when I run the below:

$ ./bitcoin-iterate </home/mypath/blocks> -q --tx=%bh,%tl

or any other valid commands from bitcoin-iterate I always get this back:

"" Usage: ./bitcoin-iterate Valid block, transaction, input, output, and utxo format:

: unquoted %bl: block length %bv: block version %bp: block prev hash %bm: block merkle hash %bs: block timestamp %bt: block target %bn: block nonce ...... "" I must miss something. Maybe important to note that on this machine have not yet finished to download Bitcoin blockchain (currently on Block height=482717). I wouldn't expect this to matter but I thought to mention it anyway...just in case it does.
in3rsha commented 4 years ago

Closing this issue for now.