Perl, SQL query and save data to csv file with correct Datetime format

Question

The data table has too many columns to select one by one, so I am trying to pull entire data into a file. There are a number of columns contains datetime for UTC and local time. When I used the following script, all hour information is deleted and only date is saved. How can I easily fix the code to save the entire datetime information?

In summery, all datetime data in csv file was saved as "25-FEB-15" instead of "25-FEB-15 HH:MM:SS AM -08:00"

open(OUTFILE, "> ./outputfile.csv");  

my($dbh,$sth);
$dbh = DBI->connect("xxx")

my $sqlGetEid = "

select *
from Table_1
where MARKET = 'Chicago' and DATETIMELOCAL >= '22-FEB-2015' and DATETIMELOCAL < '01-MAR-2015' 

";

my $curSqlEid = $dbh->prepare($sqlGetEid);

$curSqlEid->execute();

my $counter = 0;
my $delimiter = ',';
my $fields = join(',', @{ $curSqlEid->{NAME_lc} });
print "$fields\n";
printf OUTFILE "$fields\n";

while (my @row = $curSqlEid->fetchrow_array) {  
    my $csv = join(',', @row)."\n";
    printf OUTFILE "$csv";
    $counter ++;
    if($counter % 10000 == 0){
        print $csv, "\n";
    }
}

I have fixed codes according to the comments below, but the problem is not resolved yet. The DB I am using is Oracle, so MySQL based codes seems not compatible. The problem is narrow down to the datetime formats that Oracle uses, so by properly handling the format this problem can be resolved. But I am not sure which perl package I have to use to nicely handle Oracle datatime formats.

use DBI;
use DBD::Oracle qw(:ora_types);
use Compress::Zlib;
use FileHandle;
use strict;
use warnings;
use DateTime;
use Data::Dumper;
use Text::CSV_XS;
use DateTime::Format::DateParse;
use DateTime::Format::DBI;


open(OUTFILE, "> ./output.csv");  

my $dbh = DBI->connect("xxx")

my $sth = $dbh->prepare("SELECT * FROM Table_1");
$sth->execute;
my $fields = join(',', @{ $sth->{NAME_lc} });


#EXTRACT COLUMN NAMES
my @col_names = @{ $sth->{NAME_lc} } ;
my $column_names = join ", ", @col_names;


my $select = "
select $column_names
from Table_1
where MARKET = 'Chicago' and DATETIMELOCAL >= '22-FEB-2015' and DATETIMELOCAL < '01-MAR-2015' 
";

my $query = $dbh->prepare($select);
$query->execute;

#WRITE DATA TO CSV FILE
my $csv_attributes = {
    binary => 1,  #recommneded
    eol => $/,    #recommended(I wonder why it's not $\ ?)
};

my $csv = Text::CSV_XS->new($csv_attributes);

my $fname = 'data.csv';
open my $OUTFILE, ">", $fname
    or die "Couldn't open $fname: $!";
$csv->print($OUTFILE, \@col_names);
$csv->column_names(@col_names);  #Needed for print_hr() below

while (my $row = $query->fetchrow_arrayref) {

    print  $row->[0];# $row->{datetimelocal} does not work
    my $datetime = DateTime::Format::DBI->parse_datetime( $row->[0]) ; # This might be wrong

    $row->[0] = strftime("%d-%b-%y %I:%M:%S %p", $datetime);    
    $csv->print($OUTFILE, $row);
}


close $OUTFILE;

$query->finish;
$dbh->disconnect;

Answer 1

The data table has too many columns to select one by one

Not an issue. We're computer programmers after all.

In summery, all datetime data in csv file was saved as "25-FEB-15" instead of "25-FEB-15 HH:MM:SS AM -08:00"

I'm not seeing that:

use strict; 
use warnings; 
use 5.012;
use Data::Dumper;

use DBI;
use DBD::mysql;
use Text::CSV_XS;

#CONFIG VARIABLES
my $db_type = "mysql";
my $database = "my_db";
my $host = "localhost";
my $port = "3306";
my $user = "root";
my $pword = "";

#DATA SOURCE NAME
my $dsn = "dbi:$db_type:$database:$host:$port";

#PERL DBI CONNECT
my $dbh = DBI->connect($dsn, $user, $pword);

my $tablename = "Table_1";

#CONDITIONALLY DROP THE TABLE
my $drop_table = "drop table if exists $tablename;";
my $query = $dbh->prepare($drop_table);
$query->execute();

#CONDITIONALLY CREATE THE TABLE
my $create_table =<<"END_OF_CREATE"; 
create table $tablename (
    id INT(12) not null auto_increment primary key, 
    open DECIMAL(6,4),
    high DECIMAL(6,4),
    low DECIMAL(6,4),
    close DECIMAL(6,4),
    market VARCHAR(40),
    datetimelocal DATETIME
)
END_OF_CREATE

$query = $dbh->prepare($create_table);
$query->execute();

