[unixODBC-support] Was there a change in the time zone meaning of SQL_TIMESTAMP?

Reza Taheri rtaheri at vmware.com
Fri Nov 18 20:21:15 GMT 2016


We have developed a performance benchmark (http://www.tpc.org/tpcx-v) with PostgreSQL using ODBC in the application. Everything was working fine up to

·         postgresql93-9.3.5-2PGDG.rhel7

·         postgresql93-odbc-09.03.0300-1PGDG.rhel7

·         unixODBC-2.3.1-10.el7
Then, someone took our kit, and installed it on a system with

·         postgresql93-9.3.14-1PGDG.rhel7

·         postgresql93-odbc-09.05.0200-1PGDG.rhel7

·         unixODBC-2.3.1-11.el7

·         A twist here may be that he may have loaded the database in one time zone, later running the test from another time zone

Now we are running into the following problem: the server rejects stored procedure calls with:
<>ERROR:  function traderesultframe5(bigint, numeric, unknown, timestamp with time zone, bigint, numeric) does not exist at character 15
<>HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I can see why this could happen since psql tpcv -c '\df' says our function is declared as:

public | traderesultframe5                | void                                                              | broker_id ident_t, comm_amount value_t, st_completed_id character, trade_dts timestamp without time zone, trade_id trade_t, trade_price s_price_t

So, the problem is with the timestamp parameter. We bind the timestamp argument with: SQLBindParameter(…, SQL_C_TIMESTAMP, SQL_TIMESTAMP, …). This used to work with the older revs. But not for this user. I was able to get PostgreSQL to accept the function call by changing the stored procedure to explicitly declare the parameter as timestamp with time zone. But timestamps (not stating with or without time zone) are used all over the place in our application, and according to PostgreSQL documentation:

The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior.

So, to be consistent, we’d have to change all our declarations to say with time zone. That’s a pain. Plus, it seems like PGSQL actually recommends storing timestamps without time zone (i.e., absolute time)

Did the definition of SQL_TIMESTAMP somehow change such that it now implies timestamp with time zone?  Or did some marshaling that used to take place doesn’t work anymore?

Also, is there a way for SQLBindParameter() to explicitly bind the argument as SQL_TIMESTAMP and force it to be without time zone?

Thanks,
Reza

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-support/attachments/20161118/e7ebbeaa/attachment-0001.html>


More information about the unixODBC-support mailing list