[teiid-issues] [JBoss JIRA] (TEIID-1841) week function in teiid not returning expected value

[Steven Hawkins (Updated) (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1841?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-1841:
----------------------------------

           Issue Type: Quality Risk  (was: Bug)
    Affects Version/s: 7.1
                           (was: 7.5)
          Component/s: ODBC


There is a difference in the date/time logic between the two systems.  Postgresql is using ISO 8601 and Teiid uses the Java calendar logic.  Since there is no ANSI standard covering this function, you'll see quite a bit of variation, for example see mysql.

As for a resolution here, we cannot simply change the teiid week function as that would be a breaking change for others and it is not incorrect.  We could look at a system property that would switch the implementation to mimic postgresql.  In general though we have not made a guarantee that our functions will return the same results as postgresql, so you'll probably find other discrepancies as well.
                
> week function in teiid not returning expected value
> ---------------------------------------------------
>
>                 Key: TEIID-1841
>                 URL: https://issues.jboss.org/browse/TEIID-1841
>             Project: Teiid
>          Issue Type: Quality Risk
>          Components: ODBC
>    Affects Versions: 7.1
>         Environment: Teiid 7.5 running on java 1.6 openjdk on rhel5
>            Reporter: Graeme Gillies
>            Assignee: Steven Hawkins
>
> One of our users of our teiid instance as reported that the week function (to determine which week of the year it is) operates in teiid a little different to how they expect.
> Teiid resets the week count to 1 on every 1st of January even though
> the day is not the 1st day of the week (Sunday in here).
> But Postgres will only resets the week count to 1 only on the 1st Monday
> of the year.
> The examples below compare the results return by teiid and Postgres:
> {noformat}
> # Teiid result:
> EngVDBR=> select week(parsedate('2011-01-01', 'yyyy-MM-dd')) AS Week;
> week
> ------
> 1
> (1 row)
> EngVDBR=> select week(parsedate('2011-01-02', 'yyyy-MM-dd')) AS Week;
> week
> ------
> 2
> (1 row)
> {noformat}
> {noformat}
> # Postgres result:
> postgres=> SELECT EXTRACT(WEEK FROM DATE '2011-01-01') AS Week;
> week
> ------
> 52
> (1 row)
> postgres=> SELECT EXTRACT(WEEK FROM DATE '2011-01-03') AS Week;
> week
> ------
> 1
> (1 row)
> {noformat}

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira