SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Finding the midpoint between two dates

    How would I find the date in the middle of two fields: start_date and end_date()?

    Thank you E

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What have you tried?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by eruna View Post
    How would I find the date in the middle of two fields: start_date and end_date()?
    presumably end_date() isn't really a function as it would appear

    i would use start_date plus an interval number of days calculated as half of the number of days between start_date and end_date

    i can haz mysql date functions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I was hoping this would be a piece of cake for someone who knows MYSQL better than me.
    I've tried lots of things can't figure out anything that has an intelligible result and doesn't generates an error.
    Here are a couple of them:

    SELECT date_responded, start_date, end_date, (start_date+end_date )/2
    FROM `volunteer_response`
    WHERE end_date !='0000-00-00'

    > result is something like this: 20081051000000 Looks like :2008-10-51 00:00:00 Am I reading this wrong? It is the average of: 2008-09-01 00:00:00 and 2008-12-01 00:00:00

    -------------

    SELECT date_responded, start_date, end_date, (start_date+INTERVAL DATEDIFF(end_date ,start_date) DAY)

    FROM `volunteer_response`
    WHERE end_date !='0000-00-00'

    This returns the end date

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,069
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    How about FROM_UNIXTIME( UNIX_TIMESTAMP(start_date) + UNIX_TIMESTAMP(end_date) / 2 ) ?

    According the MySQL manual this is lossy since FROM_UNIXTIME and UNIX_TIMESTAMP don't map one to one, but it's worth a try and see if it helps anything
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yea I tried experimenting with FROM_UNIXTIME

    Your suggestion had this result
    start: 2008-09-01 00:00:00 end: 2008-12-01 00:00:00 result: 2028-02-16 03:00:00

    The SQL I need to do is usually pretty basic. It can be surprising how tricky it can be to come up with some calculations.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by eruna View Post
    SELECT date_responded, start_date, end_date, (start_date+INTERVAL DATEDIFF(end_date ,start_date) DAY)

    This returns the end date
    you were ~so~ close with this

    you forgot to divide the DATEDIFF by two !!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Duh.. Thank you

    E


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •