SQL query for time()

Hello everyone, I need your help.
I have this table in my db mysql:


ID	DATE_E		TIME_E	LZT		P
15581	2010-05-31	22:32	XP6038165	1
15577	2010-05-31	18:45	XZ4027509	1
15573	2010-05-31	18:18	XZ6040802	1
15571	2010-05-31	18:10	XP6050330	1
15576	2010-05-31	18:02	XP4026160	1
15570	2010-05-31	17:53	XZ7041132	1
15563	2010-05-31	16:06	XY1002807	1
15575	2010-05-31	16:06	XI6026614	1
15569	2010-05-31	15:54	XY1040502	1
15566	2010-05-31	15:10	XY1026711	1
15567	2010-05-31	14:56	XY1004701	1
15565	2010-05-31	14:49	XP4064152	1
15558	2010-05-31	14:48	XY1046808	1
15562	2010-05-31	14:34	XZ2049504	1
15564	2010-05-31	14:11	XY1032007	1
15559	2010-05-31	13:22	XZ7046310	1
15585	2010-05-31	13:19	XZ4043910	1
15555	2010-05-31	13:18	XZ4043908	1
15554	2010-05-31	13:17	XY1005701	1
15584	2010-05-31	13:13	XP6023146	1
15560	2010-05-31	13:12	XZ7046310	1
15561	2010-05-31	12:31	XZ7038102	1
15553	2010-05-31	12:26	XY1033901	1
15557	2010-05-31	12:26	XP6004124	1
15552	2010-05-31	11:59	XY1003407	1
15549	2010-05-31	11:31	XY1004307	1
15548	2010-05-31	11:28	XY1007204	1
15551	2010-05-31	10:52	XY1006507	1
15547	2010-05-31	10:07	XY1046809	1
15556	2010-05-31	10:01	XZ7057411	1
15545	2010-05-31	08:16	XZ4043601	1
15546	2010-05-31	06:59	XI5023208	1
15543	2010-05-31	06:33	XP4055050	1
15541	2010-05-31	05:55	XP6020039	1
15550	2010-05-31	05:04	XI6041309	1
15536	2010-05-31	01:36	XP6024030	1
15535	2010-05-31	00:40	XY1010302	1
15537	2010-05-31	00:09	XZ2049408	1

I need this output:


strTime1_00h-06h	LZT	
05:55		XP6020039	
05:04		XI6041309	
01:36		XP6024030	
00:40		XY1010302	
00:09		XZ2049408	
TotalstrTime1 = 5
		
strTime2_06h-12h	LZT	
11:59		XY1003407	
11:31		XY1004307	
11:28		XY1007204	
10:52		XY1006507	
10:07		XY1046809	
10:01		XZ7057411	
08:16		XZ4043601	
06:59		XI5023208	
06:33		XP4055050	
TotalstrTime2 = 9
		
strTime3_12h-18h	LZT		
17:53		XZ7041132	
16:06		XY1002807	
16:06		XI6026614	
15:54		XY1040502	
15:10		XY1026711	
14:56		XY1004701	
14:49		XP4064152	
14:48		XY1046808	
14:34		XZ2049504	
14:11		XY1032007	
13:22		XZ7046310	
13:19		XZ4043910	
13:18		XZ4043908	
13:17		XY1005701	
13:13		XP6023146	
13:12		XZ7046310	
12:31		XZ7038102	
12:26		XY1033901	
12:26		XP6004124
TotalstrTime3 = 19
		
strTime4_18h-00h	LZT		
22:32		XP6038165	
18:45		XZ4027509	
18:18		XZ6040802	
18:10		XP6050330	
18:02		XP4026160		
TotalstrTime4 = 5

Any help would be very much appreciated and many thanks to any who can help me.

Thanks in advance.
Chevy

I try this pair of queries for each of the four time bands:


SELECT 
   TIME_FORMAT(`TIME_E`, '%H:%i') AS 'strTime1_00h-06h', `LZT`
   FROM tbl_1
      WHERE 
          TIME(`TIME_E`) BETWEEN '00' AND '06'
ORDER BY TIME_E DESC;
SELECT Count(*) FROM tbl_1  
   WHERE TIME_E
      BETWEEN '00' AND '06'; 

And this is the output:


05:55 XP6020039
05:04 XI6041309
01:36 XP6024030
00:40 XY1010302
00:09 XZ2049408
5

I need four queries ?

First of all, it looks like your time field does not use the mysql datatype ‘TIME’, which it really should. When it is a ‘TIME’-type field, you can use queries like this to get the wanted results:

SELECT TIME_FORMAT(`TIME_E`, '%H:%i') AS 'strTime1_00h-06h', `LZT` FROM `table` WHERE TIME(`TIME_E`) BETWEEN '00' AND '06';

