MySQL Query在DateTime之间自定义

Question

I'm in need of your help. What I'm trying to achieve is the following:

Obtain both the withdrawal and deposit profit, for each day, for the past week.

So I'm hoping to get rows with the values: Day, Deposit Profit, Withdrawal Profit. The catch however is that a day is a custom day, meaning: A day is between yyyy-mm-dd 13:00:00 and yyyy-mm-dd 13:00:00. So a group by date wouldn't be sufficient.

The query I've tried experimenting with was:

SELECT submit_date, 
MAX(deposit_amount) - MIN(deposit_amount) AS deposit, 
SUM(withdrawal_amount * withdrawal_percentage) as withdrawal 
FROM `pro_Profits` 
WHERE account_id = '{C795E1D2-452A-DEE8-A800-02E94332114A}' 
AND submit_datetime >= NOW() - INTERVAL 1 WEEK 
GROUP BY submit_date 
ORDER BY `submit_datetime` DESC  

Table:

  CREATE TABLE IF NOT EXISTS `pro_Profits` (
  `id` varchar(512) NOT NULL,
  `account_id` varchar(512) NOT NULL,
  `submit_date` date NOT NULL,
  `submit_time` time NOT NULL,
  `submit_datetime` datetime NOT NULL,
  `deposit_amount` bigint(20) NOT NULL,
  `withdrawal_amount` bigint(20) NOT NULL,
  `deposit_percentage` double NOT NULL DEFAULT '1',
  `withdrawal_percentage` double NOT NULL DEFAULT '0.4',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `id_2` (`id`),
  KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

What you basically need to do is shift the day by 13 hours. You can use a function for this in MySQL:

TIMESTAMPDIFF(HOUR,13,submit_date)

In your SQL query this would look something like this:

SELECT 
  TIMESTAMPDIFF(HOUR,13,submit_date) as shifted_submit_date, 
  MAX(deposit_amount)-MIN(deposit_amount) AS deposit, 
  SUM(withdrawal_amount*withdrawal_percentage) as withdrawal 
FROM 
  pro_Profits 
WHERE 
  account_id = '{C795E1D2-452A-DEE8-A800-02E94332114A}' AND 
  submit_datetime >= NOW()-INTERVAL 1 WEEK 
GROUP BY 
  shifted_submit_date 
ORDER BY 
  submit_datetime DESC  

A bit of experimenting might be needed to get exactly what you want. I find it strange that you group by one thing, and order by another.