Hi all,

I know that this is an unixODBC mail list and my question is related to ODBC++ library but it was impossible to me to find a forum dedicated to ODBC++ library. Sorry for any inconvenience.  I think Timestamp class has a bug  and I’d be glad if somebody could confirm (or not) my supposition. Any comment/suggestion is welcomed.

Next, I shortly describe the steps to reproduce the problem, the problem description and the proposed fix:

Steps to reproduce:

1.       Create one DB (MY_DB)with one TIMESTAMP column  called “timecol”

2.       Set the computer TimeZone to “GMT+01:00 Madrid” with DST activated

3.       Write into the DB the timestamp “1225027900”; it corresponds to:

GMT timezone:    Sun, 26 Oct 2008 13:31:40 UTC

Madrid timezone: Sunday, October 26, 2008 2:31:40 PM

The following piece of code is used to save the timestamp:

….

std::time_t t = 1225027900;

odbc::PreparedStatement *ins_stmt(prepareStatement("insert into MY_DB (\"timecol\") values (?)"));

ins_stmt->setTimestamp( 1, odbc::Timestamp(t));

ins_stmt->executeUpdate();

…

4.       Now, read from MY_DB:

…

odbc::PreparedStatement *sel_stmt(conn->get()->prepareStatement( "select * from MY_DB" ) );

      odbc::ResultSet rset( sel_stmt->executeQuery() );

      odbc::Timestamp ts( rset->getTimestamp(1));

std::time_t loadedFromDB = ts.getTime();

…

5.       The value retrieved from DB of loadedFromDB should be 1225027900 but instead its value is 3600 seconds less, that is, 1225024300!

Problem description:

Note: The 26th of October at 03:00 AM , Madrid’s TS, is when the transition from Daylight Saving Time (DST) to Standard Time (ST) takes place.

·         When writing data to the DB the Timezone constructor calls setTime(std::time_t) which in turn calls std::localtime. Thus, what we actually get saved into the DB is a local time, i.e, in our particular case: Sunday, October 26, 2008 2:31:40 PM which is correct taking into consideration that Madrid’s TZ is one hour ahead GMT.

·         When reading data back from the DB we first retrieve an object “ts” which holds exactly the date from the DB, i.e., Sunday, October 26, 2008 2:31:40 PM. So far, so good. Now comes into play odbc::Tiemstamp::getTime() method which is supposed to transform back the local time kept inside “ts” object into UTC. Basically what it does is (for the sake of simplicity I won’t show all code details):

time_t Time::getTime() const

{

tm stm;

stm.tm_year=year_-1900;

stm.tm_mon=month_-1;

stm.tm_mday=day_;

stm.tm_hour=0;  //red row

stm.tm_min=0;   //red row

stm.tm_sec=0;   //red row

  stm.tm_isdst=-1; //negative means not known

  return std::mktime(&stm) + second_ + minute_*60 + hour_*3600;

           }

                The problem we got here is easy to spot: std::mktime is passed 00:00 AM (see the rows highlighted in red) that is, three hours before the DST takes place. Therefore, std::mktime is unaware that the time we actually want to compute is ST. If we passed day 27th instead of 26th , std::mktime would return the correct result because the hours, minutes and seconds wouldn’t matter to decide whether we have to deal with DST or ST!

Proposed fix:

Thanks for any help/suggestion,

Costin.