new java date query

Thread view:

Big Jim - 07 Apr 2006 18:34 GMT

apologies, not really a java query, but a follow on from my last post which
was specifically java.

From the last post I get the impression that the standard method of storing
dates (in this case a "next_call_date" as part of a deal object) is:

user in Japan enters 06/06/06
this gets converted to millis from epoch, let's say it equals 90, sends 90
to server
server saves 90 in DB (or converts 90 to a date in a consistent timezone,
lets say GMT gives 05/06/06 and stores that)

client requests said date
server (possibly converts the DB date to a long and) sends 90 to client
client converts 90 to local date and displays 06/06/06

So, I'm wondering what's the standard solution for querying the DB:

another client in NY enters 06/06/06
this gets converted to millis from epoch which this time gives us say 150
server saves 150 (or 06/06/06) in the DB

So, 2 questions,

1. some support analyst in the UK wants to get all the dates with a
start_date of 06/06/06, he enters
select * from deal where next_call_date = 06/06/06 which obviously won't
find the japan deal

2. the clients have a "search for deal where next_call_date is, is before or
is after" facility, how would they do this? e.g. in Japan the user enters:
get deals where next_call_date is 06/06/06 - if it then sends 90 to the
server how can the server know to return the deal entered in NY?

Is there a tried and trusted way to deal with these situations?

Cheers for any advice, Richard.

Reply to this Message

Oliver Wong - 07 Apr 2006 22:49 GMT

> apologies, not really a java query, but a follow on from my last post
> which was specifically java.
[quoted text clipped - 18 lines]
> this gets converted to millis from epoch which this time gives us say 150
> server saves 150 (or 06/06/06) in the DB

Looks like you got it so far.

> So, 2 questions,
>
> 1. some support analyst in the UK wants to get all the dates with a
> start_date of 06/06/06, he enters
> select * from deal where next_call_date = 06/06/06 which obviously won't
> find the japan deal

The values you gave aren't very realistic, so you'll have to stretch
your imagination a bit for this explanation:

When the analyst in UK says "Give me all deals with date of 06/06/06",
since he didn't specify a timezone, we presume he means in his local time
zone. Let's say that becomes the unix timestamp 120. Neither the "06/06/06
in Japan" matches, because that's equal to unix timestamp 90, nor the
"06/06/06 in NY", because that's equal to unix timestamp 150. However, let's
say "06/06/07 in Japan" gives a timestamp value of 120, so it matches, and
"06/06/05 in NY" timestamp of 120, so that matches too.

