API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 29th, 2017
73
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.17 KB | None | 0 0
raw download clone embed print report
  1. set @k_period = 1440;
  2.  
  3. SELECT
  4. FROM_UNIXTIME(
  5. FLOOR(
  6. UNIX_TIMESTAMP(MIN(`date`)) /(@k_period * 60)
  7. ) *(@k_period * 60)
  8. ) AS date ,
  9. SUBSTRING_INDEX(MIN(CONCAT(`date` , '_' , `open`)) ,'_' ,- 1) AS `open` ,
  10. MAX(high) AS high ,
  11. MAX(low) AS low ,
  12. SUBSTRING_INDEX(MAX(CONCAT(`date` , '_' , `close`)) ,'_' ,- 1) AS `close` ,
  13. SUM(volumn) AS volumn
  14. FROM
  15. bitfinex_btc_usd_60
  16.  
  17. GROUP BY
  18. FLOOR(
  19. UNIX_TIMESTAMP(`date`) /(@k_period * 60)
  20. )
  21. ORDER BY
  22. date;
  23.  
  24. date open high low close volumn
  25. 2017-02-15 08:00:00 1011.20000000 1018.20000000 1014.40000000 1013.90000000 1657.56771832
  26. 2017-02-16 08:00:00 1013.90000000 1045.80000000 1040.00000000 1038.50000000 11260.04584229
  27. 2017-02-17 08:00:00 1037.20000000 1064.30000000 1056.50000000 1060.10000000 10452.95630785
  28.  
  29. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 01:00:00', '1011.20000000', '1011.90000000', '1010.50000000', '1011.90000000', '114.40467058');
  30. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 02:00:00', '1011.60000000', '1011.80000000', '1010.10000000', '1011.70000000', '169.30957431');
  31. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 03:00:00', '1011.40000000', '1018.20000000', '1011.10000000', '1016.50000000', '895.14868914');
  32. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 04:00:00', '1016.50000000', '1016.90000000', '1014.40000000', '1015.00000000', '127.47284789');
  33. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 05:00:00', '1015.30000000', '1015.30000000', '1014.00000000', '1014.50000000', '59.37837700');
  34. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 06:00:00', '1015.00000000', '1015.40000000', '1014.00000000', '1015.30000000', '85.64851448');
  35. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 07:00:00', '1015.20000000', '1015.50000000', '1013.20000000', '1013.90000000', '206.20504492');
  36. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 08:00:00', '1013.90000000', '1014.30000000', '1013.90000000', '1014.30000000', '84.32824424');
  37. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 09:00:00', '1014.30000000', '1016.00000000', '1014.00000000', '1015.90000000', '171.64293214');
  38. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 10:00:00', '1016.00000000', '1030.80000000', '1015.60000000', '1030.20000000', '1879.94581574');
  39. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 11:00:00', '1030.20000000', '1030.20000000', '1023.30000000', '1023.40000000', '525.06307721');
  40. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 12:00:00', '1023.40000000', '1026.00000000', '1023.40000000', '1025.90000000', '78.60095712');
  41. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 13:00:00', '1025.80000000', '1026.80000000', '1023.70000000', '1026.20000000', '137.69076229');
  42. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 14:00:00', '1026.40000000', '1028.00000000', '1024.00000000', '1028.00000000', '255.33095664');
  43. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 15:00:00', '1028.10000000', '1029.00000000', '1022.80000000', '1025.60000000', '335.15465613');
  44. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 16:00:00', '1025.70000000', '1027.90000000', '1024.60000000', '1027.50000000', '257.43484974');
  45. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 17:00:00', '1027.60000000', '1028.90000000', '1027.50000000', '1027.80000000', '173.76322030');
  46. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 18:00:00', '1027.90000000', '1028.00000000', '1025.00000000', '1025.00000000', '181.29768235');
  47. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 19:00:00', '1025.00000000', '1027.70000000', '1023.70000000', '1027.70000000', '311.46359336');
  48. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 20:00:00', '1027.70000000', '1028.10000000', '1026.20000000', '1027.80000000', '269.76641027');
  49. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 21:00:00', '1028.20000000', '1029.70000000', '1027.00000000', '1027.40000000', '278.12470794');
  50. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 22:00:00', '1027.50000000', '1029.70000000', '1026.00000000', '1029.70000000', '348.46915707');
  51. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-16 23:00:00', '1029.70000000', '1037.70000000', '1029.70000000', '1034.50000000', '1268.69486501');
  52. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 00:00:00', '1034.60000000', '1040.90000000', '1034.50000000', '1039.00000000', '558.99734392');
  53. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 01:00:00', '1038.60000000', '1042.10000000', '1030.20000000', '1037.30000000', '1158.77443316');
  54. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 02:00:00', '1037.30000000', '1045.80000000', '1037.10000000', '1042.10000000', '1375.74324704');
  55. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 03:00:00', '1042.30000000', '1043.00000000', '1040.00000000', '1040.10000000', '102.14137652');
  56. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 04:00:00', '1040.40000000', '1042.00000000', '1040.00000000', '1040.50000000', '443.63211330');
  57. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 05:00:00', '1040.50000000', '1041.00000000', '1031.00000000', '1040.10000000', '749.39771610');
  58. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 06:00:00', '1039.00000000', '1040.00000000', '1035.00000000', '1035.50000000', '148.06910762');
  59. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 07:00:00', '1035.70000000', '1039.10000000', '1035.10000000', '1038.50000000', '166.51861708');
  60. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 08:00:00', '1037.20000000', '1044.90000000', '1037.20000000', '1044.90000000', '363.96726264');
  61. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 09:00:00', '1044.90000000', '1046.00000000', '1040.00000000', '1045.00000000', '239.84863223');
  62. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 10:00:00', '1044.70000000', '1044.70000000', '1034.40000000', '1035.70000000', '455.74968678');
  63. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 11:00:00', '1036.00000000', '1040.00000000', '1034.70000000', '1040.00000000', '208.67549451');
  64. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 12:00:00', '1039.90000000', '1040.60000000', '1036.60000000', '1040.50000000', '198.50991005');
  65. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 13:00:00', '1040.40000000', '1042.10000000', '1035.00000000', '1039.30000000', '323.39968433');
  66. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 14:00:00', '1039.40000000', '1040.00000000', '1033.10000000', '1037.50000000', '251.62652531');
  67. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 15:00:00', '1037.50000000', '1043.70000000', '1037.50000000', '1042.30000000', '401.88391714');
  68. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 16:00:00', '1042.40000000', '1044.00000000', '1037.20000000', '1043.40000000', '753.53400248');
  69. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 17:00:00', '1043.50000000', '1045.10000000', '1040.20000000', '1045.00000000', '469.82463351');
  70. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 18:00:00', '1045.00000000', '1049.00000000', '1041.90000000', '1041.90000000', '838.10164710');
  71. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 19:00:00', '1041.90000000', '1044.90000000', '1034.10000000', '1043.20000000', '615.77723295');
  72. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 20:00:00', '1043.20000000', '1045.00000000', '1040.90000000', '1044.80000000', '490.26122234');
  73. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 21:00:00', '1044.80000000', '1057.70000000', '1044.40000000', '1056.10000000', '2491.03266705');
  74. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 22:00:00', '1056.00000000', '1061.00000000', '1052.70000000', '1055.90000000', '1461.54759764');
  75. insert into `bitfinex_btc_usd_60` ( `date`, `open`, `high`, `low`, `close`, `volumn`) values ( '2017-02-17 23:00:00', '1057.10000000', '1064.30000000', '1056.50000000', '1060.10000000', '889.21619179');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!