date and timestamp comparison in between statement

[gadeyne.bram]

Hi,

I have a table that defines a column named time witch is of type timestamp. The real database is a Microsoft SQL Server database.

The column actually contains dates. So the hour part is always 00:00.

I wanted to select all records for July 2013. If I'm correct then the between statement should always include the start and end of the range?

This did not select the records for July 31:

select *

from tableX

where time between '2013-07-01' and '2013-07-31'

This did select the records for July 31:

select *

from tableX

where time between PARSETIMESTAMP('2013-07-01','yyyy-MM-dd') and PARSETIMESTAMP('2013-07-31','yyyy-MM-dd')

Is this behaviour normal?

With kind regards

Bram

[rareddy]

I believe when timestamp is defined in terms of string field, Teiid assumes the format to be in the form of "yyyy-mm-dd hh:mm:ss", which is default by java.sql.Timestamp, what I do not know is if format is not matched what should be the expected behavior. I always try to escaped format like

{ts '2014-09-11 11:28:13'}
{d '2014-09-11'}
{t '11:28:42'}

Ramesh..

[shawkins]

We need to see where the comparison is happening and if any timezone differences come into play.  In some cases even though you think you are comparing based upon calendar fields, a timezone difference will cause a shift in the value.

Steve

[gadeyne.bram]

Hi Steven and Ramesh,

I'm not sure if there is a question here for me in your replies.

My client and server are running in the same timezone. Does Teiid convert timezone's using default server time or always to UTC?

With kind regards

Bram

[shawkins]

> Does Teiid convert timezone's using default server time or always to UTC?

Internally everything is UTC as that is Java's time handling model.  But there is logic from both Teiid Client to Teiid Server and from Teiid Server to Source that deals with timezone handling.

The Teiid server will send the server timezone to the client.  If it does not match the client then any bound time values that isn't set with an explicit Calendar will be adjusted accordingly.  Then on the server side any parsed time value will be assumed to be in the server timezone.  From there any bind value in a source query will be subject to the driver's timezone handling, and an explicit timezone can be set on the translator if needed.

What is the sql sent to the source? And if you turn bind values off do you see different results.

[gadeyne.bram]

Hi,

The original example does not work anymore since the data has changed and there are no rows returned. But I have a new scenario with the same problem.

So this query:

select count(*)

from dbX_tableX p

where p.Time between '2014-07-01' and '2014-07-31'

is translated into:

SELECT TOP 100 COUNT(*) AS c_0 FROM "dbX"."dbo"."tableX" g_0 WHERE convert(varchar, g_0."Time", 21) >= '2014-07-01' AND convert(varchar, g_0."Time", 21) <= '2014-07-31'

The result here is 6363 rows

And this quey:

select count(*)

from dbX_tableX p

where p.Time between PARSETIMESTAMP('2014-07-01','yyyy-MM-dd') and PARSETIMESTAMP('2014-07-31','yyyy-MM-dd')

is translated into:

SELECT TOP 100 COUNT(*) AS c_0 FROM "dbX"."dbo"."tableX" g_0 WHERE g_0."Time" >= ? AND g_0."Time" <= ?

The result here is 6576 rows.

The time column is a SQL Server datetime. In Teiid designer it is defined as dateTime. So it seems like string conversion is used in the first example. Would it be reasonable to also match yyyy-MM-dd and compare dates?

With kind regards

Bram

[shawkins]

The first query is using string comparison, while the second is using timestamp.  More than likely the source dateTime is mapped to a timestamp Teiid type.  So in the first case we do look for timestamp string literals, and since they are not use string comparison.