title: SQL count distinct visits of a customer with opening hours from 10am to 6am
body: <p>I am writing a customer loyalty software for a club that opens from 10am to 6am everyday. The data is store in MYSQL and I'd like to count the customer's total visits for the month. </p>
  
  <p>I am using count(distinct(date)) but if the player came at 5pm and stayed till 3am with 2 transactions at 10pm and 2am. It will be counted as 2 visits instead of 1. </p>
  
  <p>I have a transaction table with the columns listed below:</p>
  
  <blockquote>
    <p>ps: anything in the brackets () is not real data. I get about 2000 transactions a day. I am also able to change the table structure </p>
  
  <pre> Transaction_ID | Date(not Date/Time) | Customer_ID | Item | price | timestamp
     1            | 11-06-2015    (6pm) | Jane        | drink| 2.00  | 156165166
     2            | 09-06-2015    (2pm) | Jane        | drink| 2.00  | 1433858493
     3            | 10-06-2015    (3am) | Jane        | drink| 2.00  | 1433906073
     4            | 06-06-2015    (6pm) | Jane        | drink| 2.00  | 156165166
  </pre>
  </blockquote>
  
  <p>Current code returns {4, Jane}. The answer I'm looking for is {3,Jane}. Transaction {2,3} should be considered as one visit</p>
  
  <pre><code>SELECT count(distinct(Date)) as visit, Customer_ID 
  FROM transaction  
  GROUP BY Customer_ID 
  WHERE timestamp BETWEEN $timestamp1 AND $timestamp2
  </code></pre>
  
  <pre>$timestamp1 = strtotime("first day of february +10am");
  $timestamp2 = strtotime("first day of march +6am");</pre> 
  
  <p>How do you suggest to accurately count the total visits below? I am able to change the table structure from Date to Date/time.</p>
  
  <blockquote>
    <p>The easiest answer with least changes to my codes.</p>
  
  <pre>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
  
  </pre>
  </blockquote>