Basically you select any row that has a time between X and Y (in this case 00 and 06) with this query. The TIME_FORMAT outputs the time in given format, in this case only hours and minutes should be displayed. There are also seconds in a TIME-field, but we do not need those, so that’s why the TIME_FORMAT is being applied.

Many thanks for your reply, but I don’t understand: I need four queries for my output or I can do only query for output?

SHOW CREATE TABLE


DROP TABLE IF EXISTS `tbl_1`;
CREATE TABLE `tbl_1` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `DATE_E` date DEFAULT NULL,
  `TIME_E` time DEFAULT NULL,
  `LZT` varchar(255) DEFAULT NULL,
  `P` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

INSERT statements for the dataset


LOCK TABLES `tbl_1` WRITE;
INSERT INTO `tbl_1` VALUES (1,'2010-05-31','22:32:00','XP6038165',1);
INSERT INTO `tbl_1` VALUES (2,'2010-05-31','18:45:00','XZ4027509',1);
INSERT INTO `tbl_1` VALUES (3,'2010-05-31','18:18:00','XZ6040802',1);
INSERT INTO `tbl_1` VALUES (4,'2010-05-31','18:10:00','XP6050330',1);
INSERT INTO `tbl_1` VALUES (5,'2010-05-31','18:02:00','XP4026160',1);
INSERT INTO `tbl_1` VALUES (6,'2010-05-31','17:53:00','XZ7041132',1);
INSERT INTO `tbl_1` VALUES (7,'2010-05-31','16:06:00','XY1002807',1);
INSERT INTO `tbl_1` VALUES (8,'2010-05-31','16:06:00','XI6026614',1);
INSERT INTO `tbl_1` VALUES (9,'2010-05-31','15:54:00','XY1040502',1);
INSERT INTO `tbl_1` VALUES (10,'2010-05-31','15:10:00','XY1026711',1);
INSERT INTO `tbl_1` VALUES (11,'2010-05-31','14:56:00','XY1004701',1);
INSERT INTO `tbl_1` VALUES (12,'2010-05-31','14:49:00','XP4064152',1);
INSERT INTO `tbl_1` VALUES (13,'2010-05-31','14:48:00','XY1046808',1);
INSERT INTO `tbl_1` VALUES (14,'2010-05-31','14:34:00','XZ2049504',1);
INSERT INTO `tbl_1` VALUES (15,'2010-05-31','14:11:00','XY1032007',1);
INSERT INTO `tbl_1` VALUES (16,'2010-05-31','13:22:00','XZ7046310',1);
INSERT INTO `tbl_1` VALUES (17,'2010-05-31','13:19:00','XZ4043910',1);
INSERT INTO `tbl_1` VALUES (18,'2010-05-31','13:18:00','XZ4043908',1);
INSERT INTO `tbl_1` VALUES (19,'2010-05-31','13:17:00','XY1005701',1);
INSERT INTO `tbl_1` VALUES (20,'2010-05-31','13:13:00','XP6023146',1);
INSERT INTO `tbl_1` VALUES (21,'2010-05-31','13:12:00','XZ7046310',1);
INSERT INTO `tbl_1` VALUES (22,'2010-05-31','12:31:00','XZ7038102',1);
INSERT INTO `tbl_1` VALUES (23,'2010-05-31','12:26:00','XY1033901',1);
INSERT INTO `tbl_1` VALUES (24,'2010-05-31','12:26:00','XP6004124',1);
INSERT INTO `tbl_1` VALUES (25,'2010-05-31','11:59:00','XY1003407',1);
INSERT INTO `tbl_1` VALUES (26,'2010-05-31','11:31:00','XY1004307',1);
INSERT INTO `tbl_1` VALUES (27,'2010-05-31','11:28:00','XY1007204',1);
INSERT INTO `tbl_1` VALUES (28,'2010-05-31','10:52:00','XY1006507',1);
INSERT INTO `tbl_1` VALUES (29,'2010-05-31','10:07:00','XY1046809',1);
INSERT INTO `tbl_1` VALUES (30,'2010-05-31','10:01:00','XZ7057411',1);
INSERT INTO `tbl_1` VALUES (31,'2010-05-31','08:16:00','XZ4043601',1);
INSERT INTO `tbl_1` VALUES (32,'2010-05-31','06:59:00','XI5023208',1);
INSERT INTO `tbl_1` VALUES (33,'2010-05-31','06:33:00','XP4055050',1);
INSERT INTO `tbl_1` VALUES (34,'2010-05-31','05:55:00','XP6020039',1);
INSERT INTO `tbl_1` VALUES (35,'2010-05-31','05:04:00','XI6041309',1);
INSERT INTO `tbl_1` VALUES (36,'2010-05-31','01:36:00','XP6024030',1);
INSERT INTO `tbl_1` VALUES (37,'2010-05-31','00:40:00','XY1010302',1);
INSERT INTO `tbl_1` VALUES (38,'2010-05-31','00:09:00','XZ2049408',1);
UNLOCK TABLES;