Why does using a timestamp with a UTC time zone cause the result to be ** unknown **
when running the query with DBD::Firebird?
The same query works with isql. The Firebird version is 4.0.4.
use DBI;
my $dbh = DBI->connect( "dbi:Firebird:dbname=$db", $user, $passwd ) or die DBI->errstr;
my $table = 'test_table_unix_seconds';
my $col = 'sec';
$dbh->do( "RECREATE TABLE $table ($col INT)" );
$dbh->do( "INSERT INTO $table ($col) VALUES(86400)" );
my $sth = $dbh->prepare( "SELECT $col, dateadd(SECOND,$col,TIMESTAMP '1970-01-01') FROM $table" );
$sth->execute();
$sth->dump_results;
# 86400, 'Fr 02 Jan 1970 00:00:00 CET'
$sth = $dbh->prepare( "SELECT $col, dateadd(SECOND,$col,TIMESTAMP '1970-01-01 UTC') FROM $table" );
$sth->execute();
$sth->dump_results;
# 86400, '** unknown **'
$sth = $dbh->prepare( "SELECT $col, dateadd(SECOND,$col,TIMESTAMP '1970-01-01 UTC') at time zone 'CET' as ts FROM $table" );
$sth->execute();
$sth->dump_results;
# 86400, '** unknown **'
With isql:
SQL> SELECT sec, dateadd(SECOND,sec,TIMESTAMP '1970-01-01 UTC') at time zone 'CET' as ts FROM test_table_unix_seconds;
SEC TS
============ =========================================================
86400 1970-01-02 01:00:00.0000 CET
The UTC timezone timestamp works in this example:
$sth = $dbh->prepare(
"SELECT datetime_ts, DATEDIFF(SECOND,TIMESTAMP '1970-01-01 00:00:00 UTC',datetime_ts) FROM test_timestamp"
);
$sth->execute();
$sth->dump_results;
# 'Fr 02 Jan 1970 00:00:00 CET', '82800'