#INSERT DATA INTO TABLE
my $insert =<<"END_OF_INSERT";
    insert into $tablename(open, high, low, close, market, datetimelocal)
    values (?, ?, ?, ?, ?, ?)
END_OF_INSERT

my @data = (
    [10.00, 12.00, 9.00, 11.50, 'Chicago', '2015-2-23 16:00:01'],
    [10.00, 12.01, 9.01, 11.51, 'New York', '2015-2-23 16:00:01'],
);

for my $aref (@data) {
    $query = $dbh->prepare($insert);
    $query->execute(@$aref);
}

#PREPARE COLUMN NAME QUERY
my $select =<<"END_OF_SELECT";
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name='Table_1'; 
END_OF_SELECT

$query = $dbh->prepare($select);
$query->execute;

#EXTRACT COLUMN NAMES
my @col_names = @{$dbh->selectcol_arrayref($query)};
my $column_names = join ", ", @col_names;

#PREPARE SELECT QUERY
$select =<<"END_OF_SELECT";
    select $column_names from $tablename
    where MARKET = 'Chicago' and DATETIMELOCAL >= '2015-2-22' and DATETIMELOCAL < '2015-3-1' 
END_OF_SELECT

$query = $dbh->prepare($select);
$query->execute;

#WRITE DATA TO CSV FILE
my $csv_attributes = {
    binary => 1,  #recommneded
    eol => $/,    #recommended(I wonder why it's not $\ ?)
};

my $csv = Text::CSV_XS->new($csv_attributes);

my $fname = 'data.csv';
open my $OUTFILE, ">", $fname
    or die "Couldn't open $fname: $!";
$csv->print($OUTFILE, \@col_names);

while (my $row = $query->fetchrow_arrayref) {
    $csv->print($OUTFILE, $row);
}

close $OUTFILE;

$query->finish;
$dbh->disconnect;

db table:

mysql> describe Table_1;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(12)      | NO   | PRI | NULL    | auto_increment |
| open          | decimal(6,4) | YES  |     | NULL    |                |
| high          | decimal(6,4) | YES  |     | NULL    |                |
| low           | decimal(6,4) | YES  |     | NULL    |                |
| close         | decimal(6,4) | YES  |     | NULL    |                |
| market        | varchar(40)  | YES  |     | NULL    |                |
| datetimelocal | datetime     | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
7 rows in set (0.19 sec)

mysql> select * from Table_1;
+----+---------+---------+--------+---------+----------+---------------------+
| id | open    | high    | low    | close   | market   | datetimelocal       |
+----+---------+---------+--------+---------+----------+---------------------+
|  1 | 10.0000 | 12.0000 | 9.0000 | 11.5000 | Chicago  | 2015-02-23 16:00:01 |
|  2 | 10.0000 | 12.0100 | 9.0100 | 11.5100 | New York | 2015-02-23 16:00:01 |
+----+---------+---------+--------+---------+----------+---------------------+
2 rows in set (0.00 sec)

output:

$ cat data.csv
id,open,high,low,close,market,datetimelocal
1,10.0000,12.0000,9.0000,11.5000,Chicago,"2015-02-23 16:00:01"

all datetime data in csv file was saved as "25-FEB-15" instead of "25-FEB-15 HH:MM:SS AM -08:00"

Note that a mysql DATETIME column type does not save timezone offset information. If you want to format your datetime and add a timezone offset, you can do something like this:

...
...
use DateTime::Format::MySQL;
use DateTime::Format::Strptime qw{ strftime };

...
...

#WRITE DATA TO CSV FILE
my $csv_attributes = {
    binary => 1,  #recommneded
    eol => $/,    #recommended(I wonder why it's not $\ ?)
};

my $csv = Text::CSV_XS->new($csv_attributes);

my $fname = 'data.csv';
open my $OUTFILE, ">", $fname
    or die "Couldn't open $fname: $!";

$csv->print($OUTFILE, \@col_names);
$csv->column_names(@col_names);  #Needed for print_hr() below

my $tz_offset = "-08:00";

while (my $row = $query->fetchrow_hashref) {
    my $datetime = DateTime::Format::MySQL->parse_datetime(
        $row->{datetimelocal}
    ); 

    #strftime() comes from DateTime::Format::Strptime:
    $row->{datetimelocal} = strftime(
        "%d-%b-%y %I:%M:%S %p $tz_offset", 
        $datetime
    );

    $csv->print_hr($OUTFILE, $row); #=>print hash ref.  To get the column order right, you first have to set the column order with $csv->column_names().
}

close $OUTFILE;

$query->finish;
$dbh->disconnect;

output:

$ cat data.csv
id,open,high,low,close,market,datetimelocal
1,10.0000,12.0000,9.0000,11.5000,Chicago,"23-Feb-15 04:00:01 PM -08:00"

Answer 2

Select each column explicitly. For the date column select a TO_DATE of the column with the format you want