Calculating time between two dates per day

Question

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:

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.

@RiggsFolly Is it duplicate? This is about MySQL, the linked question is about PHP... — Stefano Zanini, Apr 24 '17 at 13:11
@RiggsFolly this question is a mySSQL question? so its not a duplicate? — tom, Apr 24 '17 at 13:12
@StefanoZanini Ah, yo may well be right. I will remove the DUP. — RiggsFolly, Apr 24 '17 at 13:13
@StefanoZanini But it has to have a dup somewhere in the MYSQL section — RiggsFolly, Apr 24 '17 at 13:16
@tom really you are showing your private db data on a public platform ? if this is so then please make some fake data and update your question — lazyCoder, Apr 24 '17 at 13:17
@BunkerBoy their is nothing in this database what is private? — tom, Apr 24 '17 at 13:19
You could start by [Reading the MYSQL manual, maybe start here with TIMEDIFF()](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff) — RiggsFolly, Apr 24 '17 at 13:21
Or [TIMESTAMPDIFF()](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff) — RiggsFolly, Apr 24 '17 at 13:21
Another thing: You are at least the third person who wants help on this database, making this a total of some fifteen questions on it in less than two weeks. Neither of you seems to know enough about SQL to deal with the tasks at hand. You may want to consider hiring a coach or attending classes. — Thorsten Kettner, Apr 24 '17 at 13:34
You want to show *every* record in the database that has a predecessor (i.e. a previous record for the same address) in order to show time differences? — Thorsten Kettner, Apr 24 '17 at 13:40
@ThorstenKettner that is what I want yeah! but you get the problem because every divice posts alot of address so you need to take the last record from every day and look when the next one is and calculate the time between that — tom, Apr 24 '17 at 13:43
So start by grouping by address and day and get the max(time) for them. This result set is the base you work on. With a better DBMS you would then use `LAG` or `LEAD` to find previous or following records. In MySQL you'd have to select the same dataset again and find those records by joining. That's not very difficult, but probably rather slow. — Thorsten Kettner, Apr 24 '17 at 13:56
@ThorstenKettner If you look at the code that stefanozanini answered is that what you mean? — tom, Apr 25 '17 at 07:55
If *you* look at the code, do you see it grouping by address and *day*? Just read the queries carefully and think about what they do. Is it what you want? Try with your data or make up an example on paper and go through it. Do you get the results you want? — Thorsten Kettner, Apr 25 '17 at 08:46
@ThorstenKettner i am not really getting what I want. If you read the comments you know whatis the problem. I need to compair the last time of the day with the first time of the day after. — tom, Apr 25 '17 at 08:49
This (that you don't really know what you want) is what I feared. **So start thinking.** You and your colleagues come here and want us to write queries for you. This is not what StackOverflow is for. Think about what you want. Take some sample data and paper and draw a table of the results you want. Then think about how you get to the data. Which records do you use, which not? Once you know how to get the data (and only then) write the query... — Thorsten Kettner, Apr 25 '17 at 08:55
... If you have a problem with the query, ask your colleagues for help. Use Google. Search StackOverflow. Only if you find no answer, then come here. With a very precise problem which you describe as good as possible. — Thorsten Kettner, Apr 25 '17 at 08:56
@ThorstenKettner I dont know why you say you and your colleagues because I am alone? but okay! I am ofcourse thinking I even changed the query but some things I just dont get it and thats why I am asking it here because I got lot of projects running atm. And I do not find allot on the internet because they all say min and max but thats not what I need at this point — tom, Apr 25 '17 at 08:57
Then who is S. ter Keurs? Who is thattom? They have been asking help on the same database. As to your problem: you are not even really starting to work on it. You haven't taken the time to think about what data you want in your results. The problem is not the query. The problem is that you don't know what you want it do. So take some paper, take a pen and start writing down what *exactly* the query shall do and what results it shall produce. — Thorsten Kettner, Apr 25 '17 at 09:11
@ThorstenKettner Okay let me makeit clear what I want: I want a query that calculates the time between 2 times(dates) The problem is that there are allot of addresses each day and to know the time between the last time of a day and the next time on a other day than today — tom, Apr 25 '17 at 09:40

Stefano Zanini · Accepted Answer · 2017-04-24T13:43:04.720

0

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.

edited Apr 24 '17 at 13:43

answered Apr 24 '17 at 13:26

Stefano Zanini

5,876
2
13
33

I see what you mean but I get an error message https://gyazo.com/5172fa05e9a6876eeef070f710e94179 – tom Apr 24 '17 at 13:34
That's because interval is a keyword, sorry! You can either use another alias (like intvl or anything else) or surround the word in backticks. I edited my answer with this latter option – Stefano Zanini Apr 24 '17 at 13:38
Awhhh I see! Thx! only now I get this error message https://gyazo.com/4a5ff42cbd82587b0cbe8affd2024cea – tom Apr 24 '17 at 13:40
All subquery results must have an alias. Now it's fixed – Stefano Zanini Apr 24 '17 at 13:43
I just noticed a thing that is not what I want as you can see in this link https://gyazo.com/92654f94e2be7e506c11bbb6d88b9c6f there are to many from 1 address./ I think you calculate the time between every address and you need to 1 address for a day – tom Apr 25 '17 at 06:51
You have to explain yourself: do you want first visit of day 1 compared to first visit of day 2? Or last with first? Or first with last? Or last with last? Or something else? – Stefano Zanini Apr 25 '17 at 08:05
I want to compair the last visit of the day with the first visit of the day after – tom Apr 25 '17 at 08:20
So I think you need some kind of distinct addresss per day. But I dont know – tom Apr 26 '17 at 07:53

Calculating time between two dates per day

1 Answers1