Working with Dates

>> I'd also like to know how to parse a String as a date
Use the parse() function of SimpleDateFormat

SimpleDateFormat will help you convert a string to a date:

SimpleDateFormat sdf = new SimpleDateFormat( "dd/MM/yyyy" ) ;
Date d = sdf.parse( "10/05/1974" ) ;

for example :-)

Date d = new SimpleDateFormat("yyyy/MM/dd").parse("1981/10/12");

for parsing the String to date use

DateFormat.parse(string);

Hi Tim!

...and sudhakar ;°)

For the first part fo your question...  you should be able to get the number of miliseconds since 1970 for each date in the database...  and these should be standard (I think)

Personally, I would store the dates in the database as a single timezone, and let the user choose how they want the dates displayed

Here is an example to parse the String to date

http://www.ideas2work.com/java-articles/Java-Query-DateFormat.html

Hello zzynx :-)

Good Morning. But here is already  evening 5:00 PM monday

Problem is in how the data is entered - when the timezones change, the database stores the dates in that timezone. Great in theory but a bit of a pita for poor old me :)


Not sure how to award points in this one lol - about 5 million people replied! :)

>> I would store the dates in the database as a single timezone, and let the user choose how they want the dates displayed
I agree

In Java, there is no time zone info in Date

>> when the timezones change, the database stores the dates in that timezone
That's not how it should be

Okay so when I get get the dates from the database they're all in timezones. Anyway to put them all in one timezone? Otherwise the calculations are going to be off :)

>> In Java, there is no time zone info in Date
Also correct. [ Hi, CEHJ ;°) ]
It comes when you represent that Date (e.g. in the Calendar object)

>>Anyway to put them all in one timezone?

That should happen automatically. They'll be displayed in the default timezone

>> so when I get get the dates from the database
... you have a Date. A unique point in time.
Up to you to decide in what TimeZone you want it to be "represented".

Arg  Date  = Sat Jul 31 00:00:00 BST 2004
Arg  Date  = Sat Nov 01 00:00:00 GMT 2003

Not sure why it's doing that then - the others aren't....


Let me go investigate again :)

>>Not sure why it's doing that then

I see what you mean. They're really the same timezone, but adjusted by the default Calendar for daylight saving time. Try setting an explicit TimeZone on the DateFormat you're using

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy zzz");
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));

Hmmm, it doesn't like parsing this format :


2004-10-01


for the 1st of October 2004.

I've updated the pattern in the SimpleDateFormat constructor....

And I know this particular date is in that format....

>>Hmmm, it doesn't like parsing this format :

Parsing? You should be formatting. The dates are not stored and being retrieved as string in the db are they?

>> The dates are not stored and being retrieved as string in the db are they?
Mmmm. I wouldn't bet on that...

Okay two seperate things here lol.


1) I have dates being pulled from the database as dates. They are also being stored as dates.
2) I also have to turn some strings into dates to compare against the dates taken from the database....


I really should have kept my mouth shut when I said that the current application sucked.....

>> 1) I have dates being pulled from the database as dates. They are also being stored as dates.
That looks OK.

>> 2) I also have to turn some strings into dates to compare against the dates taken from the database....
SimpleDateFormat.parse() is the way to go.

SimpleDateFormat craps itself :)

Here is the code I'm using - and I'm convinced I've screwed it up as usual lol


String startYear="2003-08-01";

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
         sdf.setTimeZone(TimeZone.getTimeZone("UTC"));

         startOfAcademicYear = sdf.parse(startYear);

So...


System.out.println(sdf.format(startOfAcademicYear));

(You need to use a DateFormat + set TimeZone for printing your Date too, or a default format + TimeZone will be used)

>> startOfAcademicYear = sdf.parse(startYear);

You must be aware of the fact that
        System.out.println( startOfAcademicYear ); // which in fact equals

        System.out.println( startOfAcademicYear.toString() );

is again "a representation" of the Date startOfAcademicYear which uses a certain (your system's default) time zone.

So, if you use

        System.out.println(sdf.format(startOfAcademicYear));

you shouldn't see any unwanted timezone stuff.

Interestingly if I do this  :


System.out.println("Test Date  = " + date1);
System.out.println("Arg  Date  = " + date2);

I get this output :

Test Date  = 2002-10-12
Arg  Date  = Fri Nov 01 00:00:00 GMT 2002


Can't figure out why lol

>>Can't figure out why lol

(See my last comment)

>>>>Can't figure out why lol
>>(See my last comment)
and my last too ;°)

The two different string formats are from do different implementations of date.  The short format is from java.sql.Date and the long format is from java.util.Date.  The java.sql.date enherits from java.util.Date, but ignores the time portion of the date.  It is created from the ResultSet.getDate() method.  If you want date AND time, then use the java.sql.Timestamp class (which is created from the ResultSet.getTime() method).

So if I use get timestamp instead of getDate(), I can convert that into a "normal" Date object.....

Let me go try that :)

>>I can convert that into a "normal" Date object.....

Yes

java.util.Date d = rs.getTimestamp(1).getTimestamp();
System.out.println(sdf.format(d));

Even after all that, if I look at the date overall, it's still showing as an hour out :


Arg  Date  = 31-Jul-02    12:00
Test Date  = 29-Apr-02    11:00
Arg  Date  = 31-Jul-02    12:00


They should all be set to midnight but as you can see, the second one is showing as 11:00

