• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

Working with Dates

Hi,
   I'm having lots of fun working with dates, primarily that for some reason the dates in the database are in two different time zones ( i.e GMT and BST ). I need to extract these dates and put them all in the same time zone. I don't want to convert them as that will change the actual dates i.e there is an hour difference between the two time zones. I just want all the dates in the same time zone regardless of their original zone.

  I'd also like to know how to parse a String as a date - the constructor and parse have been deprecated. Can anyone provide an example?


Cheers,


Pete
0
petepalmer
Asked:
petepalmer
  • 19
  • 18
  • 15
  • +4
5 Solutions
 
zzynxSoftware engineerCommented:
>> I'd also like to know how to parse a String as a date
Use the parse() function of SimpleDateFormat
0
 
TimYatesCommented:
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 :-)
0
 
zzynxSoftware engineerCommented:
Date d = new SimpleDateFormat("yyyy/MM/dd").parse("1981/10/12");
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sudhakar_koundinyaCommented:
for parsing the String to date use

DateFormat.parse(string);

0
 
zzynxSoftware engineerCommented:
Hi Tim!
0
 
zzynxSoftware engineerCommented:
...and sudhakar ;°)
0
 
TimYatesCommented:
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
0
 
sudhakar_koundinyaCommented:
Here is an example to parse the String to date

http://www.ideas2work.com/java-articles/Java-Query-DateFormat.html
0
 
TimYatesCommented:
>  Hi Tim!

Hiya ;-)
0
 
sudhakar_koundinyaCommented:
Hello zzynx :-)

Good Morning. But here is already  evening 5:00 PM monday
0
 
petepalmerAuthor Commented:
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! :)
0
 
zzynxSoftware engineerCommented:
>> 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
0
 
CEHJCommented:
In Java, there is no time zone info in Date
0
 
zzynxSoftware engineerCommented:
>> when the timezones change, the database stores the dates in that timezone
That's not how it should be
0
 
petepalmerAuthor Commented:
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 :)
0
 
zzynxSoftware engineerCommented:
>> 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)
0
 
CEHJCommented:
>>Anyway to put them all in one timezone?

That should happen automatically. They'll be displayed in the default timezone
0
 
zzynxSoftware engineerCommented:
>> 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".
0
 
petepalmerAuthor Commented:
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 :)
0
 
CEHJCommented:
>>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
0
 
CEHJCommented:
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy zzz");
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
0
 
petepalmerAuthor Commented:
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....
0
 
CEHJCommented:
>>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?
0
 
zzynxSoftware engineerCommented:
>> The dates are not stored and being retrieved as string in the db are they?
Mmmm. I wouldn't bet on that...
0
 
petepalmerAuthor Commented:
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.....
0
 
zzynxSoftware engineerCommented:
>> 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.
0
 
petepalmerAuthor Commented:
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);
0
 
CEHJCommented:
So...


System.out.println(sdf.format(startOfAcademicYear));
0
 
CEHJCommented:
(You need to use a DateFormat + set TimeZone for printing your Date too, or a default format + TimeZone will be used)
0
 
zzynxSoftware engineerCommented:
>> 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.
0
 
petepalmerAuthor Commented:
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
0
 
CEHJCommented:
>>Can't figure out why lol

(See my last comment)
0
 
zzynxSoftware engineerCommented:
>>>>Can't figure out why lol
>>(See my last comment)
and my last too ;°)
0
 
zzynxSoftware engineerCommented:
>>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

The clue is in how you create the String's date1 and date2?
0
 
objectsCommented:
0
 
MogalManicCommented:
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).
0
 
petepalmerAuthor Commented:
So if I use get timestamp instead of getDate(), I can convert that into a "normal" Date object.....

Let me go try that :)
0
 
CEHJCommented:
Yes, a java.sql.Date has no time information
0
 
CEHJCommented:
>>I can convert that into a "normal" Date object.....

Yes

java.util.Date d = rs.getTimestamp(1).getTimestamp();
System.out.println(sdf.format(d));
0
 
petepalmerAuthor Commented:
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.
0
 
CEHJCommented:
What DateFormat have you used to produce that 'test date'?
0
 
petepalmerAuthor Commented:
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.
0
 
CEHJCommented:
Yes, you'd be better off with the more precise

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

0
 
petepalmerAuthor Commented:
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
0
 
MogalManicCommented:
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();

0
 
CEHJCommented:
Now set the TimeZone to GMT and print those again
0
 
petepalmerAuthor Commented:
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 :)
0
 
MogalManicCommented:
When you load them from the database INTO java is when the timezone is assumed.  If you just use java.util.Date (or java.sql.Timestamp) they will ALL have the same timezone.  The timezone will be the default local's timezone.  To load from the database a specific timezone it would be something like this:

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

   date1.setTime(result.getTime("timeField));
   date2.setTime(result.getTime("timeField));
0
 
CEHJCommented:
Pete, what *is* the TimeZone you should be using btw?
0
 
petepalmerAuthor Commented:
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
0
 
CEHJCommented:
If all the times should be set to midnight, why, btw, are you interested in anything other than the date?
0
 
petepalmerAuthor Commented:
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 :)

 
0
 
CEHJCommented:
Is it possible that the program that inserted the date has adjusted the time to allow for daylight saving?
0
 
petepalmerAuthor Commented:
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....
0
 
CEHJCommented:
Can you show a result of a date field select pasted from SQL Plus?
0
 
petepalmerAuthor Commented:
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
0
 
CEHJCommented:
All those dates, if you print them using all defaults should not alter the time. The only thing that'll vary is GMT/BST
0
 
CEHJCommented:
(So there's probably a mistake somewhere)
0
 
petepalmerAuthor Commented:
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....
0
 
zzynxSoftware engineerCommented:
>> Okay I have finally managed to fix the problem.
Nice to hear :°)
0
 
petepalmerAuthor Commented:
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 :)
0
 
zzynxSoftware engineerCommented:
Thanks
0
 
objectsCommented:
(:
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 19
  • 18
  • 15
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now