I have a lot of weather data, where I have the temperature every hour. Now I want to find out whether there was a tropical night in the whole last month.
Definition from tropic night:
"A tropical night is a night in which the lowest air temperature does not drop below 20°C between 6pm and 6am"
To find out when a tropic night was, I have to check the temperature over 2 days (from 6pm from day 1 to 6am from day 2). So how can I reach this in MySLQ?
The table looks like follow:
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');
Just one night from 2020-07-27 (6pm) to 2020-07-28 (6am) was a tropic night in the data from my database.
To show the data from my database, I have create a SQLFIDDLE.
My favourite statement where, do show the date from each tropical night per month:
The night from 2020-07-27 to 2020-07-28 was a tropical night with a min temperature from 21.5°C.
So I will as output the both dates where the tropical night is and the min temperature in this night.
But I don't know how can I check from each day per month at 6pm to each next day at 6am to check the temperature.
Thanks for any helpful answer!
Normally, you'd do the presentation stuff in application code, but just for fun...:
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)
In more recent versions of MySQL, it's possible that strict mode needs to be disabled for this to work.