Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Stackoverflow Problem Solve : how to write a mysql query based on the output?
INSERT INTO master_tab (sheetno, time_stamp) VALUES (101,'2018-05-27 11:16:58'), (103,'2018-05-27 11:20:18'), (102,'2018-05-27 11:24:39'), (105,'2018-05-27 11:27:44'), (106,'2018-05-27 11:34:03'), (107,'2018-05-27 11:51:32'), (108,'2018-05-27 12:41:34'), (109,'2018-05-27 12:48:13'), (110,'2018-05-27 12:52:40'), (111,'2018-05-27 13:18:59'), (112,'2018-05-27 14:13:07'), (113,'2018-05-27 14:18:04'), (114,'2018-05-27 14:21:12'), (115,'2018-05-27 14:54:01'), (117,'2018-05-27 14:55:14'), (118,'2018-05-27 14:57:31'), (119,'2018-05-27 15:05:30'), (120,'2018-05-27 15:30:44'), (121,'2018-05-27 15:46:38'), (122,'2018-05-27 16:02:16'), (123,'2018-05-28 11:32:10'), (124,'2018-05-28 11:38:18'), (125,'2018-05-28 11:49:45'), (126,'2018-05-28 11:52:09'), (127,'2018-05-28 12:21:00'), (128,'2018-05-28 12:24:33'), (129,'2018-05-28 13:04:56'), (130,'2018-05-28 14:10:07'), (140,'2018-05-28 14:18:47'), (141,'2018-05-28 14:22:09'), (142,'2018-05-28 14:28:04'), (143,'2018-05-28 14:37:53'), (144,'2018-05-28 15:06:27'), (145,'2018-05-28 15:33:05'), (146,'2018-05-28 15:35:44'), (147,'2018-05-28 15:53:41'), (148,'2018-05-28 16:13:16'), (149,'2018-05-28 16:27:51'), (150,'2018-05-28 16:38:54'), (151,'2018-05-28 16:44:54'); select * from master_tab; SELECT DATE_FORMAT(MIN(time_stamp), '%Y-%m-%d') AS min, DATE_FORMAT(MAX(time_stamp), '%Y-%m-%d') AS max, COUNT(DISTINCT DATE(time_stamp)) AS cnt, COUNT(DISTINCT CONCAT(DATE(time_stamp),(CASE WHEN TIME(time_stamp) < '12:00:00' THEN 1 END))) AS FN, COUNT(DISTINCT CONCAT(DATE(time_stamp),(CASE WHEN TIME(time_stamp) >= '12:00:00' THEN 1 END))) AS VA FROM master_tab;
run
|
edit
|
history
|
help
0
Umang Khambhalikar
Srinivas
list with indented categories
proyecto
test
Srinivas
MySQL JSON_SEARCH boolean Issue
string time compare
lab_dop_for_mysql
poi