Created
April 8, 2018 14:22
-
-
Save haukex/a18a3021f139ce5317dc987e2c821fcb to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env perl | |
use warnings; | |
use strict; | |
use Time::HiRes qw/gettimeofday tv_interval/; | |
use DBI; | |
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:",undef,undef, | |
{ RaiseError=>1, AutoCommit=>0 } ); | |
{ # create table and fill with fake data | |
$dbh->do(<<'END_SQL'); | |
CREATE TABLE candles_USD_BCH ( | |
id INTEGER PRIMARY KEY, | |
start INTEGER | |
); | |
END_SQL | |
my $sth = $dbh->prepare(q{INSERT INTO candles_USD_BCH (start) VALUES (?)}); | |
while (<DATA>) { | |
chomp; | |
for my $i (1..100) { | |
$sth->execute($_ + 5700*$i); | |
} | |
} | |
$dbh->commit; | |
} | |
print $dbh->selectrow_array('SELECT COUNT(*) FROM candles_USD_BCH'), " rows in DB\n"; | |
if(1) { | |
my $t0 = [gettimeofday]; | |
# original code by F. Grela, fixed (https://stackoverflow.com/q/49717944/9300627) | |
use POSIX qw(strftime); | |
my $lock = 0; | |
my @empty; | |
my $dbtable = "USD_BCH"; | |
my $stmt = qq(SELECT start FROM candles_$dbtable ORDER BY start ASC LIMIT 1;); | |
my $sth = $dbh->prepare( $stmt ); | |
my $rv = $sth->execute() or die $DBI::errstr; | |
if($rv < 0) { | |
print $DBI::errstr; | |
} | |
my $start_all = $sth->fetchrow_array(); | |
$stmt = qq(SELECT start FROM candles_$dbtable ORDER BY start DESC LIMIT 1;); | |
$sth = $dbh->prepare( $stmt ); | |
$rv = $sth->execute() or die $DBI::errstr; | |
if($rv < 0) { | |
print $DBI::errstr; | |
} | |
my $end_all = $sth->fetchrow_array(); | |
for (my $i = $start_all; $i < $end_all; $i += 60) { | |
$stmt = qq(SELECT start FROM candles_$dbtable WHERE start LIKE $i ORDER BY start ASC LIMIT 1;); | |
$sth = $dbh->prepare( $stmt ); | |
$rv = $sth->execute() or die $DBI::errstr; | |
if($rv < 0) { | |
print $DBI::errstr; | |
} | |
my $row; | |
$row = $sth->fetchrow_array(); | |
if ($row) { | |
if ($lock == 1) { | |
my $firstempty = strftime "%Y-%m-%d %H:%M:%S", localtime $empty[0]; | |
my $lastempty = strftime "%Y-%m-%d %H:%M:%S", localtime pop @empty; | |
print "$firstempty - $lastempty\n"; | |
$lock = 0; | |
@empty = (); | |
} | |
#print "Pełne: $row\n"; | |
} | |
else { | |
$lock = 1; | |
push @empty, $i; | |
#print "Pueste: @empty\n"; | |
} | |
} | |
if ($lock == 1) { | |
my $firstempty = strftime "%Y-%m-%d %H:%M:%S", localtime $empty[0]; | |
my $lastempty = strftime "%Y-%m-%d %H:%M:%S", localtime pop @empty; | |
print "$firstempty - $lastempty\n"; | |
$lock = 0; | |
} | |
# end original code by F. Grela | |
print "Time: ",tv_interval($t0),"\n"; | |
} | |
if(1) { | |
my $t0 = [gettimeofday]; | |
# wolfrevokcats's solution (https://stackoverflow.com/a/49718580/9300627) | |
use POSIX qw(strftime); | |
my $dbtable = "USD_BCH"; | |
my $stmt = qq(SELECT start FROM candles_$dbtable ORDER BY start ASC;); | |
my $sth = $dbh->prepare( $stmt ); | |
my $rv = $sth->execute() or die $DBI::errstr; | |
my $fmt='%Y-%m-%d %H:%M:%S'; | |
my $expected; | |
while (my @row=$sth->fetchrow_array()) { | |
if (defined $expected && $row[0] != $expected) { | |
printf "%s - %s\n", strftime($fmt, localtime $expected), strftime($fmt, localtime ($row[0]-60)) | |
} | |
$expected=$row[0]+60 | |
} | |
# end wolfrevokcats's solution | |
print "Time: ",tv_interval($t0),"\n"; | |
} | |
$dbh->disconnect; | |
__DATA__ | |
1523182500 | |
1523182560 | |
1523182620 | |
1523182680 | |
1523182740 | |
1523182800 | |
1523182860 | |
1523182920 | |
1523182980 | |
1523183040 | |
1523183100 | |
1523183160 | |
1523183220 | |
1523183280 | |
1523183340 | |
1523183400 | |
1523183460 | |
1523183520 | |
1523183580 | |
1523183640 | |
1523183700 | |
1523183760 | |
1523183820 | |
1523183880 | |
1523183940 | |
1523184000 | |
1523184060 | |
1523184120 | |
1523184180 | |
1523184240 | |
1523184300 | |
1523184360 | |
1523184420 | |
1523184480 | |
1523184540 | |
1523184600 | |
1523184660 | |
1523184720 | |
1523184780 | |
1523184840 | |
1523184900 | |
1523184960 | |
1523185020 | |
1523185080 | |
1523185140 | |
1523185200 | |
1523185260 | |
1523185320 | |
1523185380 | |
1523185440 | |
1523185500 | |
1523185560 | |
1523185620 | |
1523185680 | |
1523185740 | |
1523185800 | |
1523185860 | |
1523185920 | |
1523185980 | |
1523186040 | |
1523186100 | |
1523186160 | |
1523186220 | |
1523186280 | |
1523186640 | |
1523186700 | |
1523186760 | |
1523186820 | |
1523186880 | |
1523186940 | |
1523187000 | |
1523187060 | |
1523187120 | |
1523187180 | |
1523187240 | |
1523187300 | |
1523187360 | |
1523187420 | |
1523187480 | |
1523187540 | |
1523187600 | |
1523187660 | |
1523187720 | |
1523187780 | |
1523187840 | |
1523187900 | |
1523187960 | |
1523188020 | |
1523188080 | |
1523188140 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment