Bitcoin Forum
December 17, 2017, 10:14:46 PM *
News: Latest stable version of Bitcoin Core: 0.15.1  [Torrent].
 
   Home   Help Search Donate Login Register  
Pages: « 1 [2]  All
  Print  
Author Topic: [BEGINNER WORKSHOP]: bitcoincharts + postgres = cool sql queries  (Read 5581 times)
molecular
Donator
Legendary
*
Offline Offline

Activity: 2436



View Profile
February 24, 2013, 03:58:25 PM
 #21

Molecular what are the programs you used for trading? Also give me your donation address please?

used? you mean traidor.py?

1JANa7gQ2VE7Wkv3o4917ECy8NWYtUx5F5

PGP key molecular F9B70769 fingerprint 9CDD C0D3 20F8 279F 6BE0  3F39 FC49 2362 F9B7 0769
1513548886
Hero Member
*
Offline Offline

Posts: 1513548886

View Profile Personal Message (Offline)

Ignore
1513548886
Reply with quote  #2

1513548886
Report to moderator
1513548886
Hero Member
*
Offline Offline

Posts: 1513548886

View Profile Personal Message (Offline)

Ignore
1513548886
Reply with quote  #2

1513548886
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
piramida
Legendary
*
Offline Offline

Activity: 1120



View Profile
February 24, 2013, 04:30:40 PM
 #22


I usually only ever do this like once a month.

If someone makes a script or something, please share. I'd do it for money because my own need for it is low Wink

The bitcoinchart.com api takes a start-time (unix time), that should be passed as "select max(unixtime) from trades;". I'm not sure how the \copy command behaves (wether or not it overwrites data or how that can be configured)



Ok so thats easy then, as "copy from" would append data to the table without touching existing rows.

i am satoshi
sgbett
Legendary
*
Offline Offline

Activity: 1526



View Profile
February 24, 2013, 11:01:49 PM
 #23

EDIT4: anyone knowledgable with postgres have a suggestion what datatype to best use for the monetary values?

NUMERIC(16,8) should do it



http://haschinabannedbitcoin.com
"A purely peer-to-peer version of electronic cash would allow online payments to be sent directly from one party to another without going through a financial institution" - Satoshi Nakamoto
molecular
Donator
Legendary
*
Offline Offline

Activity: 2436



View Profile
February 25, 2013, 06:44:11 AM
 #24

EDIT4: anyone knowledgable with postgres have a suggestion what datatype to best use for the monetary values?

NUMERIC(16,8) should do it




In case postgres uses that datatype for aggregate functions, 8 places before the decimal point will be enough.

PGP key molecular F9B70769 fingerprint 9CDD C0D3 20F8 279F 6BE0  3F39 FC49 2362 F9B7 0769
sgbett
Legendary
*
Offline Offline

Activity: 1526



View Profile
February 25, 2013, 12:02:06 PM
 #25

the params in that datatype represent scale and precision, scale is the total number of digits (ie including decimals) precision is the number of decimals

with the current bitcoin spec 16,8 is enough to store the largest theoretical possible transaction amount of 21million, and the smallest value of one satoshi.

The datatype scale and precision only affect the stored value, aggregate functions operate correctly disregarding scale and precision of the datatype (within the limits of the architecture).

eg the max value that can be stored in the field is 99999999.99999999,if you sum this with 0.00000001 then the result is 100000000 (ie no overflow occurs)

http://haschinabannedbitcoin.com
"A purely peer-to-peer version of electronic cash would allow online payments to be sent directly from one party to another without going through a financial institution" - Satoshi Nakamoto
molecular
Donator
Legendary
*
Offline Offline

Activity: 2436



View Profile
February 25, 2013, 09:45:33 PM
 #26

the params in that datatype represent scale and precision, scale is the total number of digits (ie including decimals) precision is the number of decimals

with the current bitcoin spec 16,8 is enough to store the largest theoretical possible transaction amount of 21million, and the smallest value of one satoshi.

The datatype scale and precision only affect the stored value, aggregate functions operate correctly disregarding scale and precision of the datatype (within the limits of the architecture).

eg the max value that can be stored in the field is 99999999.99999999,if you sum this with 0.00000001 then the result is 100000000 (ie no overflow occurs)

Good to know. Thanks. Changed the op to use 16,8

PGP key molecular F9B70769 fingerprint 9CDD C0D3 20F8 279F 6BE0  3F39 FC49 2362 F9B7 0769
oakpacific
Hero Member
*****
Offline Offline

Activity: 798


