Skip to content

Instantly share code, notes, and snippets.

@haukex
Created Apr 8, 2018
Embed
What would you like to do?
#!/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