Calculating Value Between Date Ranges

Question

So I am a bit stumped, I know how to do this theoretically but I am having trouble executing it in practice.

Basically I have a table and a revisions table. The table reflects the status as of now and the revisions table reflects the past status of the table.

id3,    id2,      id1,   title,   timestamp, status, 
56456   229299  4775    x name      1432866912  0   
56456   232054  123859  x name      1434000054  1   
56456   235578  16623   x name      1435213281  1   
56456   237496  139811  x name      1464765447  1   
56456   381557  0       x name      1487642800  1   
56456   616934  186319  x name      1496103368  1   
56456   668046  246292  x name      1505386262  1   
56456   766390  246292  x name      1523273582  1

Basically what I want is to look at the historical live/offline status of all entries in the table. So I know the current status is live, and I know the dates the entry was offline/live as well.

What I want to do is calculate the live or offline dates between the timestamps. The dates between 1 -> 0 Are live dates. The dates between 1 -> Are live dates. The dates Between 0 -> 1 Are offline dates and the dates between 0 -> 0 Are offline dates.

So ideally my data would have a live/offline status delineated by each day in between each of these status changes.

I.E

The the output would display the dates between Timestamp 1432866912 & 1434000054 as the Status being Offline

I tried searching but didn't see anything relevant.

EDIT:

@RaymondNijland The first row has a unixtimestamp for the date May 28, 2015 & the second row a timestamp of the date June 11, 2015. The first row is offline and the second row is live.

So I basically want my data to look like this

Date          Status
May 28, 2015  Offline
May 29, 2015  Offline
May 30, 2015  Offline
....
....
June 9, 2015 Offline
June 10, 2015 Offline
June 11, 2015 Live
June 12, 2015 Live

I need to do it this way because our database doesn't store the data on a daily basis, but only when a change is made to the data.

think you need to simulate LEAD in MySQL, what the expected output based on your example data? — Raymond Nijland, Apr 12 '18 at 18:18
I'm totally confused. What do the rows represent? Where does 14328669127 come from? What output do you want? Where is the "day" stored? — Gordon Linoff, Apr 12 '18 at 18:18
@GordonLinoff - The rows are entries that have been modified. Each modification is tied to id3, or the unique identifier for the entry. 14328669127 is the UNIX-TIMESTAMP when the modification took place, which is the day it was modified. — Jalal.Hassan, Apr 12 '18 at 18:27
@AbhishekKeshri Thank you for catching that. I must have accidentally added a 7 in there. — Jalal.Hassan, Apr 12 '18 at 19:49
can you get the data in a dict or list, and solve it using something like python? — Abhishek Keshri, Apr 12 '18 at 19:58
@AbhishekKeshri Unfortunately no. Our database is a bit of a Frankenstein Monster so it requires a bit of creativity and inspiration to get the data sometimes. — Jalal.Hassan, Apr 12 '18 at 20:43
You know your result set will be in 1000s rows is that what you want? — Krish, Apr 12 '18 at 20:45
@krishKM Yes, but I cannot think of another way to pull the historical data with the way our DB is set up. — Jalal.Hassan, Apr 12 '18 at 21:04

score 0 · Answer 1 · answered Apr 12 '18 at 18:56

0

Use "case" to know if it is Offline or Live and "date_format" function to display timestamp in date. See this demo: http://sqlfiddle.com/#!9/88281f/13

select DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%b %e, %Y') AS  `date` , 
  case when status=0 then 'Offline' else 'Live' end as `status`
from yourTbl
order by `timestamp`

answered Apr 12 '18 at 18:56

jose_bacoy

12,227
1
20
38

Thanks I know how to do that, but what I am having trouble with is showing the Live/Offline status between two different dates. I further clarified in my original post. – Jalal.Hassan Apr 12 '18 at 19:09
Got it. Im revising my answer – jose_bacoy Apr 12 '18 at 19:11

score 0 · Answer 2 · answered Apr 12 '18 at 21:18

You can't retrieve records that aren't in your table. In such cases, you must generate the dates sequence and then perform cross-checks or left joins etc.

have a look at Generating Date sequence

Below code generates list of dates using min and max dates from your revision table. Do a cross check with your revision table and get the last seen/found status code for the current row date.

assuming your table is called Revisions with status and timestamp fields. following SQL code should work for you:

Fiddle here

select
  TDates.genDate, -- generated date sequence 
  (select      case when r.status =0 then 'Offline' else 'Live' end
    from       revisions R
    WHERE      date(from_unixtime(R.Timestamp)) <= TDates.genDate
    order by   R.timestamp desc
    limit 1
  ) as genStatus
from 
  (
    SELECT     * 
    FROM
      (select adddate(T4.minDate, t3*1000 + t2*100 + t1*10 + t0) genDate, T4.minDate, T4.maxDate from
        (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
        (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
        (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
        (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
        -- using your table get the min date and max date we are going to check. So we generate just the required dates.
        (select date(from_unixtime(min(R.timestamp))) as minDate, date(from_unixtime(max(R.timestamp))) as maxDate from revisions as R  ) T4
      ) T
    where T.genDate <= T.maxDate
  ) As TDates 
order by TDates.genDate

this is just concept, you are more than welcome to improve performance hints

Thanks, I will update you when I have a chance to look it over. — Jalal.Hassan, Apr 12 '18 at 21:57

Calculating Value Between Date Ranges

2 Answers2