Hi Thomas,
> I just saw the behavior of MySQL TIMESTAMPDIFF does not match H2. I
> created a new issue:
> http://code.google.com/p/h2database/issues/detail?id=209
> Patches are welcome!
>
I haven't had time to patch the datediff function, but I coded a
function that at least solves my need to calculate the elapsed time
between two dates in term of years, months and days. It's small and
attached.
The function "intervalBetweenDates" it's far to be optimized and don't
take time fields into account for calculations but it's an start point
that do their job.
Until "Interval" data type be defined I'm not sure what would be the
best way to use the result of this function from SQL, whether as an
array of integers or as an string of concatenated values like date
subtraction operator makes in DB2 ("YYYYMMDD") or unique integer made
in the same form.
If you think this can be useful I welcome your comments about how to
integrate it with existing functions.
Regards,
Dario.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
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.
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
/**
* Date Utils
*
* @author Dario V. Fassi
*/
public class DateDiffUtil {
public static final int YEARS=0;
public static final int MONTHS=1;
public static final int DAYS=2;
private static final int Feb24DayOfYear = 55; // http://en.wikipedia.org/wiki/February_24
private static final int Feb29DayOfYear = 60;
private static final int DaysInLeapYear = 366;
private static final int DaysInNormalYear = 365;
/**
* Interval Between Dates : as an int[3] with years,months,days interval (Only date fields)
*
* @param from
* @param to
* @return
*/
public static int[] intervalBetweenDates(Calendar from, Calendar to) {
if (from == null || to == null) {
return null;
}
int[] dint = new int[3];
if (to.get(Calendar.YEAR) >= from.get(Calendar.YEAR)
&& to.get(Calendar.MONTH) >= from.get(Calendar.MONTH)
&& to.get(Calendar.DAY_OF_MONTH) >= from.get(Calendar.DAY_OF_MONTH)) {
dint[0] = to.get(Calendar.YEAR) - from.get(Calendar.YEAR);
dint[1] = to.get(Calendar.MONTH) - from.get(Calendar.MONTH);
dint[2] = to.get(Calendar.DAY_OF_MONTH) - from.get(Calendar.DAY_OF_MONTH);
return dint;
}
GregorianCalendar calc = (GregorianCalendar) from.clone();
// Year move forward
if (to.get(Calendar.YEAR) > from.get(Calendar.YEAR)) {
dint[0] = to.get(Calendar.YEAR) - from.get(Calendar.YEAR) + 1;
calc.add(Calendar.YEAR, dint[0]);
while (calc.after(to)) {
dint[0]--;
calc.add(Calendar.YEAR, -1);
}
}
// Move Month past target because singularity dates
while (calc.compareTo(to) <= 0) {
dint[1]++;
calc.add(Calendar.MONTH, 1);
}
while (calc.after(to)) {
dint[1]--;
calc.add(Calendar.MONTH, -1);
}
// Move Day past target because singularity dates
while (calc.compareTo(to) <= 0) {
dint[2]++;
calc.add(Calendar.DAY_OF_MONTH, 1);
}
while (calc.after(to)) {
dint[2]--;
calc.add(Calendar.DAY_OF_MONTH, -1);
}
return dint;
}
/**
* Date in leap year ?
*
* @param c
* @return
*/
public static boolean isLeap(Calendar c) {
return isLeap(c.get(Calendar.YEAR));
}
/**
* Leap year
*
* @param year
* @return
*/
public static boolean isLeap(int year) {
return ((year % 400) == 0) || ((year % 100) > 0) && ((year % 4) == 0);
}
/**
* Days between dates
*
* //assert: from must be before to
*
* @param from
* @param to
* @return
*/
public static long daysBetween(Calendar from, Calendar to) {
Calendar date = (Calendar) from.clone();
long daysBetween = 0;
while (date.before(to)) {
date.add(Calendar.DAY_OF_MONTH, 1);
daysBetween++;
}
return daysBetween;
}
/**
* Years between dates
*
* @param from
* @param to
* @return
*/
public static double yearsBetween(Calendar from, Calendar to) {
int doy0 = from.get(Calendar.DAY_OF_YEAR);
int days0 = (isLeap(from) ? DaysInLeapYear : DaysInNormalYear) - doy0 + 1;
int doy1 = to.get(Calendar.DAY_OF_YEAR);
int n = DaysInNormalYear;
if (isLeap(from) && isLeap(to)) {
n = DaysInLeapYear;
}
if (isLeap(from) && (doy0 <= Feb29DayOfYear)) {
n = DaysInLeapYear;
}
if (isLeap(to) && (doy1 >= Feb24DayOfYear)) {
n = DaysInLeapYear;
}
double years = (doy1 + days0) / (double) n + (to.get(Calendar.YEAR) - from.get(Calendar.YEAR) - 1);
return years;
}
/**
* Format dates ISO
*
* @param d
* @return
*/
public static String fmtDate(Calendar d) {
return (new SimpleDateFormat("yyyy-MM-dd")).format(d.getTime()).trim();
}
/**
* Main test
*
* @param args
*/
public static void main(String args[]) {
Calendar from = Calendar.getInstance();
from.set(Calendar.YEAR, 2008);
from.set(Calendar.MONTH, 5);
from.set(Calendar.DAY_OF_MONTH, 11);
Calendar to1 = Calendar.getInstance();
to1.set(Calendar.YEAR, 2010);
to1.set(Calendar.MONTH, 5);
to1.set(Calendar.DAY_OF_MONTH, 10);
Calendar to2 = Calendar.getInstance();
to2.set(Calendar.YEAR, 2010);
to2.set(Calendar.MONTH, 5);
to2.set(Calendar.DAY_OF_MONTH, 11);
Calendar to3 = Calendar.getInstance();
to3.set(Calendar.YEAR, 2010);
to3.set(Calendar.MONTH, 5);
to3.set(Calendar.DAY_OF_MONTH, 12);
String sFrom = fmtDate(from);
int[] d1 = intervalBetweenDates(from, to1);
System.err.println(" " + sFrom + " - " + fmtDate(to1) + " = " + d1[0] + " years " + d1[1] + " months " + d1[2] + " days.");
int[] d2 = intervalBetweenDates(from, to2);
System.err.println(" " + sFrom + " - " + fmtDate(to2) + " = " + d2[0] + " years " + d2[1] + " months " + d2[2] + " days.");
int[] d3 = intervalBetweenDates(from, to3);
System.err.println(" " + sFrom + " - " + fmtDate(to3) + " = " + d3[0] + " years " + d3[1] + " months " + d3[2] + " days.");
// //////////////////////////////////////
from.set(Calendar.YEAR, 2010);
from.set(Calendar.MONTH, 7);
from.set(Calendar.DAY_OF_MONTH, 11);
to1.set(Calendar.YEAR, 2010);
to1.set(Calendar.MONTH, 7);
to1.set(Calendar.DAY_OF_MONTH, 10);
to2.set(Calendar.YEAR, 2010);
to2.set(Calendar.MONTH, 7);
to2.set(Calendar.DAY_OF_MONTH, 11);
to3.set(Calendar.YEAR, 2010);
to3.set(Calendar.MONTH, 7);
to3.set(Calendar.DAY_OF_MONTH, 12);
sFrom = fmtDate(from);
d1 = intervalBetweenDates(from, to1);
System.err.println(" " + sFrom + " - " + fmtDate(to1) + " = " + d1[0] + " years " + d1[1] + " months " + d1[2] + " days.");
d2 = intervalBetweenDates(from, to2);
System.err.println(" " + sFrom + " - " + fmtDate(to2) + " = " + d2[0] + " years " + d2[1] + " months " + d2[2] + " days.");
d3 = intervalBetweenDates(from, to3);
System.err.println(" " + sFrom + " - " + fmtDate(to3) + " = " + d3[0] + " years " + d3[1] + " months " + d3[2] + " days.");
// //////////////////////////////////////
from.set(Calendar.YEAR, 2007);
from.set(Calendar.MONTH, 1);
from.set(Calendar.DAY_OF_MONTH, 28);
to1.set(Calendar.YEAR, 2008);
to1.set(Calendar.MONTH, 1);
to1.set(Calendar.DAY_OF_MONTH, 28);
to2.set(Calendar.YEAR, 2008);
to2.set(Calendar.MONTH, 1);
to2.set(Calendar.DAY_OF_MONTH, 29);
to3.set(Calendar.YEAR, 2008);
to3.set(Calendar.MONTH, 2);
to3.set(Calendar.DAY_OF_MONTH, 1);
sFrom = fmtDate(from);
d1 = intervalBetweenDates(from, to1);
System.err.println(" " + sFrom + " - " + fmtDate(to1) + " = " + d1[0] + " years " + d1[1] + " months " + d1[2] + " days.");
d2 = intervalBetweenDates(from, to2);
System.err.println(" " + sFrom + " - " + fmtDate(to2) + " = " + d2[0] + " years " + d2[1] + " months " + d2[2] + " days.");
d3 = intervalBetweenDates(from, to3);
System.err.println(" " + sFrom + " - " + fmtDate(to3) + " = " + d3[0] + " years " + d3[1] + " months " + d3[2] + " days.");
// //////////////////////////////////////
from.set(Calendar.YEAR, 2008);
from.set(Calendar.MONTH, 1);
from.set(Calendar.DAY_OF_MONTH, 28);
to1.set(Calendar.YEAR, 2009);
to1.set(Calendar.MONTH, 1);
to1.set(Calendar.DAY_OF_MONTH, 28);
to2.set(Calendar.YEAR, 2009);
to2.set(Calendar.MONTH, 1);
to2.set(Calendar.DAY_OF_MONTH, 29);
to3.set(Calendar.YEAR, 2009);
to3.set(Calendar.MONTH, 2);
to3.set(Calendar.DAY_OF_MONTH, 1);
sFrom = fmtDate(from);
d1 = intervalBetweenDates(from, to1);
System.err.println(" " + sFrom + " - " + fmtDate(to1) + " = " + d1[0] + " years " + d1[1] + " months " + d1[2] + " days.");
d2 = intervalBetweenDates(from, to2);
System.err.println(" " + sFrom + " - " + fmtDate(to2) + " = " + d2[0] + " years " + d2[1] + " months " + d2[2] + " days.");
d3 = intervalBetweenDates(from, to3);
System.err.println(" " + sFrom + " - " + fmtDate(to3) + " = " + d3[0] + " years " + d3[1] + " months " + d3[2] + " days.");
// //////////////////////////////////////
from.set(Calendar.YEAR, 2008);
from.set(Calendar.MONTH, 1);
from.set(Calendar.DAY_OF_MONTH, 28);
to1.set(Calendar.YEAR, 2012);
to1.set(Calendar.MONTH, 1);
to1.set(Calendar.DAY_OF_MONTH, 28);
to2.set(Calendar.YEAR, 2008);
to2.set(Calendar.MONTH, 1);
to2.set(Calendar.DAY_OF_MONTH, 29);
to3.set(Calendar.YEAR, 2008);
to3.set(Calendar.MONTH, 2);
to3.set(Calendar.DAY_OF_MONTH, 1);
sFrom = fmtDate(from);
d1 = intervalBetweenDates(from, to1);
System.err.println(" " + sFrom + " - " + fmtDate(to1) + " = " + d1[0] + " years " + d1[1] + " months " + d1[2] + " days.");
d2 = intervalBetweenDates(from, to2);
System.err.println(" " + sFrom + " - " + fmtDate(to2) + " = " + d2[0] + " years " + d2[1] + " months " + d2[2] + " days.");
d3 = intervalBetweenDates(from, to3);
System.err.println(" " + sFrom + " - " + fmtDate(to3) + " = " + d3[0] + " years " + d3[1] + " months " + d3[2] + " days.");
// //////////////////////////////////////
from.set(Calendar.YEAR, 2008);
from.set(Calendar.MONTH, 7);
from.set(Calendar.DAY_OF_MONTH, 1);
to1.set(Calendar.YEAR, 2008);
to1.set(Calendar.MONTH, 7);
to1.set(Calendar.DAY_OF_MONTH, 30);
to2.set(Calendar.YEAR, 2008);
to2.set(Calendar.MONTH, 8);
to2.set(Calendar.DAY_OF_MONTH, 31);
to3.set(Calendar.YEAR, 2008);
to3.set(Calendar.MONTH, 8);
to3.set(Calendar.DAY_OF_MONTH, 1);
sFrom = fmtDate(from);
d1 = intervalBetweenDates(from, to1);
System.err.println(" " + sFrom + " - " + fmtDate(to1) + " = " + d1[0] + " years " + d1[1] + " months " + d1[2] + " days.");
d2 = intervalBetweenDates(from, to2);
System.err.println(" " + sFrom + " - " + fmtDate(to2) + " = " + d2[0] + " years " + d2[1] + " months " + d2[2] + " days.");
d3 = intervalBetweenDates(from, to3);
System.err.println(" " + sFrom + " - " + fmtDate(to3) + " = " + d3[0] + " years " + d3[1] + " months " + d3[2] + " days.");
}
}
