new java date query

Discussion in 'Java' started by Big Jim, Apr 7, 2006.

  1. Big Jim

    Big Jim Guest

    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.
    Big Jim, Apr 7, 2006
    #1
    1. Advertising

  2. Big Jim

    Oliver Wong Guest

    "Big Jim" <> wrote in message
    news:VpxZf.2626$...
    > 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
    Oliver Wong, Apr 7, 2006
    #2
    1. Advertising

  3. 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
    Patricia Shanahan, Apr 7, 2006
    #3
  4. Big Jim

    Roedy Green Guest

    On Fri, 07 Apr 2006 22:35:45 GMT, Patricia Shanahan <>
    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.
    Roedy Green, Apr 7, 2006
    #4
  5. Big Jim

    P.Hill Guest

    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, Apr 8, 2006
    #5
  6. Big Jim

    P.Hill Guest

    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
    P.Hill, Apr 8, 2006
    #6
  7. Big Jim

    Big Jim Guest

    "Roedy Green" <> wrote in
    message news:...
    > On Fri, 07 Apr 2006 22:35:45 GMT, Patricia Shanahan <>
    > 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, Apr 8, 2006
    #7
  8. Big Jim

    Big Jim Guest

    "Patricia Shanahan" <> wrote in message
    news:5PBZf.1414$...
    > 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, Apr 8, 2006
    #8
  9. Big Jim

    Big Jim Guest

    "Oliver Wong" <> wrote in message
    news:z7BZf.33249$K11.33086@clgrps12...
    >
    > "Big Jim" <> wrote in message
    > news:VpxZf.2626$...
    >> 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.
    Big Jim, Apr 8, 2006
    #9
  10. Big Jim

    Roedy Green Guest

    On Sat, 08 Apr 2006 21:32:38 GMT, "Big Jim" <> 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.
    Roedy Green, Apr 8, 2006
    #10
  11. Big Jim

    Guest

    [spam removed]
    , May 15, 2013
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    595
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,244
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    513
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    718
    Richard Heathfield
    Nov 8, 2003
  5. Nik
    Replies:
    11
    Views:
    255
    Dr J R Stockton
    Sep 11, 2009
Loading...

Share This Page