SQL count distinct visits of a customer with opening hours from 10am to 6am

Question

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.

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.

I have a transaction table with the columns listed below:

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

 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

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 to accurately count the total visits below? I am able to change the table structure from Date to Date/time.

The easiest answer with least 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 shift your datetime (date,timestamp?) field back for 6 hours in a SQL statement and then you will get an interval in one day from 4AM to 12PM:

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

SQLFiddle demo