View Profile
March 07, 2013, 06:59:13 AM
 #27

Wonder if you would be interested in estimating early adopters' reserves again? I have not finished downloading the blockchain yet. Sad

https://tlsnotary.org/ Fraud proofing decentralized fiat-Bitcoin trading.
molecular
Donator
Legendary
*
Offline Offline

Activity: 2436



View Profile
March 07, 2013, 07:33:21 AM
 #28

Wonder if you would be interested in estimating early adopters' reserves again? I have not finished downloading the blockchain yet. Sad

Yeah, I'm generally interested. Just need a couple of quiet hours and the muse to kiss me. Been thinking about this.

this http://statistics.ecdsa.org/ would tell us the info if it was up-to-date. Maybe we should pester ThomasV (?).

PGP key molecular F9B70769 fingerprint 9CDD C0D3 20F8 279F 6BE0  3F39 FC49 2362 F9B7 0769
oakpacific
Hero Member
*****
Offline Offline

Activity: 798


View Profile
March 07, 2013, 07:48:50 AM
 #29

Wonder if you would be interested in estimating early adopters' reserves again? I have not finished downloading the blockchain yet. Sad

Yeah, I'm generally interested. Just need a couple of quiet hours and the muse to kiss me. Been thinking about this.

this http://statistics.ecdsa.org/ would tell us the info if it was up-to-date. Maybe we should pester ThomasV (?).


Yeah, I have been using this, it's great but a bit outdated.

https://tlsnotary.org/ Fraud proofing decentralized fiat-Bitcoin trading.
molecular
Donator
Legendary
*
Offline Offline

Activity: 2436



View Profile
May 09, 2013, 06:13:16 PM
 #30

EDIT: ignore this post, see next post

tcatm has limited the trade api. max 20000 trades can be downloaded per request.

here's a bash script that updates local trades table

Code:
#!/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

it's quite fresh (use accordingly), currently in process of syncing my table. It uses 4-hour blocks.

dont know if it quits nicely yet when done Wink

hoping someone can use it.

EDIT: added sync-end-detection. will sleep 10 minutes, then continue to sync.

EDIT2:

hmm, it stops prematurely. I think something is weird with the bitcoincharts api:

neither this: http://bitcoincharts.com/t/trades.csv?symbol=mtgoxUSD&start=1365709116&end=1365710916
nor this: http://bitcoincharts.com/t/trades.csv?symbol=mtgoxUSD&end=1365710916

deliver any data (except one trade in the first case) while I think they should.

while this: http://bitcoincharts.com/t/trades.csv?symbol=mtgoxUSD&start=1365709116

delivers the most recent trade data (20000 most recent trades) ignoring "start" (at least that is consistent with the api docs).

the api docs: http://bitcoincharts.com/about/markets-api/ say I should only use the "end" parameter. That doesn't work, though (http://bitcoincharts.com/t/trades.csv?symbol=mtgoxUSD&end=1365710916 doesn't deliver)

ideas?

PGP key molecular F9B70769 fingerprint 9CDD C0D3 20F8 279F 6BE0  3F39 FC49 2362 F9B7 0769
molecular
Donator
Legendary
*
Offline Offline

Activity: 2436



View Profile
November 02, 2013, 08:02:30 AM
 #31

api url has change to http://api.bitcoincharts.com/v1/csv/mtgoxUSD.csv, fixed op, ignore previous post.

PGP key molecular F9B70769 fingerprint 9CDD C0D3 20F8 279F 6BE0  3F39 FC49 2362 F9B7 0769
molecular
Donator
Legendary
*
Offline Offline

Activity: 2436



View Profile
November 02, 2013, 08:05:09 AM
 #32

Wonder if you would be interested in estimating early adopters' reserves again? I have not finished downloading the blockchain yet. Sad

this thread could be interesting for you: https://bitcointalk.org/index.php?topic=316297.0

PGP key molecular F9B70769 fingerprint 9CDD C0D3 20F8 279F 6BE0  3F39 FC49 2362 F9B7 0769
oakpacific
Hero Member
*****
Offline Offline

Activity: 798


View Profile
November 03, 2013, 02:19:51 PM
 #33

Wonder if you would be interested in estimating early adopters' reserves again? I have not finished downloading the blockchain yet. Sad

this thread could be interesting for you: https://bitcointalk.org/index.php?topic=316297.0

Yeah, that's cool, thanks.

https://tlsnotary.org/ Fraud proofing decentralized fiat-Bitcoin trading.
Pages: « 1 [2]  All
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!