Hi,
We are using H2 Database in our application, but based on requirement of
some users PostgreSQL is used. And we use JPA to be compatible with both
the databases, but some of the reports use native sql queries. One of the
queries that uses EXTRACT(WEEK FROM dateColumn) is not compatible with H2,
as it supports WK and WW which are not supported in Postgresql.
We've made the following change to Function.java to add this option and the
attached patch also updates help.csv files. I hope this can be integrated
into H2 codebase.
Thanks & Best Regards,
Prashant
Index: src/main/org/h2/expression/Function.java
===================================================================
--- src/main/org/h2/expression/Function.java (revision 4381)
+++ src/main/org/h2/expression/Function.java (working copy)
@@ -129,6 +129,7 @@
DATE_PART.put("SQL_TSI_WEEK", Calendar.WEEK_OF_YEAR);
DATE_PART.put("WW", Calendar.WEEK_OF_YEAR);
DATE_PART.put("WK", Calendar.WEEK_OF_YEAR);
+ DATE_PART.put("WEEK", Calendar.WEEK_OF_YEAR);
DATE_PART.put("DAY", Calendar.DAY_OF_MONTH);
DATE_PART.put("DD", Calendar.DAY_OF_MONTH);
DATE_PART.put("D", Calendar.DAY_OF_MONTH);
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/ryzFLKw46TQJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Index: src/main/org/h2/res/help.csv
===================================================================
--- src/main/org/h2/res/help.csv (revision 4381)
+++ src/main/org/h2/res/help.csv (working copy)
@@ -1181,7 +1181,7 @@
Returns the day of the year (1-366)."
"Functions (Time and Date)","EXTRACT","
EXTRACT ( { YEAR | YY | MONTH | MM | DAY | DD | DAY_OF_YEAR
- | DOY | HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS }
+ | DOY | HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS | WEEK }
FROM timestamp )
","
Returns a specific value from a timestamps."
Index: src/main/org/h2/expression/Function.java
===================================================================
--- src/main/org/h2/expression/Function.java (revision 4381)
+++ src/main/org/h2/expression/Function.java (working copy)
@@ -129,6 +129,7 @@
DATE_PART.put("SQL_TSI_WEEK", Calendar.WEEK_OF_YEAR);
DATE_PART.put("WW", Calendar.WEEK_OF_YEAR);
DATE_PART.put("WK", Calendar.WEEK_OF_YEAR);
+ DATE_PART.put("WEEK", Calendar.WEEK_OF_YEAR);
DATE_PART.put("DAY", Calendar.DAY_OF_MONTH);
DATE_PART.put("DD", Calendar.DAY_OF_MONTH);
DATE_PART.put("D", Calendar.DAY_OF_MONTH);
Index: src/docsrc/help/help.csv
===================================================================
--- src/docsrc/help/help.csv (revision 4381)
+++ src/docsrc/help/help.csv (working copy)
@@ -3268,7 +3268,7 @@
"Functions (Time and Date)","EXTRACT","
EXTRACT ( { YEAR | YY | MONTH | MM | DAY | DD | DAY_OF_YEAR
- | DOY | HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS }
+ | DOY | HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS | WEEK }
FROM timestamp )
","
Returns a specific value from a timestamps.
