CODEWITHSUNDEEP
mysqlsqlcounttimestamp
tom
tom

Reputation: 81

Calculating time between two dates per day

I'm working on a project where I search for a wifi signal (from cellphones etc). It detects every mac address that is around in the wifi sensors radius. This data is then sent from a server to a database, which uses a reporting tool to show statistics. This can be used in stores to study customer behavior.

This is what the data looks like:

enter image description here

The thing is, I want to know the time between entries, The problem is if a person stays for 10 minutes in the store it wil display alot of addresses, what I want to calculate is the difference between the visits. So what I need to do is count the time between the current day and the next day that they came.

I would then like to display this in a table like the one below.

current time       |  next time they came  |  Time between visist
-------------------------------------------------------------------
*current time*     |  *other day*          |  *Time between visits*

I have no idea how I should do this, abstract thinking is always welcome

P.s If there is any missing info, please comment. I'm new to the forums.

Upvotes: 0

Views: 100

Answers (1)

Stefano Zanini
Stefano Zanini

Reputation: 5926

First of all you have to translate that time field into its readable date part

select date(from_unixtime(`time`)) from yourTable;

This value can be the joining criteria of a self join

select  t1.address,
        from_unixtime(t1.`time`),
        min(from_unixtime(t2.`time`))
from    yourTable t1
left join
        yourTable t2
on      t1.address = t2.address and
        date(from_unixtime(t1.`time`)) < date(from_unixtime(t2.`time`))
group by t1.address, from_unixtime(t1.`time`)

This would get you, for each address and each visit time, the earliest visit time on a different day.

From there you could return the time difference

select  tt.address,
        tt.visit,
        tt.next_visit,
        timediff(coalesce(tt.next_visit, tt.visit), tt.visit) as `interval`
from    (
            select  t1.address,
                    from_unixtime(t1.`time`) as visit,
                    min(from_unixtime(t2.`time`)) as next_visit
            from    yourTable t1
            left join
                    yourTable t2
            on      t1.address = t2.address and
                    date(from_unixtime(t1.`time`)) < date(from_unixtime(t2.`time`))
            group by t1.address, from_unixtime(t1.`time`)
        ) tt

The coalesce is to avoid having a null in the timediff function, which would happen for each address's last visit.

Upvotes: 0

Related Questions