Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Java (http://www.velocityreviews.com/forums/f30-java.html)
-   -   new java date query (http://www.velocityreviews.com/forums/t151920-new-java-date-query.html)

Big Jim 04-07-2006 05:35 PM

new java date query
 
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.



Oliver Wong 04-07-2006 09:49 PM

Re: new java date query
 

"Big Jim" <noone@nowhere.com> wrote in message
news:VpxZf.2626$D7.305@newsfe3-win.ntli.net...
> 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


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


Patricia Shanahan 04-07-2006 10:35 PM

Re: new java date query
 
Big Jim wrote:
....
> 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?


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





Roedy Green 04-07-2006 10:57 PM

Re: new java date query
 
On Fri, 07 Apr 2006 22:35:45 GMT, Patricia Shanahan <pats@acm.org>
wrote, quoted or indirectly quoted someone who said :

>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.

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

P.Hill 04-08-2006 04:57 PM

Re: new java date query
 
Roedy Green wrote:
> 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

P.Hill 04-08-2006 05:00 PM

Re: new java date query
 
Oliver Wong wrote:
> 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

Big Jim 04-08-2006 09:32 PM

Re: new java date query
 

"Roedy Green" <my_email_is_posted_on_my_website@munged.invalid > wrote in
message news:frqd32l1gl4t94u61tabd69981p99ht5hu@4ax.com...
> On Fri, 07 Apr 2006 22:35:45 GMT, Patricia Shanahan <pats@acm.org>
> wrote, quoted or indirectly quoted someone who said :
>
>>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.
>
> --
> Canadian Mind Products, Roedy Green.
> http://mindprod.com Java custom programming, consulting and coaching.


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!



Big Jim 04-08-2006 09:36 PM

Re: new java date query
 

"Patricia Shanahan" <pats@acm.org> wrote in message
news:5PBZf.1414$Fy2.172@newsread3.news.pas.earthli nk.net...
> Big Jim wrote:
> ...
>> 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?

>
> 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
>

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.



Big Jim 04-08-2006 09:40 PM

Re: new java date query
 

"Oliver Wong" <owong@castortech.com> wrote in message
news:z7BZf.33249$K11.33086@clgrps12...
>
> "Big Jim" <noone@nowhere.com> wrote in message
> news:VpxZf.2626$D7.305@newsfe3-win.ntli.net...
>> 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

>
> 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

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.



Roedy Green 04-08-2006 10:08 PM

Re: new java date query
 
On Sat, 08 Apr 2006 21:32:38 GMT, "Big Jim" <noone@nowhere.com> wrote,
quoted or indirectly quoted someone who said :

>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


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


All times are GMT. The time now is 04:46 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.