Hi all,

I've got two copies of a database with the same schema. I access the
database through a C program (with embedded SQL, preprocessed by
gpre). One of the tables (let's call it T) has a field called D, which
is of TIMESTAMP type.

If this helps, I'm using Firebird Classic Server 2.0.1.12981-1
(installed through RPM) under Linux 2.6.16 with gcc 4.1.2 20070115.

(Sorry for weird table names, column names, and output, but I'm
working with confidential data, so I need to mangle them.)

===
#include <stdio.h>
#include <time.h>
#include "ibase.h"

/*
* snipped...
*/

struct tm tm;
time_t lt;
ISC_TIMESTAMP ts;

/* Set 28 February 2008 00:00:00 */
tm.tm_sec = tm.tm_min = tm.tm_hour = 0;
tm.tm_mday = 28;
tm.tm_mon = 2 - 1;
tm.tm_year = 2008 - 1900;

if ((lt = mktime(&tm)) == (time_t)-1)
return 0;

isc_encode_timestamp(localtime(<), &ts);

EXEC SQL
DECLARE Z CURSOR FOR
SELECT A, B, C
FROM T
WHERE D = :ts;

printf("D: (%d, %u)\n", ts.timestamp_date, ts.timestamp_time);

EXEC SQL
OPEN Z;

do {
long a, b, c;

EXEC SQL
FETCH Z INTO :a, :b, :c;

printf("a: %ld, b: %ld, c: %d\n", a, b, c);
} while (SQLCODE == 0);
===

With copy #1:

> export TZ=whatever
> ./get_data

[...snipped...]
D: (54524, 0)
a: 1 b: 2 c: 3
a: 4 b: 5 c: 6
[...lots of data...]

> export TZ=Australia/Melbourne
> ./get_data

[...snipped...]
D: (54524, 36000000)
[...and that's it, no row returned...]

With copy #2:

> export TZ=whatever
> ./get_data

[...snipped...]
D: (54524, 0)
a: 1 b: 2 c: 3
a: 4 b: 5 c: 6
[...lots of data...]

> export TZ=Australia/Melbourne
> ./get_data

[...snipped...]
D: (54524, 0)
a: 1 b: 2 c: 3
a: 4 b: 5 c: 6
[...lots of data...]

Notice that when running with copy #1, the time portion of the ts
variable is set to +10 hours when the TZ environment variable is set
to "Australia/Melbourne", but not with copy #2, which is invariant
with respect to TZ.

The get_data binary never got recompiled, i.e. the same binary is used.
Perhaps isc_encode_timestamp() does something I don't know.

What could have gone wrong?

Thanks.