在特定时间内获取数据
问题描述:
我有一个考勤表名出勤,可能问题是如何获得student_no,其中时间等于早上(0:00 - > 11:59 PM),或者在下午等于( 12:00 - > 11:59 PM)在特定时间内获取数据
CREATE TABLE IF NOT EXISTS `attendance` (
`ATTENDANCE_NO` int(10) NOT NULL AUTO_INCREMENT,
`A_DATE` date NOT NULL,
`TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`JOB_SERVICE` varchar(255) NOT NULL,
`ATTENDER_NO` int(10) NOT NULL,
`STUDENT_NO` varchar(20) NOT NULL,
`ATTENDANCELAT` varchar(255) NOT NULL,
`ATTENDANCELONG` varchar(255) NOT NULL,
PRIMARY KEY (`ATTENDANCE_NO`),
KEY `STUDENT_NO` (`STUDENT_NO`),
KEY `ATTENDER_NO` (`ATTENDER_NO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `attendance`
--
INSERT INTO `attendance` (`ATTENDANCE_NO`, `A_DATE`, `TIME`, `JOB_SERVICE`, `ATTENDER_NO`, `STUDENT_NO`, `ATTENDANCELAT`, `ATTENDANCELONG`) VALUES
(2, '2014-07-24', '2014-07-24 01:00:00', 'PICKUP', 3, 'S123456789', '13.624004', '123.194269'),
(3, '2014-07-24', '2014-07-23 18:25:13', 'DROPOFF', 3, 'S123456789', '13.6295657', '123.184346'),
(4, '2014-07-24', '2014-07-23 17:28:09', 'PICKUP', 3, 'S234567890', '13.625985', '123.191780'),
(5, '2014-07-24', '2014-07-23 18:27:28', 'DROPOFF', 3, 'S234567890', '13.6295657', '123.184346');
任何帮助,将不胜感激。提前致谢。
答
试试这个。我希望这能帮到您。
select * from attendance;
#morning time
select * from attendance where
HOUR(`TIME`) < 12;
#after noon time
select * from attendance where
HOUR(`TIME`) >= 12;
SQLFiddle链接:http://sqlfiddle.com/#!2/2a41da/7
也请参阅日期为MySQL的所有功能:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
+1
谢谢,它真的帮助我。 – hPys
+0
欢迎@ hPys ..! :) –
你应该给sqlFiddle链接是这样的:http://sqlfiddle.com/#!2/2a41da –
肯定,http://sqlfiddle.com/#!2/2a41da/2 – hPys