Was there a change in the time zone meaning of SQL_TIMESTAMP?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Was there a change in the time zone meaning of SQL_TIMESTAMP?

Reza Taheri

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

 


_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Was there a change in the time zone meaning of SQL_TIMESTAMP?

Nick Gorham-2
On 18/11/16 20:21, Reza Taheri wrote:

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


I think this is likely to be a driver thing, the DM doesn't get involved with bound parameters by and large.

--
Nick
_______________________________________________
unixODBC-support mailing list
[hidden email]
http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
Loading...