我有一个运行良好的mysql查询,但我需要一些选项或过滤器。此查询统计第一个日期和结束日期之间的所有小时数。
SELECT hour(timediff('2013-11-26 23:51:20',
'2013-11-28 15:51:10'))
AS totalhour
----
totalhour
39
有可能只得到08h-17h之间以及周一至周五之间的小时数的总和?
模式如下:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE latin1_general_ci NOT NULL,
`first_date` date NOT NULL,
`last_date` date NOT NULL,
`begin_hour` time NOT NULL,
`end_hour` time NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
/*Data for the table `test` */
insert into `test`(`id`,`title`,`first_date`,`last_date`,`begin_hour`,`end_hour`) values
(1,'Test1','2013-11-26','2013-11-28','06:00:20','20:00:20'),
(2,'Test2','2013-11-29','2013-12-03','09:00:10','15:51:10');
就像那样从2013-11-29"09:00:10"到"2013-12-03"15:00:00"=23小时
提前Thanx
是的,您可以
SELECT hour(timediff('2013-11-26 23:51:20',
'2013-11-28 15:51:10'))
AS totalhour
... your remaing query
WHERE HOUR(dat_time_col) BETWEEN '8' AND '17' AND DAYNAME(dat_time_col)
IN('Monday','Tuesday','Wednesday','Thursday','Friday')
dat_time_col
这将是您的日期-时间列名称,并确保您对此列有正确的数据类型
小时(日期)
DAYNAME(日期)
请参阅fiddle