1

We are trying to compare a date stored in mySQL with a plain text date field read from the original of a Gmail message, using java date compare.

First time through, the Gmail message plain text Date is read and stored in a mySQL database field “date_sent” type TIMESTAMP. The next time that message is checked, it gets the message plain text Date and, using the Java date compare function, compares that with the stored date_sent value.

This compare usually works. However -- if the date and time of the message Date being compared is during the 1am hour on a day in which the time changed (daylight savings to standard), the compare fails.

Has anyone experienced this? how were you able to fix it?

Linda
  • 11
  • 1

2 Answers2

2

It seems like you've got an uphill battle. If a message arrives, dated 1:30am, on the date when Daylight Savings ends, I don't think there's any way you can tell whether it's the FIRST occurrence of 1:30am (before the clocks go back) or the SECOND.

Presumably, you'll never get a message dated 1:30am on the date when Daylight Savings starts, because this time won't actually exist.

So if you do this by converting the text field to a date and storing it, you'll always have an issue of how to compare such dates. Your comparison might be wrong sometimes if you pick the wrong 1:30am, and I don't think there's too much you can do about it, if you want your timezones to be correct, for both Daylight Savings Time and Standard Time.

One thing you might consider doing is storing the timestamps as text, not as dates, so that the conversion never happens. If you use a format like yyyy-MM-dd HH:mm:ss then you should be able to do a text comparison instead of a date comparison, and get the correct results.

Dawood ibn Kareem
  • 77,785
  • 15
  • 98
  • 110
  • Thanks David, it’s good to hear the same thoughts from someone else! We had kicked around the idea of changing to storing as text and doing that compare but were hoping we could avoid the need to change the field in the mySQL database from TIMESTAMP to Text and converting the existing data. However this does look like the most fool-proof way to do the compare. Is there any way to convert from one to the other, or would I need to re-create the data from the original source? – Linda Nov 12 '13 at 00:18
  • Sure. The `SimpleDateFormat` class is designed to let you convert back and forth between timestamps and text. Think carefully about what timezone you want it to use, because you'll still get the same issue, if you're converting timestamps to text that are in the 1am hour on the day that Daylight Savings ends; that is, they're going to get out of sequence. Depending on how this will be used, you might be best to use a timezone that doesn't have daylight savings time, when you do your initial conversion. That way, your timestamps will stay in sequence, even though some of them may be "wrong". – Dawood ibn Kareem Nov 12 '13 at 00:38
1

I've never experienced this because I've never done it. What I think you'll have to do is get/know the timezone of the text date, convert it into a Date object (using that timezone), then compare the dates that way.

You say you're using the Date compare method, but that says it requires a Date as input. So are you already converting? Please show us that code. Are you passing in a timezone?

Also, this question has very useful information that you can use.

Community
  • 1
  • 1
Daniel Kaplan
  • 62,768
  • 50
  • 234
  • 356
  • Thanks for the information, this is good to consider. We will pursue going the text compare route first, and see how that goes! – Linda Nov 12 '13 at 00:20