从天气数据中找出是否有热带夜晚

Question

我有很多天气数据，其中每小时都有温度。现在我想知道上个月是否有一个热带夜晚。

热带夜的定义：

"热带夜晚是指在下午6点到早上6点之间，最低气温不低于20°C的夜晚；

要想知道热带夜晚是什么时候，我必须在两天内检查温度(从第1天下午6点到第2天早上6点(。那么我如何在MySLQ中达到这一点呢？

表格如下：

CREATE TABLE `temperature` (
`id` int(11) NOT NULL,
`location` int(11) NOT NULL,
`temperature` double NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `temperature` (`id`, `location`, `temperature`, `timestamp`) VALUES
(1, 1, 19.1, '2020-07-24 18:00:02'),
(2, 1, 17.8, '2020-07-24 19:00:02'),
(3, 1, 16.4, '2020-07-24 20:00:02'),
(4, 1, 16.2, '2020-07-24 21:00:02'),
(5, 1, 16.2, '2020-07-24 22:00:02'),
(6, 1, 15.7, '2020-07-24 23:00:02'),
(7, 1, 15.4, '2020-07-25 00:00:02'),
(8, 1, 15.5, '2020-07-25 01:00:02'),
(9, 1, 15.9, '2020-07-25 02:00:02'),
(10, 1, 15.5, '2020-07-25 03:00:01'),
(11, 1, 15, '2020-07-25 04:00:02'),
(12, 1, 14.4, '2020-07-25 05:00:02'),
(13, 1, 16.5, '2020-07-25 06:00:01'),
(14, 1, 24.8, '2020-07-25 07:00:01'),
(15, 1, 26.7, '2020-07-25 08:00:02'),
(16, 1, 32.5, '2020-07-25 09:00:02'),
(17, 1, 30.3, '2020-07-25 10:00:02'),
(18, 1, 30.7, '2020-07-25 11:00:02'),
(19, 1, 29.3, '2020-07-25 12:00:02'),
(20, 1, 30.4, '2020-07-25 13:00:01'),
(21, 1, 27.8, '2020-07-25 14:00:02'),
(22, 1, 27.1, '2020-07-25 15:00:02'),
(23, 1, 28, '2020-07-25 16:00:02'),
(24, 1, 27.1, '2020-07-25 17:00:02'),
(25, 1, 27.6, '2020-07-25 18:00:01'),
(26, 1, 24.1, '2020-07-25 19:00:01'),
(27, 1, 21.7, '2020-07-25 20:00:02'),
(28, 1, 20.7, '2020-07-25 21:00:02'),
(29, 1, 20.3, '2020-07-25 22:00:02'),
(30, 1, 19.2, '2020-07-25 23:00:02'),
(31, 1, 18.8, '2020-07-26 00:00:02'),
(32, 1, 18.9, '2020-07-26 01:00:02'),
(33, 1, 18.6, '2020-07-26 02:00:02'),
(34, 1, 18.6, '2020-07-26 03:00:02'),
(35, 1, 18.7, '2020-07-26 04:00:01'),
(36, 1, 18, '2020-07-26 05:00:02'),
(37, 1, 18.3, '2020-07-26 06:00:02'),
(38, 1, 25.4, '2020-07-26 07:00:02'),
(39, 1, 26.3, '2020-07-26 08:00:02'),
(40, 1, 32.7, '2020-07-26 09:00:02'),
(41, 1, 31.2, '2020-07-26 10:00:01'),
(42, 1, 31.2, '2020-07-26 11:00:02'),
(43, 1, 29.1, '2020-07-26 12:00:02'),
(44, 1, 27.9, '2020-07-26 13:00:02'),
(45, 1, 27.2, '2020-07-26 14:00:02'),
(46, 1, 28.6, '2020-07-26 15:00:02'),
(47, 1, 28.5, '2020-07-26 16:00:02'),
(48, 1, 26.3, '2020-07-26 17:00:02'),
(49, 1, 26, '2020-07-26 18:00:02'),
(50, 1, 24.5, '2020-07-26 19:00:02'),
(51, 1, 21.2, '2020-07-26 20:00:02'),
(52, 1, 20.4, '2020-07-26 21:00:02'),
(53, 1, 19.7, '2020-07-26 22:00:02'),
(54, 1, 18.3, '2020-07-26 23:00:02'),
(55, 1, 17.9, '2020-07-27 00:00:02'),
(56, 1, 16.9, '2020-07-27 01:00:02'),
(57, 1, 16.3, '2020-07-27 02:00:01'),
(58, 1, 16, '2020-07-27 03:00:02'),
(59, 1, 15.7, '2020-07-27 04:00:02'),
(60, 1, 15.6, '2020-07-27 05:00:02'),
(61, 1, 16.2, '2020-07-27 06:00:02'),
(62, 1, 29.8, '2020-07-27 07:00:02'),
(63, 1, 32.4, '2020-07-27 08:00:02'),
(64, 1, 37.9, '2020-07-27 09:00:02'),
(65, 1, 35.1, '2020-07-27 10:00:02'),
(66, 1, 33.1, '2020-07-27 11:00:02'),
(67, 1, 34.5, '2020-07-27 12:00:02'),
(68, 1, 32, '2020-07-27 13:00:02'),
(69, 1, 29.5, '2020-07-27 14:00:02'),
(70, 1, 29.7, '2020-07-27 15:00:02'),
(71, 1, 31, '2020-07-27 16:00:01'),
(72, 1, 30.8, '2020-07-27 17:00:02'),
(73, 1, 30.4, '2020-07-27 18:00:02'),
(74, 1, 29.6, '2020-07-27 19:00:02'),
(75, 1, 26.1, '2020-07-27 20:00:01'),
(76, 1, 25.3, '2020-07-27 21:00:02'),
(77, 1, 23.7, '2020-07-27 22:00:02'),
(78, 1, 22.8, '2020-07-27 23:00:02'),
(79, 1, 23, '2020-07-28 00:00:02'),
(80, 1, 22.9, '2020-07-28 01:00:02'),
(81, 1, 22.9, '2020-07-28 02:00:01'),
(82, 1, 22.2, '2020-07-28 03:00:02'),
(83, 1, 22.3, '2020-07-28 04:00:02'),
(84, 1, 21.5, '2020-07-28 05:00:02'),
(85, 1, 22.1, '2020-07-28 06:00:02'),
(86, 1, 32.8, '2020-07-28 07:00:02'),
(87, 1, 33.4, '2020-07-28 08:00:02'),
(88, 1, 36.2, '2020-07-28 09:00:02'),
(89, 1, 37, '2020-07-28 10:00:02'),
(90, 1, 31.3, '2020-07-28 11:00:02'),
(91, 1, 34.2, '2020-07-28 12:00:01'),
(92, 1, 33.9, '2020-07-28 13:00:02'),
(93, 1, 33.3, '2020-07-28 14:00:02'),
(94, 1, 31.6, '2020-07-28 15:00:02'),
(95, 1, 32.3, '2020-07-28 16:00:02'),
(96, 1, 29.2, '2020-07-28 17:00:02'),
(97, 1, 28.5, '2020-07-28 18:00:02'),
(98, 1, 26.7, '2020-07-28 19:00:01'),
(99, 1, 24.4, '2020-07-28 20:00:01'),
(100, 1, 21.9, '2020-07-28 21:00:02'),
(101, 1, 22.9, '2020-07-28 22:00:02'),
(102, 1, 22.5, '2020-07-28 23:00:01'),
(103, 1, 21.9, '2020-07-29 00:00:02'),
(104, 1, 21.2, '2020-07-29 01:00:02'),
(105, 1, 20.5, '2020-07-29 02:00:01'),
(106, 1, 19.7, '2020-07-29 03:00:02'),
(107, 1, 19, '2020-07-29 04:00:02'),
(108, 1, 18.8, '2020-07-29 05:00:02'),
(109, 1, 19, '2020-07-29 06:00:01'),
(110, 1, 18.8, '2020-07-29 07:00:02'),
(111, 1, 18.9, '2020-07-29 08:00:02');

_{在我的数据库数据中，从2020-07-27(下午6点(到2020-07-28(早上6点(的一个晚上是热带之夜}

为了显示数据库中的数据，我创建了一个SQLFIDDLE。

我想要的结果：

我最喜欢的声明是，每月显示热带之夜的日期：

2020-07-27至2020-07-28的夜晚是热带夜晚，最低温度为21.5°C

因此，我将输出热带夜晚所在的日期和该夜晚的最低温度。

但我不知道如何从每月每天下午6点到第二天早上6点检查体温。

谢谢你的帮助！

Answer 1

通常，您会在应用程序代码中做演示，但只是为了好玩…：

SELECT CONCAT_WS(' '
,'The night from'
,DATE(timestamp)
,'to'
,DATE(timestamp+INTERVAL 1 DAY)
,'was a tropical night with a min temperature from'
,CONCAT(MIN(temperature),'°C')
) x 
FROM temperature 
WHERE TIME(timestamp) NOT BETWEEN '06:00:01' AND '17:59:59' 
GROUP 
BY DATE(timestamp+INTERVAL 12 HOUR) 
HAVING MIN(temperature) >= 20;
+-------------------------------------------------------------------------------------------------+
| x                                                                                               |
+-------------------------------------------------------------------------------------------------+
| The night from 2020-07-27 to 2020-07-28 was a tropical night with a min temperature from 21.5°C |
+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在MySQL的最新版本中，可能需要禁用严格模式才能正常工作。

Answer 2

select
location,
date(date_sub(`timestamp`, interval 7 hour)) tropical_night,
min(temperature) min_tempurature
from temperature
where time(`timestamp`) not between '6:00:01' and '17:59:59'
group by location, tropical_night
having min_tempurature >= 20

我不清楚月份是如何影响的，也许你需要添加一个where条件。