The problem I think I might get is when checking that the student was there on a given census date i.e 29-Apr-02, because the date from the database is an hour early, the student would be listed as not completed even though they really had.

What DateFormat have you used to produce that 'test date'?

SimpleDateFormat sdf2 = new SimpleDateFormat("dd-MMM-yy    hh:mm");

sdf2.setTimeZone(TimeZone.getTimeZone("UTC"));


I only used it to see whether or not it was still showing a different hour.

Yes, you'd be better off with the more precise

SimpleDateFormat sdf2 = new SimpleDateFormat("dd-MMM-yy    HH:mm");

All dates should be midnight :)


Test Date  = 11-Oct-02    23:00
Arg  Date  = 01-Nov-02    00:00
Test Date  = 10-Oct-02    23:00
Arg  Date  = 01-Nov-02    00:00
Test Date  = 21-Oct-02    23:00

You still have the same underlying problem.  The database does not know about time zones.   It is just storing the date/time that you asked it to store.  You either need to store the students time zone or convert the dates to a common time zone before storing to the database.

Eitherway it is implemented, you probably need to use the Calandar object to handle the different time zones.  Here is an example for creating a calendar object in different time zones:

  Calendar date1=Calendar.getInstance(TimeZone.getTimeZone("BST"));
  Calendar date2=Calendar.getInstance(TimeZone.getTimeZone("GMT"));

date1 and date2 are initialized to the current date/time in each zone.  To get Date objects just to the following:
  Java.util.Date dt= date1.getTime();

Now set the TimeZone to GMT and print those again

Test Date  = 11-Oct-02    23:00
Arg  Date  = 01-Nov-02    00:00
Test Date  = 10-Oct-02    23:00
Arg  Date  = 01-Nov-02    00:00
Test Date  = 21-Oct-02    23:00
Arg  Date  = 01-Nov-02    00:00

Same result


The dates stored in the database don't use any specific timezone that I'm aware of. Looking at the raw data it is all set fine. It's only when I get my hands on it in Java that it seems to be doing this.

I imagine it's just the formatting that's different and that internally it's still the same time stamp - gonna go check that now :)

Pete, what *is* the TimeZone you should be using btw?

I'm in the UK so at present I believe we're at GMT. However some of the dates occur when we're in BST.

When printing the result I think Java is taking this into account ( hence the one hour difference ). The problem is, BST is one hour ahead of GMT so when we try to convert it to GMT it takes an hour off the time - which basically means we see the date a day earlier than it should be.

At least as far as I can tell - but I've been at this for a long time and my brain has kinda melted lol

If all the times should be set to midnight, why, btw, are you interested in anything other than the date?

I'm not - I only noticed that there was a potential problem when I printed the whole date string ( although I can't recall why I printed it lol ).

Here's a quick example of what I'm thinking - please note the time issues dont actually apply to this date - it's just an illustration :)


Say the census date is  01-NOV-04 00:00


now because this is in BST and not GMT, instead the date shows as :


31-OCT-04 23:00

Now if I scan the data to find completers by seeing if they finished on the 01-NOV or after, I will miss these entries because they appear to be on the 31-OCT

Or maybe I just need to take a break hehe


Thanks for helping me struggle through this btw guys :)

 

Is it possible that the program that inserted the date has adjusted the time to allow for daylight saving?

I wouldn't have thought so. The program itself is fairly stupid and afaik Oracle doesn't handle timezones itself. The staff just pick the date throughout the year regardless of timezone. Based on that I can't see how anything automated would be going on. When I look at the data from sql plus, it looks okay so I still think it's something I'm doing wrong....

Can you show a result of a date field select pasted from SQL Plus?

As you can see it's a fair spread of months....

1979-09-28 00:00:00
1975-01-03 00:00:00
1971-10-23 00:00:00
1944-05-01 00:00:00
1983-05-21 00:00:00
1977-03-03 00:00:00
1956-07-08 00:00:00
1973-09-15 00:00:00
1957-04-24 00:00:00
1981-06-04 00:00:00
1960-11-17 00:00:00
1985-05-14 00:00:00
1979-10-10 00:00:00
1962-08-17 00:00:00
1968-07-03 00:00:00
1953-12-31 00:00:00
1977-11-20 00:00:00

All those dates, if you print them using all defaults should not alter the time. The only thing that'll vary is GMT/BST

(So there's probably a mistake somewhere)

Okay I have finally managed to fix the problem. Object's URL got me headed in the right direction.

The default timezone on this machine is Europe / London  which does support daylight savings time. This meant that when daylight saving was in force, times were held as one hour earlier than they should be - presumably because the dates are all stored as simple dates and Java assumed that if the date was during daylight savings that the date itself would take this into account.

Anyway I was able to solve this by forcing the JVM to use the GMT timezone which doesn't support daylight savings time with this method :


TimeZone.setDefault(TimeZone.getTimeZone("GMT"));


Now as the default TimeZone doesn't support daylight savings, all dates are held the same regardless of where they are in the year....

>> Okay I have finally managed to fix the problem.
Nice to hear :°)

Hi,
   I'd forgotten to close the thread. I've given 300 points to Objects as the URL got me going in the right direction and ultimately lead me to solving the problem. I've shared the remaining points amongst the people that spent a lot of time trying to help with code samples and stuff.

thanks to everyone who took part :)

Thanks

(: