new java date query
Big Jim
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
"Big Jim" <no...@nowhere.com> wrote in message
news:VpxZf.2626$D7....@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
...
> 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
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
> 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
> 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
"Roedy Green" <my_email_is_post...@munged.invalid> wrote in
message news:frqd32l1gl4t94u61...@4ax.com...
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
"Patricia Shanahan" <pa...@acm.org> wrote in message
news:5PBZf.1414$Fy2...@newsread3.news.pas.earthlink.net...
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
"Oliver Wong" <ow...@castortech.com> wrote in message
news:z7BZf.33249$K11.33086@clgrps12...
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
sorting it out.
Roedy Green
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
vysh...@gmail.com
Get the best All Deals in Washington Dc from NewLook Deals. We offers Day Spa deals like Facial, Threading, Laser Hair Removal deals and more.
At present we offers best discount on below deals:
• Eyebrow Threading, Dermalogica / Herbal Facial and Diamond Dermabrasion (Up to 55% Off)
o Herbal Facial (Shahnaz Advance) or Dermalogica Power Facial
o 10 Eye Brow Threading
o Diamond Dermabrasion
o 10 Full Face Threading
• One Full Body Laser Hair Removal with Gentle Max Pro (Up to 85% Off)
• Six Laser Hair Removal Treatments on Small, Medium, Large and Extra Large Area's (UP to 83% Off)
o 10 Full Face Threading
o Six Laser Hair Removal Treatments on Medium Area’s
o Six Laser Hair Removal Treatments on Large Area’s
o Six Laser Hair Removal Treatments on Extra Large Area’s
For More Details Please visit our Website: https://www.newlookdeals.com/alldeals/washington-dc
Communication Address:
201 Elden Street,Suite # 203, Herndon, VA , 20170
Our customer service is pinned at : custome...@newlookdeals.com