In other words, the analyst in UK is saying "Give me all the deals which
will start exactly 2 months from now (assuming it's April 6th, 2006 in the
UK analyst's local time zone), and he will get all deals which will occur on
"June 6th, 2006 in UK", all deals which occur in "June 7th, 2006 in Japan"
and all deals which occur in "June 5th, 2006 in NY", which are all refer to
the exact point in time (recall that these values aren't very realistic).
They all refer to the exact point in time because when it's April 6th 2006
in UK, it is simultaneously April 5th, 2006 in NY and April 7th, 2006 in
Japan. So when the analyst says "2 months from now", he's getting the
correct entries.

> 2. the clients have a "search for deal where next_call_date is, is before
> or is after" facility, how would they do this? e.g. in Japan the user
> enters: get deals where next_call_date is 06/06/06 - if it then sends 90
> to the server how can the server know to return the deal entered in NY?

The server should send all deals before or after unix timestamp 90.

> Is there a tried and trusted way to deal with these situations?

Deal only with timestamps internally. Format the timestamps into
"human-readable strings" only at the very last minute, right before
displaying the information to the user.

- Oliver

Reply to this Message

P.Hill - 08 Apr 2006 18:00 GMT

> When the analyst in UK says "Give me all deals with date of
> 06/06/06", since he didn't specify a timezone, we presume he means in
> his local time zone.

An interesting assumption, but one that is under Big Jim's control.

> Deal only with timestamps internally. Format the timestamps into
> "human-readable strings" only at the very last minute, right before
> displaying the information to the user.

Possibly a good idea and curiously apparently close to what the
folks who built java.util.Date where thinking at the time,
but timezones and calendars actually might come into play when
not only converting to a user readable date, but also at the other
end of things when converting to a timedate-aware (but not necessarily
timezone) database column.

-Paul

Reply to this Message

Big Jim - 08 Apr 2006 22:40 GMT

>> apologies, not really a java query, but a follow on from my last post
>> which was specifically java.
[quoted text clipped - 64 lines]
>
> - Oliver

Cheers Oliver, I see what you mean and it certainly looks like a sensible
approach.
I think the core of my problem is just simpler than this though. It was
dealing with the way the DB and objects were set up that made it tricky as I
think the app was simply originally written to be used in one location only.
Of course the original author has long gone and the problems were only
discovered when they started using it internationally.
I've posted a reply to Roedy's post further on that shows what way I think
I'll go with this.
For now, it's working, thank God for that! many thanks for your help in
sorting it out.

Reply to this Message

Patricia Shanahan - 07 Apr 2006 23:35 GMT

...

> 1. some support analyst in the UK wants to get all the dates with a
> start_date of 06/06/06, he enters
[quoted text clipped - 7 lines]
>
> Is there a tried and trusted way to deal with these situations?

I think you have a business policy question, not a computer question.

There are several things deals with a given start date could mean to
e.g. the support analyst:

1. Deals that had that start date in the analyst's timezone.

2. Deals that had that start date in the home timezone of the business.

3. Deals that had that start date in the timezone in which the deal was
made.

Any of those could be implemented, and might be the right answer. Forget
the computer. Suppose one person in the business is phoning another,
asking for a list of deals by date. Which would they mean?

Patricia

Reply to this Message

Roedy Green - 07 Apr 2006 23:57 GMT

>1. Deals that had that start date in the analyst's timezone.
>
>2. Deals that had that start date in the home timezone of the business.
>
>3. Deals that had that start date in the timezone in which the deal was
>made.

You can sidestep some of this complexity by getting rid of the time
and storing a pure date. Christmas is on Dec 25. The floral society
meets the third Tuesday of each month.... It has nothing to do with
timezones. The deal was signed as 2006-01-31. It is still binding no
matter what the timezone.

Where do these pure dates come from?

1. they may be entered by humans as yyyy-mm-dd having whatever meaning
they assign to them, typically the date in the timezone the deal was
signed.

2. You get them by considering the date of a timestamp at Greenwich.

3. You get hem by considering the date of a timestamp at the location
it was entered.

This is how BigDate works. I have found that perhaps 90% of date
calculations are simplified by getting rid of the time and timezone
before you do your calculations. If the result does not depend on
them, they just trip you up.

Further you can store a pure date in 16 or 32 bits quite neatly. For a
timestamp you need 64.

On the other hand, capturing raw data and storing it as UTC timestamps
means you have unabiguously nailed precisely when the event occurred.
So long as you get that right initially, you can then display it in
any form that people find convenient, even if something as goofy as
retroactive daylight saving were announced.

Signature

Canadian Mind Products, Roedy Green.
http://mindprod.com Java custom programming, consulting and coaching.

Reply to this Message

P.Hill - 08 Apr 2006 17:57 GMT

> You can sidestep some of this complexity by getting rid of the time
> and storing a pure date.

Hopefully, his code, JDBC driver and DBMS can do the right thing when
converting from a String (sent to a JDBC preparedStatement is concerned)
to whatever date/datetime/timestamp column he is using
on the DB side. He definitely needs to check this for different
timezones all the way from client through the driver to the server.
calls like preparedStatement.setDate( myBusinessDate, aCalendar );

As to which timezones, Patricia definitly has a point that there
is business choice here, regradless of the ability of code to handle
the various cases.

-Paul

Reply to this Message

Big Jim - 08 Apr 2006 22:32 GMT

>>1. Deals that had that start date in the analyst's timezone.
>>
[quoted text clipped - 33 lines]
> any form that people find convenient, even if something as goofy as
> retroactive daylight saving were announced.

Yes, I think if I ever get to mend this application properly I'll take that
approach i.e. change the idl from sending longs to sending date objects
containing just 3 ints for year, month and date as the smallest granularity
I care about is a particular day. The validation can easily be done on the
client.
I think the big difference in this app than most (apparently) is that I
don't really care about the "universal exact instant in time" that a java
date represents. It's just 3 ints that should appear the same to all clients
anywhere.
This would make searching from any client anywhere easy too as the DB would
just show the date that was entered, I could even change the db col to store
an eight digit int e.g. 20061225.
It also takes out any timezone complexity for sql interrogation of the DB or
any other app that cares to access it e.g. batch jobs or report generators.

Of course, now that it's working (in the messy way it does) I'll never get
to touch it again until they do announce retroactive daylight saving!

Reply to this Message

Roedy Green - 08 Apr 2006 23:08 GMT

>containing just 3 ints for year, month and date as the smallest granularity
>I care about is a particular day. The validation can easily be done on the
>client.

You can also store them as days since 1970 Jan 01 or some other epoch.
This is more compact and allows calculation such as +n days, what day
of week, direct compare. BigDate gives you many other calculation
methods on them, such as age in years,months,days, the Nth Tuesday of
the month...

http://mindprod.com/jgloss/products1.html#COMMON11

Signature

Canadian Mind Products, Roedy Green.
http://mindprod.com Java custom programming, consulting and coaching.

Reply to this Message

Big Jim - 08 Apr 2006 22:36 GMT

> ...
>> 1. some support analyst in the UK wants to get all the dates with a
[quoted text clipped - 26 lines]
>
> Patricia

Good point,
In this case it's "deals that had that date enetered on the client
regardless of my timezone, the timezone the deal was entered in or the
timezone of the business"
I've posted an answer to Roedy's post with where I think I'll go with this.

Reply to this Message

new java date query

Free Magazines