HSQL 2.3.4 and TO_CHAR with IW

[Sliderule Smith]

Using HSQL Version 2.3.4, and, I am wondering if the Documentation and the results are in sync when using TO_CHAR function with IW as one of the parameters.

Consider the following:

CREATE CACHED TABLE MY_DATE_WEEK_TEST (ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) PRIMARY KEY, MY_DATE DATE NOT NULL);

INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-20');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-21');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-22');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-23');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-24');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-25');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-26');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-27');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-28');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-29');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-30');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2015-12-31');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2016-01-01');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2016-01-02');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2016-01-03');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2016-01-04');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2016-01-05');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2016-01-06');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2016-01-07');
INSERT INTO MY_DATE_WEEK_TEST (MY_DATE) VALUES('2016-01-08');

SELECT "ID", "MY_DATE", TO_CHAR("MY_DATE", 'DAY, YYYY-MM-DD') AS "MY_DATE_TO_CHAR", WEEK("MY_DATE") AS "WEEK", TO_CHAR("MY_DATE", 'WW') AS "TO_CHAR_WW", TO_CHAR("MY_DATE",'IW') AS "TO_CHAR_IW" FROM "MY_DATE_WEEK_TEST" AS "MY_DATE_WEEK_TEST";

Since the Documentation ( PDF file, Table 10.1. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements ) states:

WW Week of year (1-53) where week 1 starts on the first day of the year and continues
to the seventh day of the year (not a calendar week).

IW Week of year (1-52 or 1-53) based on the ISO standard. Week starts on Monday.
The first week may start near the end of previous year.

But, the above Query, seems to change the WEEK NUMBER, when using TO_CHAR with IW based on a Sunday rather then the documented Monday.

Is this code correct, or, the documentation, or am I missing something obvious ( of course, would not be the first time today I am missing something obvious, and, I assume not the last ).

Thanks for the help.

Sliderule

[Fred Toussi]

Thanks. There was an issue with IW for the end of the year which has been fixed in current SVN code. IW does change the week on Monday.

ID MY_DATE             MY_DATE_TO_CHAR       WEEK TO_CHAR_WW TO_CHAR_IW 
-- ------------------- --------------------- ---- ---------- ---------- 
1  2015-12-20 00:00:00 Sunday, 2015-12-20    51   51         51         
2  2015-12-21 00:00:00 Monday, 2015-12-21    52   51         52         
3  2015-12-22 00:00:00 Tuesday, 2015-12-22   52   51         52         
4  2015-12-23 00:00:00 Wednesday, 2015-12-23 52   51         52         
5  2015-12-24 00:00:00 Thursday, 2015-12-24  52   52         52         
6  2015-12-25 00:00:00 Friday, 2015-12-25    52   52         52         
7  2015-12-26 00:00:00 Saturday, 2015-12-26  52   52         52         
8  2015-12-27 00:00:00 Sunday, 2015-12-27    52   52         52         
9  2015-12-28 00:00:00 Monday, 2015-12-28    53   52         53         
10 2015-12-29 00:00:00 Tuesday, 2015-12-29   53   52         53         
11 2015-12-30 00:00:00 Wednesday, 2015-12-30 53   52         53         
12 2015-12-31 00:00:00 Thursday, 2015-12-31  53   53         53         
13 2016-01-01 00:00:00 Friday, 2016-01-01    53   1          53         
14 2016-01-02 00:00:00 Saturday, 2016-01-02  53   1          53         
15 2016-01-03 00:00:00 Sunday, 2016-01-03    53   1          53         
16 2016-01-04 00:00:00 Monday, 2016-01-04    1    1          1          
17 2016-01-05 00:00:00 Tuesday, 2016-01-05   1    1          1          
18 2016-01-06 00:00:00 Wednesday, 2016-01-06 1    1          1          
19 2016-01-07 00:00:00 Thursday, 2016-01-07  1    1          1          
20 2016-01-08 00:00:00 Friday, 2016-01-08    1    2          1          

