this thread could be interesting for you: https://bitcointalksearch.org/topic/distribution-of-bitcoin-wealth-by-owner-316297
Yeah, that's cool, thanks.
It was the Bitcointalk forum that inspired us to create Bitcointalksearch.org - Bitcointalk is an excellent site that should be the default page for anybody dealing in cryptocurrency, since it is a virtual gold-mine of data. However, our experience and user feedback led us create our site; Bitcointalk's search is slow, and difficult to get the results you need, because you need to log in first to find anything useful - furthermore, there are rate limiters for their search functionality.
The aim of our project is to create a faster website that yields more results and faster without having to create an account and eliminate the need to log in - your personal data, therefore, will never be in jeopardy since we are not asking for any of your data and you don't need to provide them to use our site with all of its capabilities.
We created this website with the sole purpose of users being able to search quickly and efficiently in the field of cryptocurrency so they will have access to the latest and most accurate information and thereby assisting the crypto-community at large.
#!/bin/bash
db="mtgox"
user="postgres"
symbol="mtgoxUSD"
PSQL="psql -q -t -U ${user} ${db}"
DL="curl -s"
function sql() {
rc=$(echo "$1" | $PSQL)
}
function extend() {
sql "drop table if exists import;"
sql "create table import (id serial, unixtime int, price numeric(32,10), volume numeric(32,8), type smallint);"
len=$(( 4 * 60 * 60 ))
while true; do
sql "select max(unixtime) from trades;"
start=$rc
start_human=$(date -d "@$start")
echo "--- $start_human ----------------------------------------------------------"
end=$(( $start + $len ))
echo "start ($start) + len ($len) = end ($end)"
# download
$DL "http://bitcoincharts.com/t/trades.csv?symbol=$symbol&start=$start&end=$end" > trades.csv
end_file=$(head -n 1 trades.csv | cut -d , -f 1)
start_file=$(tail -n 1 trades.csv | cut -d , -f 1)
echo "start_file ($start_file) - start ($start) = $(( $start_file - $start ))"
echo "end_file ($end_file) - end ($end) = $(( $end_file - $end ))"
if [ $start_file -eq $start ]; then
echo start times match, updating trades table
# put into import table
sql "delete from import;"
sql "\copy import(unixtime,price,volume) from 'trades.csv' delimiters ',' csv;"
sql "delete from trades where unixtime >= $start;"
sql "insert into trades (unixtime, t, price, volume) select unixtime, TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second', price, volume from import order by id desc;"
if [ $start_file -eq $end_file ]; then
echo "end detected, sleeping for 10 minutes,...."
sleep 10m;
fi
else
echo "start_file != start, exiting, check code"
exit 1
fi
done
}
extend
mtgox=# drop table trades;
DROP TABLE
mtgox=# create table trades (id serial, unixtime int, t timestamp, price numeric(32,10), volume numeric(32,8));
NOTICE: CREATE TABLE will create implicit sequence "trades_id_seq" for serial column "trades.id"
CREATE TABLE
mtgox=# \copy trades(unixtime,price,volume) from 'trades.csv' delimiters ',' csv;
mtgox=# update trades set t = TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second';
UPDATE 3563178
mtgox=# drop table trades;
DROP TABLE
mtgox=# create table trades (id serial, unixtime int, t timestamp, price numeric(32,10), volume numeric(32,8));
NOTICE: CREATE TABLE will create implicit sequence "trades_id_seq" for serial column "trades.id"
CREATE TABLE
mtgox=# \copy trades(unixtime,price,volume) from 'trades.csv' delimiters ',' csv;
mtgox=# update trades set t = TIMESTAMP 'epoch' + unixtime * INTERVAL '1 second';
UPDATE 3563178