Timestamp with timezone: works with isql but not with DBD::Firebird

68 Views Asked by At

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'
0

There are 0 best solutions below