Java Date Calculation

Question

I have a database field where date is stored as DD/MM/YYYY format. Now what I want to do is to see if today date = stored date - 1. Basically if today is the day before the end date. Based on that I will do some action...

example of format:

10/02/2012

There is formatting issue here because am using this:

Date todayDate;

And the stored date is VARCHAR2 in DB. Am assuming I would have to convert the retrieved date to Date regular format, or something like that. Help would be appreciated.

Update:

                currentEndDate =  rset.getString("ENDDATE");
                endDate = (Date)formatter.parse(currentEndDate);
                /*Check if end date = today date - 1*/
                Calendar cal = Calendar.getInstance();
                //set to end date
                cal.setTime(endDate);
                //end date - 1
                cal.add(Calendar.DATE, -1); 
                Date today;

Now this is what I have..I am trying here to get the end date -1 so I did that using add() function. Now How can I check if end-date - 1 == today date or not? because when I tried :

if(today.equals(cal.add(Calendar.date, -1)))

it said void return from the add function. Thanks,

Answer 1

You need to turn the String into a date. You can do this in your query or after you get the string result:

Option 1: turn it into a Date type in your query:

SELECT TO_DATE("COLNAME", 'MM/DD/YYYY') FROM "MYTABLE"

Option 2: turn it into a Date type after you get the string:

String date_from_db;
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
Date date = sdf.parse(date_from_db);

Then you can see if the current time (new Date()) is between the retrieved date and a day before that. If you want to handle all corner cases including leap seconds, you'll have to use a Calendar, but if you're okay with leaving out leap seconds then it'll be fine to just subtract 24*60*60*1000 milliseconds from your db date.

You can't directly compare a Date and a Calendar as per the API documentation:

...objects are equal if and only if the getTime method returns the same long value for both

Also, Calendar's equals() won't work:

The result is true if and only if the argument is a Calendar object of the same calendar system that represents the same time value

Simple solution: use the Calendar's getTime() method, which returns a Date object which will readily compare with other dates using equals(), after(), and before().

Or in code:

cal.add(Calendar.DAY, -1);
if(cal.getTime().equals(new Date())) { ... }

You could also instantiate another Calendar and use its setTime(), but that seems less efficient.

Answer 2

There are three JDBC types relating to time:

1. The JDBC DATE type represents a date consisting of day, month, and year. The corresponding SQL DATE type is defined in SQL-92, but it is implemented by only a subset of the major databases. Some databases offer alternative SQL types that support similar semantics.
2. The JDBC TIME type represents a time consisting of hours, minutes, and seconds. The corresponding SQL TIME type is defined in SQL-92, but it is implemented by only a subset of the major databases. As with DATE, some databases offer alternative SQL types that support similar semantics.
3. The JDBC TIMESTAMP type represents DATE plus TIME plus a nanosecond field. The corresponding SQL TIMESTAMP type is defined in SQL-92, but it is implemented by only a very small number of databases.

Because the standard Java class java.util.Date does not match any of these three JDBC date/time types exactly (it includes both DATE and TIME information but has no nanoseconds), JDBC defines three subclasses of java.util.Date to correspond to the SQL types. They are:

1. java.sql.Date for SQL DATE information. The hour, minute, second, and millisecond fields of the java.util.Date base class should be set to zero. If the number of milliseconds supplied to the java.sql.Date constructor is negative, the driver will compute the date as the number of milliseconds before January 1, 1970. Otherwise, the date is computed as the specified number of milliseconds after January 1, 1970.
2. java.sql.Time for SQL TIME information. The year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1. This is the "zero" date in the Java epoch.
3. java.sql.Timestamp for SQL TIMESTAMP information. This class extends java.util.Date by adding a nanoseconds field.

All three of the JDBC time-related classes are subclasses of java.util.Date, and as such, they can be used where a java.util.Date is expected. For example, internationalization methods take a java.util.Date object as an argument, so they can be passed instances of any of the JDBC time-related classes.

A JDBC Timestamp object has its parent's date and time components and also a separate nanoseconds component. If a java.sql.Timestamp object is used where a java.util.Date object is expected, the nanoseconds component is lost.

However, since a java.util.Date object is stored with a precision of one millisecond, it is possible to maintain this degree of precision when converting a java.sql.Timestamp object to a java.util.Date object. This is done by converting the nanoseconds in the nanoseconds component to whole milliseconds (by dividing the number of nanoseconds by 1,000,000) and then adding the result to the java.util.Date object. Up to 999,999 nanoseconds may be lost in this conversion, but the resulting java.util.Date object will be accurate to within one millisecond.

The following code fragment is an example of converting a java.sql.Timestamp object to a java.util.Date object that is accurate to within one millisecond.

Timestamp t = new Timestamp(98724573287540L);
java.util.Date d;
d = new java.util.Date(t.getTime() + (t.getNanos() / 1000000));

Answer 3

You can directly use Date type in your DB and you can have this directly as Date instance in java