6 Replies Latest reply on Sep 19, 2014 10:49 AM by Steven Hawkins

    date and timestamp comparison in between statement

    gadeyne.bram Master

      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

        • 1. Re: date and timestamp comparison in between statement
          Ramesh Reddy Master

          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..

          • 2. Re: date and timestamp comparison in between statement
            Steven Hawkins Master

            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

            • 3. Re: date and timestamp comparison in between statement
              gadeyne.bram Master

              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

              • 4. Re: date and timestamp comparison in between statement
                Steven Hawkins Master

                > 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.

                • 5. Re: date and timestamp comparison in between statement
                  gadeyne.bram Master

                  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

                  • 6. Re: date and timestamp comparison in between statement
                    Steven Hawkins Master

                    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.