[Sliderule Smith]

Fred:
Just as an FYI . . . I am not getting the same results you displayed above when testing with hsqldb.jar as posted on the SNAPSHOT page

( [http://www.hsqldb.org/repos/org/hsqldb/hsqldb/SNAPSHOT/] ) . . .

that is file hsqldb-20161209.170108-64.jar .

The results I am getting are below, where, IW still seems be be changing on Sunday rather than Monday as expected:

ID  MY_DATE              MY_DATE_TO_CHAR        WEEK  TO_CHAR_WW  TO_CHAR_IW
--  -------------------  ---------------------  ----  ----------  ----------
 1  2015-12-20 00:00:00  Sunday, 2015-12-20       51  51          52
 2  2015-12-21 00:00:00  Monday, 2015-12-21       52  51          52
 3  2015-12-22 00:00:00  Tuesday, 2015-12-22      52  51          52
 4  2015-12-23 00:00:00  Wednesday, 2015-12-23    52  51          52
 5  2015-12-24 00:00:00  Thursday, 2015-12-24     52  52          52
 6  2015-12-25 00:00:00  Friday, 2015-12-25       52  52          52
 7  2015-12-26 00:00:00  Saturday, 2015-12-26     52  52          52
 8  2015-12-27 00:00:00  Sunday, 2015-12-27       52  52          1
 9  2015-12-28 00:00:00  Monday, 2015-12-28       53  52          1
10  2015-12-29 00:00:00  Tuesday, 2015-12-29      53  52          1
11  2015-12-30 00:00:00  Wednesday, 2015-12-30    53  52          1
12  2015-12-31 00:00:00  Thursday, 2015-12-31     53  53          1
13  2016-01-01 00:00:00  Friday, 2016-01-01       53  1           1
14  2016-01-02 00:00:00  Saturday, 2016-01-02     53  1           1
15  2016-01-03 00:00:00  Sunday, 2016-01-03       53  1           2
16  2016-01-04 00:00:00  Monday, 2016-01-04        1  1           2
17  2016-01-05 00:00:00  Tuesday, 2016-01-05       1  1           2
18  2016-01-06 00:00:00  Wednesday, 2016-01-06     1  1           2
19  2016-01-07 00:00:00  Thursday, 2016-01-07      1  1           2
20  2016-01-08 00:00:00  Friday, 2016-01-08        1  2           2

[Sliderule Smith]

While using the latest SVN to build hsqldb.jar as of 2017-02-17, I am still getting results as below, where, the TO_CHAR function with IW parameter is returning a change of week on Sunday, not Monday as per documentation. Just as an FYI, this is HSQL Version 2.4.0 using and java.runtime.version: (1.8.0_121-b13) .

ID	MY_DATE	MY_DATE_TO_CHAR	WEEK	TO_CHAR_WW	TO_CHAR_IW
1	2015-12-20 00:00:00	Sunday, 2015-12-20	51	51	52
2	2015-12-21 00:00:00	Monday, 2015-12-21	52	51	52
3	2015-12-22 00:00:00	Tuesday, 2015-12-22	52	51	52
4	2015-12-23 00:00:00	Wednesday, 2015-12-23	52	51	52
5	2015-12-24 00:00:00	Thursday, 2015-12-24	52	52	52
6	2015-12-25 00:00:00	Friday, 2015-12-25	52	52	52
7	2015-12-26 00:00:00	Saturday, 2015-12-26	52	52	52
8	2015-12-27 00:00:00	Sunday, 2015-12-27	52	52	1
9	2015-12-28 00:00:00	Monday, 2015-12-28	53	52	1
10	2015-12-29 00:00:00	Tuesday, 2015-12-29	53	52	1
11	2015-12-30 00:00:00	Wednesday, 2015-12-30	53	52	1
12	2015-12-31 00:00:00	Thursday, 2015-12-31	53	53	1
13	2016-01-01 00:00:00	Friday, 2016-01-01	53	1	1
14	2016-01-02 00:00:00	Saturday, 2016-01-02	53	1	1
15	2016-01-03 00:00:00	Sunday, 2016-01-03	53	1	2
16	2016-01-04 00:00:00	Monday, 2016-01-04	1	1	2
17	2016-01-05 00:00:00	Tuesday, 2016-01-05	1	1	2
18	2016-01-06 00:00:00	Wednesday, 2016-01-06	1	1	2
19	2016-01-07 00:00:00	Thursday, 2016-01-07	1	1	2
20	2016-01-08 00:00:00	Friday, 2016-01-08	1	2	2

[Fred Toussi]

What is your time zone?

[Sliderule Smith]

Time zone is PST ( Pacific Standard Time ) for North America ( GMT - 8 ), and, the field MY_DATE is a DATE rather than a TIMESTAMP.

[Sliderule Smith]

Fred:

Downloaded the latest SVN . . . 2017-03-12 . . . and . . . now getting the following results . . . that . . . I believe are correct, that is, using TO_CHAR function, IW is changing on MONDAY as per documentation.

The output below is from the Select statement:

SELECT DATABASE_VERSION() AS HSQL_VER, DATABASE_TIMEZONE() AS DB_TZ, ID, MY_DATE, TO_CHAR(MY_DATE, 'DY, YYYY-MM-DD') AS MY_DATE_TO_CHAR, WEEK(MY_DATE) AS WEEK, TO_CHAR(MY_DATE, 'WW') AS TO_CHAR_WW, TO_CHAR(MY_DATE,'IW') AS TO_CHAR_IW FROM MY_DATE_WEEK_TEST;

HSQL_VER  DB_TZ  ID  MY_DATE     MY_DATE_TO_CHAR  WEEK  TO_CHAR_WW  TO_CHAR_IW
--------  -----  --  ----------  ---------------  ----  ----------  ----------
2.4.0     -7:00   1  2015-12-20  Sun, 2015-12-20    51  51          51
2.4.0     -7:00   2  2015-12-21  Mon, 2015-12-21    52  51          52
2.4.0     -7:00   3  2015-12-22  Tue, 2015-12-22    52  51          52
2.4.0     -7:00   4  2015-12-23  Wed, 2015-12-23    52  51          52
2.4.0     -7:00   5  2015-12-24  Thu, 2015-12-24    52  52          52
2.4.0     -7:00   6  2015-12-25  Fri, 2015-12-25    52  52          52
2.4.0     -7:00   7  2015-12-26  Sat, 2015-12-26    52  52          52
2.4.0     -7:00   8  2015-12-27  Sun, 2015-12-27    52  52          52
2.4.0     -7:00   9  2015-12-28  Mon, 2015-12-28    53  52          53
2.4.0     -7:00  10  2015-12-29  Tue, 2015-12-29    53  52          53
2.4.0     -7:00  11  2015-12-30  Wed, 2015-12-30    53  52          53
2.4.0     -7:00  12  2015-12-31  Thu, 2015-12-31    53  53          53
2.4.0     -7:00  13  2016-01-01  Fri, 2016-01-01    53  1           53
2.4.0     -7:00  14  2016-01-02  Sat, 2016-01-02    53  1           53
2.4.0     -7:00  15  2016-01-03  Sun, 2016-01-03    53  1           53
2.4.0     -7:00  16  2016-01-04  Mon, 2016-01-04     1  1           1
2.4.0     -7:00  17  2016-01-05  Tue, 2016-01-05     1  1           1
2.4.0     -7:00  18  2016-01-06  Wed, 2016-01-06     1  1           1
2.4.0     -7:00  19  2016-01-07  Thu, 2016-01-07     1  1           1
2.4.0     -7:00  20  2016-01-08  Fri, 2016-01-08     1  2           1

Thank-you for this bug fix with the next HSQL release version.