Search code examples
dateh2formatdatetime

H2 FORMATDATETIME() unexpecting changing year on 01/01

I'm trying to set the minutes and seconds to 00 in a date, I use formatdatetime(date, 'Y-MM-dd HH:00:00') and it works fine with normal dates, but trying with formatdatetime('2017-01-01 12:27:27', 'Y-MM-dd HH:00:00') the result is 2016-01-01 12:00:00 instead of 2017-01-01 12:00:00. Why?

Here is some sample code:

CREATE TABLE test_table (
timestamp DATETIME(3) NOT NULL
);

INSERT INTO test_table
VALUES 
('2017-01-01 12:27:27'), 
('2017-01-02 12:27:27'), 
('2017-01-03 12:27:27');

SELECT FORMATDATETIME(timestamp, 'Y-MM-dd HH:00:00')
FROM test_table;

Result is:

FORMATDATETIME(TIMESTAMP, 'Y-MM-dd HH:00:00')  
2016-01-01 12:00:00
2017-01-02 12:00:00
2017-01-03 12:00:00

Why this behaviour?

I tried with H2 1.4.192/JDK 1.7.0_80 and H2 1.4.195/JRE 1.8.0_74.

Output from SELECT timestamp FROM test_table is normal:

TIMESTAMP ▼
2017-01-01 12:27:27.0
2017-01-02 12:27:27.0
2017-01-03 12:27:27.0

EDIT: I've found the problem, it seems to be the locale:

Output from SELECT FORMATDATETIME(timestamp, 'Y-MM-dd HH:00:00 z', 'it', 'GMT') FROM test_table:

FORMATDATETIME(TIMESTAMP, 'Y-MM-dd HH:00:00 z', 'it')  
2016-01-01 12:00:00 CET
2017-01-02 12:00:00 CET
2017-01-03 12:00:00 CET

Output from SELECT FORMATDATETIME(timestamp, 'Y-MM-dd HH:00:00 z', 'en', 'GMT') FROM test_table:

FORMATDATETIME(TIMESTAMP, 'Y-MM-dd HH:00:00 z', 'en')  
2017-01-01 12:00:00 CET
2017-01-02 12:00:00 CET
2017-01-03 12:00:00 CET

With it locale it subtract one year at 01/01/2017, while with en locale it doesn't. Can someone explain me why this occurs? Shouldn't locale change only data rapresentation?

Solution

  • Ok issue solved, it was my misunderstanding on date formatting:

    H2 uses, as suggested by hendrik in his comment, Java SimpleDateFormat to format dates. Following SimpleDateFormat formats, 'Y' (uppercase) stands for week year (the year to whom the week belongs).

    Now 01/01/2017 was a Sunday. In the Italian locale (used on my machine), Sunday is considered the last day of the week (so the week belongs to the past year, 2016), while in the international standard (and in the en locale too), Sunday is the first day of week, then belonging to 2017.