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
15 years, 5 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.
15 years, 5 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
15 years, 5 months