Comparison of diff types of dates

[noude]

Hello,

I'm a newbie in Ireports and I don't know how to convert a string in a date format, in a report I have two colums: DATE1 and DATE2

I have to compare them and have the difference of time between them.

DATE1 == 2009-05-03

DATE2 == 2010-06-12T20:17:35+02:00

But DATE1 and DATE2 or in a string format ! :-/ How can I do this please ?

 

Thanks for help !

Post Edited by noude at 09/29/2010 09:52

[noude]

Up please =(

[itpragmatic]

Hi there. The simplest ways is, go to DATE2 and DATE1 textfields. Under text field properties click on pattern and select your desirable format, in your case it must be yyyy/mm/dd HH:mm.ss before that set your expression class of your both date(date1 and date2) text fields to java.util.date and then update your sql query using following.
select,
------
TIME_FORMAT(TIMEDIFF(date2,date1),'%H:%i:%S')as execute_time
from ------------
and see whether it works. This is what I can suggest after reading your problem as i am not sure in what exactly fromat you want to display your result. All the best.

Post Edited by itpragmatic at 09/29/2010 13:42

[noude]

Oooooh thanks for you answer !

 

But, it's not a SQL request, it's like a .csv file with colums, so I have columns DATE1 and DATE2.

 

And they are in a string format, and I want them in a date format.

I selected field pattern as you said but it's don't work sorry !

 

I really don't know how to change them in a date format =( =( =(

 

[itpragmatic]

Ok so you must have been trying to execute your application within a java file to ireport, right? If this is true then you have to use SimpledateFormat in your program and also need to parse it. Don't forget to include import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.text.ParseException;

import java.util.Date;

in the program. Anyways I am still not sure how exactly you are working in ireport with your csv files (I mean to say you want to it within a java file or how?) I apologize if I am asking anything wrong but I didn't understand your problem thoroughly yet.

[noude]

Ahahah I don't really understand what you are saying sorry because I'm a trainee for now and I don't know Java language :-/

 

I will explain you what I am using.

It's a big soft who give to iReports a file (I don't know what is it but it seems like a csv file I think), after I just have to create my fields and hop !

[itpragmatic]

ok i got it in other way..sorry.

[cbarlow3]

So if I understand the problem correctly, we shouldn't focus on changing how the information GETS to iReport and try to convert from text to date there, because at this stage the source of the data is either outside your control or at best a bit of a mystery to you, so you're looking for a way to take those text inputs and do the conversions in iReport.  I think that one way you can do that is to create variables and make the variables have "Value Expressions" that are based on the original text fields.

