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

What have you tried?

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

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

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

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.

you were ~so~ close with this

you forgot to divide the DATEDIFF by two !!


Duh… Thank you