2

Storage table

|        id| product_id |           date_add |         date_remove
------------------------------------------------------------------
|        1 |         10 |2018-04-02 08:28:43 | 2018-04-03 07:21:08     
|        2 |         10 |2018-04-05 08:28:43 | 2018-04-06 08:28:50
|        3 |         10 |2018-04-01 08:28:43 | 2018-04-05 08:28:50  
|        4 |         12 |2018-04-01 08:28:43 | 2018-04-03 07:21:08 
|        5 |         12 |2018-04-04 08:28:43 | 2018-04-04 10:28:43
|        6 |         13 |2018-03-01 08:28:43 | 2018-03-01 10:28:43

how to find ? how many days product was in the storage in period 2018-04-01 to 2018-04-05?

find result

| product_id | days
|        10 |   5   
|        12 |   3

try

SELECT product_id, SUM(DATEDIFF(date_remove, date_add)) as days
FROM storage
 where date_remove BETWEEN '2018-04-01 00:00:00'
AND '2018-04-05 23:59:59'
AND date_add BETWEEN '2018-04-01 00:00:00'
AND '2018-04-05 23:59:59'
GROUP BY product_id

but result wrong because 'SUM' sums all days

get result

| product_id | days
|        10 |   7

correct result

| product_id | days
|        10 |   5

upd

http://rextester.com/QFS96125

result 9,646805555556 but probably maximum 5 days and product_id 13 correct 0,436608796296 but result 0,87

valera
  • 79
  • 1
  • 7
  • 1
    Where is your query that you have been trying so far and what specific issue does it have? – B001ᛦ Apr 04 '18 at 08:44
  • convert to unix timestamp, divide by (60*60*24). – Simba Apr 04 '18 at 08:44
  • Possible duplicate of [How to get the number of days of difference between two dates on mysql?](https://stackoverflow.com/questions/2490173/how-to-get-the-number-of-days-of-difference-between-two-dates-on-mysql) – Nitin Dhomse Apr 04 '18 at 08:50
  • 2
    What exactly keeps you from writing the query yourself? What do you want us to tell you? The algorithm? How to aggregate rows? What else? – Thorsten Kettner Apr 04 '18 at 08:54

2 Answers2

1

First you want to look at all date ranges that are within or overlap with the range 2018-04-01 to 2018-04-05.

where date_add < date '2018-04-06' and date_remove >= date '2018-04-01'

Then, with the ranges found, you want to consider only their days in the range 2018-04-01 to 2018-04-05.

greatest(date_add, date '2018-04-01')
least(date_remove, date '2018-04-06')

Then you want to count days. Here you need a rule. Do you want to look at single ranges and only take their full days which you add up then? Or do you want to consider day fractions, add all up and see how many full days result? For the latter you could get durations in seconds and add these up:

select
  product_id,
  sum(timestampdiff(second, greatest(date_add, date '2018-04-01'), 
                            least(date_remove, date '2018-04-06'))
     ) / 60 / 60 / 24  as days
from storage
where date_add < date '2018-04-06' and date_remove >= date '2018-04-01'
group by product_id
order by product_id;

This gets you

product_id | days
-----------+---------------
10         | 5,599872685185
12         | 2,036400462963

Feel free to use FLOOR, CEIL or ROUND on the resulting days.

Rextester demo: http://rextester.com/XTVU47656

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thank you so math! It work but not completely correct =( http://rextester.com/QFS96125 result 9,646805555556 but probably maximum 6 days and product_id 13 correct 0,436608796296 but result 0,87 – valera Apr 04 '18 at 10:43
  • Looks fine to me. ID 1: 5 full days + ID 2: some hours + ID 3: 4 days and some seconds = 9.646... days for product 10. ID 4: almost two days + ID 5: 2 hours = 2,036... days for product 12. IDs 6 and 7: 10 hours, 28 minutes 43 seconds each = 0,873... for product 13. – Thorsten Kettner Apr 04 '18 at 12:02
0

To obtain such result, try

SELECT SUM(DATEDIFF(date_remove, date_add)) as days
FROM table
GROUP BY product_id

Keep in mind that this will sum all the days for the same product_id. To get the result for each id, use:

SELECT id, product_id, DATEDIFF(date_remove, date_add) as days
FROM table
Cynical
  • 9,328
  • 1
  • 15
  • 30
  • You have given an answer, before valera told us what their problem is. Your queries are fine, but for ten times 59 minutes the first query would give zero (full) hours. This may be desired or not. We don't know the actual requirement. – Thorsten Kettner Apr 04 '18 at 09:00
  • it's almost what I need but if table have this variant | 1 | 10 |2018-04-02 08:28:43 | 2018-04-03 07:21:08 ------------------------------------------------------------------------- | 2 | 10 |2018-04-03 08:28:43 | 2018-04-05 08:28:50 ------------------------------------------------------------------------- | 3 | 10 |2018-04-01 08:28:43 | 2018-04-05 08:28:50 ------------------------------------------------------------------------- result this query product have 7 days but the correct 5 days (in period 2018-04-01 to 2018-04-05) – valera Apr 04 '18 at 09:12
  • @ThorstenKettner I think the request is clear enough, nevertheless I agree with you that some explanation on the requirements is needed. – Cynical Apr 04 '18 at 09:15