For example, if I have a text field called $F{DATE1} that has a value of "2009-05-03", I could create a variable of java.util.date called $V{DATE1} (hope that's not too confusing) where the variable expression is:

new Date($F{DATE1}.substring(0,4)+"/"+$F{DATE1}.substring(5,7)+"/"+$F{DATE1}.substring(8))

(I think this expression works whether your report uses Groovy or JavaScript...sorry it's so kludgy, but Date.parse() doesn't accept the standard SQL format of "YYYY-MM-DD")

You can do something similar for the other date.  Now you have two java.util.Date type variables that you can output using whatever Pattern you want, plus you can do some date math with the two values.  Hope that helps out. 

 

Carl

[cbarlow3]

By the way, assuming that you want to throw away the time part on date 2 and only care about the date part, I defined variable $V{DATE2} like this:

new Date($F{DATE2}.substring(0,4)+"/"+$F{DATE2}.substring(5,7)+"/"+$F{DATE2}.substring(8,10))

(same format as my definition for $V{DATE1} except that I have to express an ending position for the final substring (which I could have done on $V{DATE1}, of course).

Then I defined a variable $V{DaysBetween} like this:

($V{DATE2}.getTime()-$V{DATE1}.getTime())/1000/60/60/24

This takes advantage of the fact that by this time both date variables should be midnight, so I don't have to worry about which one is earlier in the day and whether I need to round up or down.  I thought Groovy would allow me to do this:

$V{DATE2}.minus($V{DATE1})

but I couldn't get that to parse.

When I run my report (I actually defined two parameters instead of fields, since this is just a test and I don't have your input data), I got this:

Text: 2009-05-03                                = Date:  Sunday 03 May 2009

Text: 2010-06-12T20:17:35+02:00 = Date: Saturday 12 June 2010

Days Between:   405

[noude]

cbarlow3:

 

 

Thaaaaaaaanks !!! It works now ! =) Thank you veryyy much ! :)

 

Post Edited by noude at 10/01/2010 09:49

[cbarlow3]

Glad that worked out.  I got your question about displaying the difference between two dates as a number of years, months, and days instead of as just a number of days.  I have two potential issues with the problem:

1. As soon as you try to express durations in terms of months (and to a minor extent this is also true of years), the definition becomes ambiguous, because months aren't equal length.  For example, what answer do you want to get back when the Date1 is Jan 28, 2010 and Daet 2 is Feb 28, 2010?  1 mo, 0 days, right?  Okay, but what about Jan 28 to Mar 1?  1 mo and 1 day?  Sounds reasonable.  But then what do you get  back when you compare Jan 29, Jan 30, or Jan 31 to Mar 1?

2. My second issue is a practical one: if you wanted to write lines of code that took two date variables and imlemented your algorithm of choice for turning a duration into a series of year, month, and days, it wouldn't be very hard.  I don't know how to write my own functions that can be called from within iReport, however, so I guess I would start defining additional variables, like MM1 would be $V{Date1}.getMonth() (remember that it will return a number 0 to 11 for Jan to Dec...won't matter when you're just using it for math perhaps, but keep it in mind), DD1 would be $V{Date1}.getDate(), and  YYYY1 would be $V{Date1}.getAt(Calendar.YEAR).  Create variables MM2, DD2, and YYYY2 the same way, only based on $V{Date2}.  Now create variables called $V{YearsDuration} $V{MonthsDuration} and $V{DaysDuration}, which I'll attempt to calculate below.  My issue is that I keep creating all these variables that are dependent on each other, but I'm not specifying an order in which they need to be evaluated.  I'm hoping that iReport/JasperServer is able to figure out the dependencies (better not be circular!) and execute the evaluations in the order I intend.

Now I'll attempt to define an algorithm and get it to work.  I'll use the same functionality as Excel's DATEDIF function, where my three duration variables correspond to the Y, YM, and MD options respectively.  You'll still get strange results in the exceptional cases such as the ones I posed above (Jan 29, 30, and 31 to Mar 1 result in 1 month and 0 days, -1 days, and -2 days respectively!), but most of the time you'll get a sort of intuitive answer out of it.

Well, I ran into problems that I suspect are related to the dependency issue I mentioned.  I tried setting $V{YearsDuration} to the expression    (MM2>MM1 | MM2==MM1 & DD2>=DD1) ? YYYY2 - YYYY1 : YYYY2 - YYYY1 - 1  but it got an error.  Even when I switched it to the simpler (but not always accurate) expression    YYYY2-YYYY1, it got an error, even though I've displayed that YYYY2 is 2010 and YYYY1 is 2009 and they're both defined as the same numeric data type.  If I have time, I'll see if I can figure out how I would do it, but unless I can get iReport to evaluate all my variables in an order that makes sense, I don't see how to get further with this approach of using variables and scripted expressions.

 

[cbarlow3]

I got a reasonable facimile of Excel's DATEDIF function running in a spreadsheet:

Cell A1 (Date1) = 5/3/2009

Cell A2 (Date2) = 6/12/2010

Cells C2 through E2 I made be =DATEDIF(A2,B2,"Y"), =DATEDIF(A2,B2,"YM"), and =DATEDIF(A2,B2,"MD") just so I would know what results I should be shooting for (I even put in some conditional formatting later so I would know if there were any discrepancies between my results and the DATEDIF results (I made several more examples on subsequent rows))

Cell G2 (MM1) = MONTH(A2)

Cell H2 (DD1) = DAY(A2)

Cell I2 (YYYY1) = YEAR(A2)

Similarly, cells J2 through L2 are MM2, DD2, and YYYY2, based on MONTH, DAY, and YEAR functions of cell B2

Cell M2 (DurationYears) = IF(OR(J2>G2,AND(J2=G2,K2>=H2)),L2-I2,L2-I2-1) (resulting in 1 in your example)

Cell N2 (DurationMonths) = IF(OR(J2<G2,AND(J2=G2,K2<H2)),12,0)+J2-G2-IF(K2<H2,1,0)  (resulting in 1 in your example)

Cell O2 (DurationDays) = IF(K2>=H2,K2-H2,DATE(I2,J2,K2)-DATE(I2,J2-1,H2))

Converting all this Excel syntax to Groovy, JavaScript, or Java would of course be necessary.

As Bill Jelen wrote in his book "Excel 2010 In Depth", the anomolies for certain calculations are "simply the downside of trying to express a measurement in months, when the length of a month is not constant.  Negative values for the MD version of a DATEDIF will happen only when the end date is March 1 or March 2.  Despite this problem, for 363 days a year, DATEDIF remains an effective way to express a date delta as a certain number of years, months, and days."

Well, I'm proud of myself in one respect:  I hadn't read this when I came up with my example of the problem with date ranges ending in March 1.  I'm just something of a calendrical calculation buff, and have come across this oddity before of tyring to measure duration in terms of years and months instead of seconds, minutes, hours, or days (ignoring DST), which are consistent duration units.

I discovered a mostly undocumented Excel function, but I hope I've also disuaded you from trying to express your duration this way!  Especially if it will be used for a chart or for percentage comparisons, etc., DAYS is the way to go.

 

[noude]

Hello cbarlow3,

 

Yes you disuaded me ^^

Days are the way to go ! =)

 

