001: /*
002: * $Id: DateDiffFunction.java,v 1.16 2005/05/02 22:28:41 ahimanikya Exp $
003: * =======================================================================
004: * Copyright (c) 2002-2005 Axion Development Team. All rights reserved.
005: *
006: * Redistribution and use in source and binary forms, with or without
007: * modification, are permitted provided that the following conditions
008: * are met:
009: *
010: * 1. Redistributions of source code must retain the above
011: * copyright notice, this list of conditions and the following
012: * disclaimer.
013: *
014: * 2. Redistributions in binary form must reproduce the above copyright
015: * notice, this list of conditions and the following disclaimer in
016: * the documentation and/or other materials provided with the
017: * distribution.
018: *
019: * 3. The names "Tigris", "Axion", nor the names of its contributors may
020: * not be used to endorse or promote products derived from this
021: * software without specific prior written permission.
022: *
023: * 4. Products derived from this software may not be called "Axion", nor
024: * may "Tigris" or "Axion" appear in their names without specific prior
025: * written permission.
026: *
027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
028: * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
029: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
030: * PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
031: * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
032: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
033: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
034: * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
035: * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
036: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
037: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
038: * =======================================================================
039: */
040:
041: package org.axiondb.functions;
042:
043: import java.sql.Timestamp;
044: import java.util.Calendar;
045:
046: import org.axiondb.AxionException;
047: import org.axiondb.DataType;
048: import org.axiondb.FunctionFactory;
049: import org.axiondb.RowDecorator;
050: import org.axiondb.types.IntegerType;
051: import org.axiondb.types.StringType;
052: import org.axiondb.types.TimestampType;
053: import org.axiondb.util.DateTimeUtils;
054:
055: /**
056: * Syntax: DATEDIFF(interval_type, timestamp1, timestamp2)
057: *
058: * @version $Revision: 1.16 $ $Date: 2005/05/02 22:28:41 $
059: * @author Rupesh Ramachandran
060: * @author Ritesh Adval
061: */
062: public class DateDiffFunction extends BaseFunction implements
063: ScalarFunction, FunctionFactory {
064:
065: /** Creates a new instance of Class */
066: public DateDiffFunction() {
067: super ("DATEDIFF");
068: }
069:
070: public ConcreteFunction makeNewInstance() {
071: return new DateDiffFunction();
072: }
073:
074: /** {@link DataType} */
075: public DataType getDataType() {
076: return RETURN_TYPE;
077: }
078:
079: /**
080: * Returns integer difference (timestamp2 - timestamp1) in units of interval of type
081: * interval_type. Valid interval types are {day, month, year, second, millisecond,
082: * minute, hour, week, quarter}
083: */
084: public Object evaluate(RowDecorator row) throws AxionException {
085: // Get 'interval_type'
086: int intervalType = DateTimeUtils
087: .labelToCode((String) (STRING_TYPE.convert(getArgument(
088: 0).evaluate(row))));
089:
090: // Get 'timestamp1'
091: Timestamp timestamp1 = null;
092:
093: Object val1 = getArgument(1).evaluate(row);
094: timestamp1 = (Timestamp) TIMESTAMP_TYPE.convert(val1);
095:
096: // Get 'timestamp2'
097: Timestamp timestamp2 = null;
098: Object val2 = getArgument(2).evaluate(row);
099: timestamp2 = (Timestamp) TIMESTAMP_TYPE.convert(val2);
100:
101: // Return null if either or both parameters are null.
102: if (timestamp1 == null || timestamp2 == null) {
103: return null;
104: }
105:
106: // Calculate the difference between dates in unit specfied by interval type
107: return new Long(calculateDateDiff(intervalType, timestamp1,
108: timestamp2));
109: }
110:
111: /*
112: * Calculate date difference between two timestamp values timestamp t2 is subtract
113: * from timestamp t1 @param intervalType interval type such as DAY, MONTH etc @param
114: * t1 first time stamp @param t2 second time stamp @return the difference (t2-t1) of
115: * in unit of intervalType specified
116: */
117: long calculateDateDiff(int intervalType, Timestamp t1, Timestamp t2)
118: throws AxionException {
119: Calendar c1 = null;
120: Calendar c2 = null;
121:
122: if (intervalType == DateTimeUtils.DAY
123: || intervalType == DateTimeUtils.WEEK
124: || intervalType == DateTimeUtils.MONTH
125: || intervalType == DateTimeUtils.QUARTER
126: || intervalType == DateTimeUtils.YEAR) {
127:
128: c1 = Calendar.getInstance(TimestampType.getTimeZone());
129: c2 = Calendar.getInstance(TimestampType.getTimeZone());
130:
131: //set start date
132: c1.setTimeInMillis(t1.getTime());
133: //set end date
134: c2.setTimeInMillis(t2.getTime());
135: }
136:
137: switch (intervalType) {
138: default:
139: case DateTimeUtils.MILLISECOND:
140: return getMilisecondsBetween(t2, t1);
141: case DateTimeUtils.SECOND:
142: return getSecondsBetween(t2, t1);
143: case DateTimeUtils.MINUTE:
144: return getMinutesBetween(t2, t1);
145: case DateTimeUtils.HOUR:
146: return getHoursBetween(t2, t1);
147: case DateTimeUtils.DAY:
148: return getDaysBetween(t2, t1);
149: case DateTimeUtils.WEEK:
150: return getWeeksBetween(c2, c1);
151: case DateTimeUtils.MONTH:
152: return getMonthsBetween(c2, c1);
153: case DateTimeUtils.QUARTER:
154: return getQuartersBetween(c2, c1);
155: case DateTimeUtils.YEAR:
156: return getYearsBetween(c2, c1);
157: }
158: }
159:
160: /**
161: * Calculates the number of miliseconds between two calendar days in a manner which is
162: * independent of the Calendar type used. i.e. (d2-d1)
163: *
164: * @param d1 The first date.
165: * @param d2 The second date.
166: * @return The number of miliseconds between the two dates. Zero is returned if the
167: * dates are the same, etc. The order of the dates does matter. If Calendar
168: * types of d1 and d2 are different, the result may not be accurate.
169: */
170: private long getYearsBetween(Calendar d1, Calendar d2) {
171: boolean negativeResult = false;
172: if (d1.after(d2)) { // swap dates so that d1 is start and d2 is end
173: java.util.Calendar swap = d1;
174: d1 = d2;
175: d2 = swap;
176: } else {
177: negativeResult = true;
178: }
179:
180: long years = d2.get(Calendar.YEAR) - d1.get(Calendar.YEAR);
181:
182: if (negativeResult) {
183: years *= -1;
184: }
185:
186: return years;
187: }
188:
189: /**
190: * Calculates the number of quarters between two calendar days in a manner which is
191: * independent of the Calendar type used. i.e. (d2-d1)
192: *
193: * @param d1 The first date.
194: * @param d2 The second date.
195: * @return The number of quarters between the two dates. Zero is returned if the dates
196: * are the same, etc. The order of the dates does matter. If Calendar types of
197: * d1 and d2 are different, the result may not be accurate.
198: */
199: private long getQuartersBetween(Calendar d1, Calendar d2) {
200: long quarters = getMonthsBetween(d1, d2) / 3;
201: return quarters;
202: }
203:
204: /**
205: * Calculates the number of months between two calendar days in a manner which is
206: * independent of the Calendar type used. i.e. (d2-d1)
207: *
208: * @param d1 The first date.
209: * @param d2 The second date.
210: * @return The number of months between the two dates. Zero is returned if the dates
211: * are the same, one if the months are adjacent, etc. The order of the dates
212: * does matter. If Calendar types of d1 and d2 are different, the result may
213: * not be accurate.
214: */
215: private long getMonthsBetween(Calendar d1, Calendar d2) {
216: boolean negativeResult = false;
217: if (d1.after(d2)) { // swap dates so that d1 is start and d2 is end
218: java.util.Calendar swap = d1;
219: d1 = d2;
220: d2 = swap;
221: } else {
222: negativeResult = true;
223: }
224:
225: long months = d2.get(Calendar.MONTH) - d1.get(Calendar.MONTH);
226: int y2 = d2.get(Calendar.YEAR);
227: if (d1.get(Calendar.YEAR) != y2) {
228: d1 = (Calendar) d1.clone();
229: do {
230: months += 12;
231: d1.add(Calendar.YEAR, 1);
232: } while (d1.get(Calendar.YEAR) != y2);
233: }
234:
235: if (negativeResult) {
236: months *= -1;
237: }
238: return months;
239: }
240:
241: /**
242: * Calculates the number of weeks between two calendar days in a manner which is
243: * independent of the Calendar type used. i.e. (d2-d1)
244: *
245: * @param d1 The first date.
246: * @param d2 The second date.
247: * @return The number of weeks between the two dates. Zero is returned if the dates
248: * are the same, etc. The order of the dates does matter. If Calendar types of
249: * d1 and d2 are different, the result may not be accurate.
250: */
251: private long getWeeksBetween(java.util.Calendar d1,
252: java.util.Calendar d2) {
253: boolean negativeResult = false;
254:
255: if (d1.after(d2)) { // swap dates so that d1 is start and d2 is end
256: java.util.Calendar swap = d1;
257: d1 = d2;
258: d2 = swap;
259: } else {
260: negativeResult = true;
261: }
262:
263: long days = d2.get(Calendar.WEEK_OF_YEAR)
264: - d1.get(Calendar.WEEK_OF_YEAR);
265: int y2 = d2.get(Calendar.YEAR);
266: if (d1.get(Calendar.YEAR) != y2) {
267: d1 = (Calendar) d1.clone();
268: do {
269: days += d1.getActualMaximum(Calendar.WEEK_OF_YEAR);
270: d1.add(Calendar.YEAR, 1);
271: } while (d1.get(Calendar.YEAR) != y2);
272: }
273: if (negativeResult) {
274: days *= -1;
275: }
276:
277: return days;
278: }
279:
280: /**
281: * Calculates the number of days between two calendar days in a manner which is
282: * independent of the Calendar type used. i.e. (d2-d1)
283: *
284: * @param d1 The first date.
285: * @param d2 The second date.
286: * @return The number of days between the two dates. Zero is returned if the dates are
287: * the same, one if the dates are adjacent, etc. The order of the dates does
288: * matter. If Calendar types of d1 and d2 are different, the result may not be
289: * accurate.
290: */
291: private long getDaysBetween(Timestamp t1, Timestamp t2) {
292: long days = (t1.getTime() - t2.getTime()) / DAYS_IN_MILLISECOND;
293: return days;
294: }
295:
296: /**
297: * Calculates the number of hours between two calendar days in a manner which is
298: * independent of the Calendar type used. i.e. (d2-d1)
299: *
300: * @param d1 The first date.
301: * @param d2 The second date.
302: * @return The number of hours between the two dates. Zero is returned if the dates
303: * are the same, etc. The order of the dates does matter. If Calendar types of
304: * d1 and d2 are different, the result may not be accurate.
305: */
306: private long getHoursBetween(Timestamp t1, Timestamp t2) {
307: long hours = (t1.getTime() - t2.getTime())
308: / HOURS_IN_MILLISECOND;
309: return hours;
310: }
311:
312: /**
313: * Calculates the number of minutes between two calendar days in a manner which is
314: * independent of the Calendar type used. i.e. (d2-d1)
315: *
316: * @param d1 The first date.
317: * @param d2 The second date.
318: * @return The number of minutes between the two dates. Zero is returned if the dates
319: * are the same, etc. The order of the dates does matter. If Calendar types of
320: * d1 and d2 are different, the result may not be accurate.
321: */
322: private long getMinutesBetween(Timestamp t1, Timestamp t2) {
323: long seconds = (t1.getTime() - t2.getTime())
324: / MINUTES_IN_MILLISECOND;
325: return seconds;
326: }
327:
328: /**
329: * Calculates the number of seconds between two calendar days in a manner which is
330: * independent of the Calendar type used. i.e. (d2-d1)
331: *
332: * @param d1 The first date.
333: * @param d2 The second date.
334: * @return The number of seconds between the two dates. Zero is returned if the dates
335: * are the same, etc. The order of the dates does not matter. If Calendar
336: * types of d1 and d2 are different, the result may not be accurate.
337: */
338: private long getSecondsBetween(Timestamp t1, Timestamp t2) {
339: long seconds = (t1.getTime() - t2.getTime())
340: / SECONDS_IN_MILLISECOND;
341: return seconds;
342: }
343:
344: /**
345: * Calculates the number of miliseconds between two calendar days in a manner which is
346: * independent of the Calendar type used. i.e. (d2-d1)
347: *
348: * @param d1 The first date.
349: * @param d2 The second date.
350: * @return The number of miliseconds between the two dates. Zero is returned if the
351: * dates are the same, etc. The order of the dates does matter. If Calendar
352: * types of d1 and d2 are different, the result may not be accurate.
353: */
354: private long getMilisecondsBetween(Timestamp t1, Timestamp t2) {
355: long milliseconds = t1.getTime() - t2.getTime();
356: return milliseconds;
357: }
358:
359: public boolean isValid() {
360: return getArgumentCount() == 3;
361: }
362:
363: private static final DataType RETURN_TYPE = new IntegerType();
364: private static final DataType STRING_TYPE = new StringType();
365: private static final DataType TIMESTAMP_TYPE = new TimestampType();
366:
367: private static final int SECONDS_IN_MILLISECOND = 1000;
368: private static final int MINUTES_IN_MILLISECOND = 60 * SECONDS_IN_MILLISECOND;
369: private static final int HOURS_IN_MILLISECOND = 60 * MINUTES_IN_MILLISECOND;
370: private static final int DAYS_IN_MILLISECOND = 24 * HOURS_IN_MILLISECOND;
371:
372: }
|