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

[Steven Hawkins (Resolved) (JIRA)]

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

Steven Hawkins resolved TEIID-1841.
-----------------------------------

    Resolution: Done


Added the system property org.teiid.iso8601Week that modifies the behavior of the week and dayOfWeek functions to be consistent with ISO 8601 regardless of the system default locale.

Note however that the dayOfWeek function whether using org.teiid.iso8601Week or not is still different than the dow extract in PG.  PG uses 0-6 starting with Sunday.  Teiid in a typical US local will return 1-7 starting with Sunday, and with org.teiid.iso8601Week set to true will return 1-7 starting with Monday.
                
> 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
>             Fix For: 8.0
>
>
> 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