php - MySQL 自定义日期时间之间的查询

Question

我需要你的帮助。我想要实现的目标如下:

获取过去一周每天的取款和存款利润。

所以我希望获得具有以下值的行:天数、存款利润、取款利润。但问题是一天是自定义日期，这意味着:一天是在 yyyy-mm-dd 13:00:00 和 yyyy-mm-dd 13:00:00 之间。因此按日期分组是不够的。

我尝试尝试的查询是:

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  

表:

  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

您基本上需要做的就是将日期平移 13 个小时。您可以在 MySQL 中使用一个函数来实现此目的:

TIMESTAMPDIFF(HOUR,13,submit_date)

在您的 SQL 查询中，这看起来像这样:

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  

可能需要进行一些试验才能得到您想要的结果。我觉得很奇怪，你按一件事进行分组，然后按另一件事进行排序。