Re: [teiid-users] sql server time column
by Steven Hawkins
It's not an issue of whether it can be addressed at the connector level (see the response to Larry). It's a question of whether it's appropriate at the connector level.
----- Original Message -----
From: "Ramesh Reddy" <rareddy(a)redhat.com>
To: "Steven Hawkins" <shawkins(a)redhat.com>
Cc: "Warren Gibson" <wgibson(a)redhat.com>, "Paul Nittel" <pnittel(a)redhat.com>, "teiid-users" <teiid-users(a)lists.jboss.org>, "teiid-dev" <teiid-dev(a)lists.jboss.org>
Sent: Thursday, July 23, 2009 10:28:31 AM GMT -06:00 US/Canada Central
Subject: Re: [teiid-users] sql server time column
Is there anyway we can query the SQLServer for how it is basing the
time? If we can, can it be a connector capability?
On Thu, 2009-07-23 at 10:09 -0400, Steven Hawkins wrote:
> Several changes in Teiid have made our old behavior of allowing users to mark datatime columns from SQL Server/Sybase as time type in Teiid not work (it used to only fail in some situations with prepared statements). The issue is that SQL Server treats the default year for a time value as 1900 instead of 1970.
>
> My preferred approach is to have users represent the source datetime as a timestamp in Teiid (which is the default upon import), but perform type reconciliation in a virtual model using the following expression - convert(timestampadd(SQL_TSI_YEAR, 70, <source datatime>), time).
>
> To ensure this doesn't harm performance I can add a rewrite rule to perform the inversion of timestampadd.
>
> Since it includes the time type, this isn't an issue with SQL Server 2008.
>
> Steve
> _______________________________________________
> teiid-users mailing list
> teiid-users(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/teiid-users
14 years, 10 months
Re: [teiid-users] sql server time column
by Steven Hawkins
There are workarounds that can be done (the previous approach was to change time literals to {ts'1900-01-01 <time>}), but I would rather not do them because:
1. customers are unlikely to have this scenario where a datetime artificially represents only time values - we forced the issue with our bqt testing
2. if you just wrap the column in the appropriate expression at the connector level, you miss out on our rewriter enhancements.
----- Original Message -----
From: "Larry O'Leary" <loleary(a)redhat.com>
To: "Steven Hawkins" <shawkins(a)redhat.com>
Cc: "Warren Gibson" <wgibson(a)redhat.com>, "Paul Nittel" <pnittel(a)redhat.com>, "teiid-users" <teiid-users(a)lists.jboss.org>, "teiid-dev" <teiid-dev(a)lists.jboss.org>
Sent: Thursday, July 23, 2009 10:02:58 AM GMT -06:00 US/Canada Central
Subject: Re: [teiid-users] sql server time column
On Thu, 2009-07-23 at 10:09 -0400, Steven Hawkins wrote:
> Several changes in Teiid have made our old behavior of allowing users to mark datatime columns from SQL Server/Sybase as time type in Teiid not work (it used to only fail in some situations with prepared statements). The issue is that SQL Server treats the default year for a time value as 1900 instead of 1970.
>
> My preferred approach is to have users represent the source datetime as a timestamp in Teiid (which is the default upon import), but perform type reconciliation in a virtual model using the following expression - convert(timestampadd(SQL_TSI_YEAR, 70, <source datatime>), time).
Can this not be done in the connector?
--
Larry O'Leary <loleary(a)redhat.com>
Red Hat, Inc.
14 years, 10 months
sql server time column
by Steven Hawkins
Several changes in Teiid have made our old behavior of allowing users to mark datatime columns from SQL Server/Sybase as time type in Teiid not work (it used to only fail in some situations with prepared statements). The issue is that SQL Server treats the default year for a time value as 1900 instead of 1970.
My preferred approach is to have users represent the source datetime as a timestamp in Teiid (which is the default upon import), but perform type reconciliation in a virtual model using the following expression - convert(timestampadd(SQL_TSI_YEAR, 70, <source datatime>), time).
To ensure this doesn't harm performance I can add a rewrite rule to perform the inversion of timestampadd.
Since it includes the time type, this isn't an issue with SQL Server 2008.
Steve
14 years, 10 months