I can ask you another quick question that nobody answers me?

 

 

I have a new problem, I've a .csv file with this column:

- Dates

- 2008/10

- 2009/05

- 2010/01

- 2010/10

 

I want to have the time (in months) between two dates (for example 2008/10 and 2009/05)

How can I do this please ?

I really need help...

it's been days since I'm on this problem ... :-/

[cbarlow3]

I think the problem is just a variant of the first one you asked.  Assuming the format is always YYYY/MM (I notice that you even had an example that shows that you have leading zeroes in the month, which is handy for parsing purposes), you have a few choices--depending on what those dates mean, you could read them in the same way as in my first response and add a '-01' to the end before converting into a java.lang.Date value.  Then you have two date values, both of which are the first of the month, and you already know how to get the number of days between two Date values.  Or, if you're trying to find the number of months between two months like this, isn't it just: 12*YYYY2+MM2-12*YYYY1-MM1 (assuming you know that YYYYMM2 is later than YYYYMM1, otherwise take abs())?

I defined two String variables $V{YYYYMM1} and $V{YYYYMM2} as "1962/09" and "2010/10" respectively and then defined a variable $V{Months} as:

12*$V{YYYYMM2}.substring(0,4).toInteger()+$V{YYYYMM2}.substring(5,7).toInteger()-12*$V{YYYYMM1}.substring(0,4).toInteger()-$V{YYYYMM1}.substring(5,7).toInteger()

I realize that's kind of a mouthful in one line, and I don't know the nuances of type conversion in Groovy vs. JavaScript, so depending on which scripting language you have set, you may be able to avoid some of the toInteger() calls.  I also couldn't get an abs() to work...I think it expects to work on a Double, not an Integer, but I figured this was easier to read, and you can adapt to your purposes.

 

Carl

[noude]

Thanks for your answer !

It is working very well =)

Thanks !!!