SQL allows for various client visits with working hours from 10 to 6 hours

Question

I write customer loyalty software for the club, which opens from 10 AM to 6 AM every day. The data is stored in MYSQL, and I would like to calculate the total number of customer visits per month.

I use an account (excellent (date)), but if the player arrived at 5 pm and stayed until 3 in the morning with 2 transactions at 22:00 and 2:00. It will be counted as 2 visits instead of 1.

I have a transaction table with the columns listed below:

ps: everything in brackets () is not real data. I get about 2000 transactions per day. I can also change the structure of the table

  Transaction_ID |  Date (not Date / Time) |  Customer_ID |  Item |  price |  timestamp
    1 |  11-06-2015 (6pm) |  Jane |  drink |  2.00 |  156165166
    2 |  06-06-2015 (2pm) |  Jane |  drink |  2.00 |  1433858493
    3 |  06-06-2015 (3am) |  Jane |  drink |  2.00 |  1433906073
    4 |  06-06-2015 (6pm) |  Jane |  drink |  2.00 |  156165166

The current code returns {4, Jane}. The answer I'm looking for is {3, Jane}. Transaction {2,3} should be considered as one visit

SELECT count(distinct(Date)) as visit, Customer_ID FROM transaction GROUP BY Customer_ID WHERE timestamp BETWEEN $timestamp1 AND $timestamp2 

  $ timestamp1 = strtotime ("first day of february + 10am");
 $ timestamp2 = strtotime ("first day of march + 6am"); 

How do you suggest accurately calculating the total number of visits below? I can change the structure of the table from date to date / time.

The easiest answer with minimal changes to my codes.

  SELECT count (DISTINCT (DATE (DATE_SUB (from_unixtime (timestamp), INTERVAL 6 HOUR))) as visit, Customer_ID 
 FROM transaction  
 GROUP BY Customer_ID 
 WHERE timestamp BETWEEN $ timestamp1 AND $ timestamp2

Answer 1

The easiest way is to move the datetime ( date , timestamp ?) Field for 6 hours in the SQL query, and then you will get the interval for one day from 4 AM to 12 PM:

 DISTINCT(DATE(DATE_SUB(dt,INTERVAL 6 HOUR))) 

SQLFiddle demo

Answer 2

Here is the code you need:

 SELECT Customer_ID 'Customer ID' , COUNT(DISTINCT visit) as 'Visits per month' , MONTH(visit) 'Month' , YEAR(visit) 'Year' FROM (SELECT * , CASE WHEN (t_timestamp > Date_StartDate AND t_timestamp < Date_EndDate) THEN d_date WHEN (t_timestamp < Date_StartDate) THEN date_add(d_date, INTERVAL -1 DAY) END 'visit' FROM (SELECT * , DATE_ADD(CAST(d_date AS DATETIME), INTERVAL 10 HOUR) Date_StartDate , DATE_ADD(DATE_ADD(cast(d_date AS DATETIME), INTERVAL 6 HOUR), INTERVAL 1 DAY) Date_EndDate FROM transactions) Results ) Results GROUP BY customer_id, month(visit), year(visit) 

Also here is an SQLFiddle with code results.

I did not use the exact format for your Customer_ID (I used INTEGER instead of VARCHAR) and did not use the exact dates that you used in your example, but obviously it should work for everything.

Consider setting the column names used in my query to the appropriate column names, and you should be fine.