Forum Stats

  • 3,838,749 Users
  • 2,262,397 Discussions
  • 7,900,750 Comments

Discussions

oracle::occi::ResultSet::getTimestamp always returns the current time offset

When fetching the value of a TIMESTAMP column with oracle::occi::ResultSet::getTimestamp it seems that the time offset is always the current offset instead of the offset applicable at that timestamp.

To get the time zone offset we are using oracle::occi::Timestamp::getTimeZoneOffset method, but got the same results when retrieving a timestamp value in summer time (2020-09-30) than when retrieving other value which is in standard time (2020-03-01).

The environment is as follow:

  • Oracle XE 11G R2
  • Time Zone = Europe/Madrid (UTC offset: STD=+01:00; DST=+02:00)
  • Temp table with one row and two columns:
CREATE TABLE TEST_TZ_OFFSET
AS 
SELECT TIMESTAMP '2020-09-30 00:00:00.000000' AS DST,
       TIMESTAMP '2020-03-01 00:00:00.000000' AS STD
FROM DUAL
  • Oracle Instant Client for Linux x86-64 Version 12.2.0.1.0

When the column in DST is fetched it works as expected (7200 seconds = +02:00), but when STD column is fetched it has the same offset as in DST when it is suppose to be 3600 seconds (+01:00)

long getGMTOffset(oracle::occi::Connection *conn, const std::string &columnName) {
    oracle::occi::Timestamp timestamp;
    oracle::occi::Statement *stmt;
    int tzhour = 0, tzminute = 0;

    try {
        std::string query = "SELECT " + columnName + " FROM TEST_TZ_OFFSET";
        stmt = conn->createStatement();
        stmt->setSQL(query);
        oracle::occi::ResultSet *rs = stmt->executeQuery();

        if (rs->next()) {
            timestamp = rs->getTimestamp(1);
        }
        
        stmt->closeResultSet(rs);
        conn->terminateStatement(stmt);
    } catch (const std::exception &e) {
        std::cerr << e.what() << "\n";
        conn->terminateStatement(stmt);
    }

    timestamp.getTimeZoneOffset(tzhour, tzminute);
    return (tzhour * 60 + tzminute) * 60;
}

std::cout << "GMT Offset of STD column = " << getGMTOffset(connection, "STD") << "\n";
std::cout << "GMT Offset of DST column = " << getGMTOffset(connection, "DST") << "\n";

Output:

GMT Offset of STD column = 7200
GMT Offset of DST column = 7200

Is this the expected behaviour?