CODEWITHSUNDEEP
mysql
Kevin
Kevin

Reputation: 513

Interval from difference of two times in MySQL

I have a MySQL query that uses two different timestamps that can be perceived as an interval of time. I need to implement an extra 25-30% of the difference. For example, if the difference between the times is 30 minutes, I need to request an additional 5 minutes before and 5 minutes after.. Is there a way to not only get the difference between the two time stamps, but then calculate this 'percentage' of time to acquire the appropriate interval, all within one statement?

Something like the following, where x is the calculated value.

..

DATE_FORMAT(timestamp1 - INTERVAL x MINUTE,'%m/%d/%Y %r') AS 'Start',

DATE_FORMAT(timestamp2 + INTERVAL x MINUTE,'%m/%d/%Y %r') AS 'End',

..

Upvotes: 1

Views: 451

Answers (1)

JYelton
JYelton

Reputation: 36512

You have a start timestamp and an end timestamp in the database. You want to add an additional percentage of time to these values, based on the time span between them. In other words, you can't add or subtract any amount of time without knowing the difference between the two.

So, first you must figure out the time span. I'm going to use minutes as the unit, but use whatever works best for your needs:

SELECT
    @new_span := TIMESTAMPDIFF(MINUTE, timestamp1, timestamp2) * 1.25 new_span,
    DATE_SUB(timestamp1, INTERVAL (@new_span*60)/2 SECOND) AS new_start,
    DATE_ADD(timestamp2, INTERVAL (@new_span*60)/2 SECOND) AS new_end,
    TIMESTAMPDIFF(MINUTE, timestamp1, timestamp2) old_span,
    timestamp1 AS previous_start,
    timestamp2 AS previous_end
FROM table;

So this query stores the difference between the two times plus 25% as a variable named new_span. The new_start and new_end fields use the variable (converted to seconds and divided in half) to modify the original start/end times. I'm also selecting old_span and the original start/end times, for comparison.

Here is sample output:

new_span  new_start            new_end              old_span  previous_start       previous_end         
1.25      2011-08-11 15:53:22  2011-08-11 15:55:38  1         2011-08-11 15:54:00  2011-08-11 15:55:00  
1350.00   2011-08-09 00:45:00  2011-08-10 17:15:00  1080      2011-08-09 12:00:00  2011-08-10 06:00:00

Upvotes: 2

Related Questions