-3

(basically trying to get inactive users who haven't done any events in the past 30 days)

table is structured like this -

user ID // device ID // event name // event_date

How do i get list of users who have event recorded in a specific time period?

d0rnish
  • 1
  • 1
  • Where are you having difficulty? Have you looked at the `DATE_SUB()` function? – fubar Aug 25 '20 at 05:04
  • Thank you for replying. I'm new to SQL, just know some basic functions. Not sure how to use DATE_SUB() – d0rnish Aug 25 '20 at 05:07
  • The [W3Schools](https://www.w3schools.com/sql/func_mysql_date_sub.asp) website provides a few simple examples. Give that a read to start with and come back if you have any questions. – fubar Aug 25 '20 at 05:09
  • Thanks i went through this, not sure this solves my problem. Thing is, i can list users who are active in a specific time range, but i want those users who are active in a time range AND inactive in another time range – d0rnish Aug 25 '20 at 05:10
  • So find a list of all users excluding users (`NOT IN`) who have had an event in the past 30 days? – fubar Aug 25 '20 at 05:12
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Aug 25 '20 at 06:19

2 Answers2

-2

Add condition in where clause

SELECT user_id, device_id, event_name, event_date FROM  user 
WHERE event_date >= DATEADD(day,-30,GETDATE()) 
and   event_date <= getdate()

Or use DateDiff

SELECT user_id, device_id, event_name, event_date FROM  user 
WHERE DATEDIFF(day,event_date,GETDATE()) between 0 and 30
Shwetank
  • 374
  • 1
  • 6
  • 20
  • Thanks. But the issue is that this will get me events between the data range. but how do i exclude the users who were active recently as well? eg - there will be some events between 1 July and 1 Aug. but i need users who don't have any events after 1 Aug – d0rnish Aug 25 '20 at 05:37
  • This answer is for ms sql server and not for mysql. – Shadow Aug 25 '20 at 06:57
-2
<?php
$fromDate = "2020-08-01 00:00:00"; //replace it with starting date in same format
$toDate = "2020-08-10 23:59:59"; //replace it with ending date in same format

//replace with your table name and make
//make sure datatype of event_date column must be "DATETIME" in sql database

$query = "SELECT * FROM EVENT_TABLE_NAME where event_date >= $fromDate and event_date <= $toDate";

You can execute this query and you will get list of records which are occurred in given date range.

Vishal Rambhiya
  • 741
  • 6
  • 10
  • Thank you. but how do i exclude the users who were active recently as well? eg - there will be some events between 1 July and 1 Aug. but i need users who don't have any events after 1 Aug – d0rnish Aug 25 '20 at 05:38
  • 1
    It seems inappropriate to provide a solution in an untagged language, unless previously discussed in comments. In this instance, the introduction of application code is entirely unnecessary. – Strawberry Aug 25 '20 at 06:48
  • Please write your question clearly. You have not mentioned that scenario in question. – Vishal Rambhiya Aug 27 '20 at 04:58