SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    SQL query for time()

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

    Code:
    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:

    Code:
    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

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2010
    Location
    Netherlands
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code MySQL:
    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.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    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
    Code:
    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;

  4. #4
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I try this pair of queries for each of the four time bands:

    Code:
    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:
    Code:
    05:55 XP6020039
    05:04 XI6041309
    01:36 XP6024030
    00:40 XY1010302
    00:09 XZ2049408
    5
    I need four queries ?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •