1

I'm getting the time zone offset of a TIMESTAMP column 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?

Lucas Ayala
  • 2,309
  • 2
  • 16
  • 20
  • It may just be giving you back the current offset, rather than the offset at that timestamp. Does it work if you use a `TIMESTAMP_WITH_TIMEZONE` column as a source instead? Try something like `TO_TIMESTAMP_TZ(TIMESTAMP '2020-09-30 00:00:00.000000') AS DST`, etc... – Matt Johnson-Pint Apr 25 '22 at 22:49
  • [Here's it working in pure SQL statements](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=208783b8823adc50d400fbe494a2b699). I don't have a good OCCI test harness available at the moment. – Matt Johnson-Pint Apr 25 '22 at 22:56
  • Also - I hope that string concatenation in your example is just for testing. It would be a potential source for a SQL Injection attack in the real world. – Matt Johnson-Pint Apr 25 '22 at 22:58
  • @MattJohnson-Pint, I've tried with `TIMESTAMP_WITH_TIMEZONE` and it works the same way. It seems that `oracle::occi::ResultSet::getTimestamp` always set the current offset, no matter what value of timestamp is retrieving. – Lucas Ayala Apr 26 '22 at 09:26
  • @MattJohnson-Pint, also thanks for your working example in pure SQL, but unfortunately I cannot add the calculated column to the query. And yes, my example is just for testing, so there is no risk of SQL injection. – Lucas Ayala Apr 26 '22 at 09:29
  • If `oracle::occi::ResultSet::getTimestamp` is always returning the *current* offset instead of the offset applicable at that timestamp, that sounds like a bug to me. You'll probably have to go through Oracle Support to get it investigated properly, though you could also try the forum at https://community.oracle.com/tech/developers/categories/call_interface. Good luck! – Matt Johnson-Pint Apr 26 '22 at 19:01
  • 1
    I've asked in the community forum https://community.oracle.com/tech/developers/discussion/4497875/oracle-occi-resultset-gettimestamp-always-returns-the-current-time-offset – Lucas Ayala Apr 27 '22 at 13:18
  • Feel free to post an answer here when you learn something either way. Thanks! – Matt Johnson-Pint Apr 28 '22 at 17:24

0 Answers0