MYSQL, сравнивая время и день недели одновременно

Question

MYSQL, сравнивая время и день недели одновременно

У меня есть следующая таблица в базе данных MYSQL.

CREATE TABLE IF NOT EXISTS `wp_my_default_settings` (
`ds_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`whichday` varchar(10) NOT NULL,
`tillday` varchar(10) NOT NULL,
`start_time` varchar(10) NOT NULL,
`end_time` varchar(10) NOT NULL,
`max_no_msg` int(11) NOT NULL,
`created_date` datetime NOT NULL,
PRIMARY KEY (`ds_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `wp_my_default_settings`
--

INSERT INTO `wp_my_default_settings` (`ds_id`, `user_id`, `whichday`,         `tillday`, `start_time`, `end_time`, `max_no_msg`, `created_date`) VALUES
(10, 11, 'Thu', 'Fri', '11:57 PM', '01:54 PM', 22, '2015-06-05 13:56:04'),
(9, 11, 'Thu', 'Thu', '12:06 AM', '02:46 PM', 20, '2015-06-05 13:56:04'),
(8, 11, 'Wed', 'Wed', '05:11 AM', '12:47 PM', 34, '2015-06-05 13:56:04'),
(7, 11, 'Tue', 'Tue', '10:02 AM', '05:14 PM', 50, '2015-06-05 13:56:04'),
(6, 11, 'Sun', 'Sun', '06:30 PM', '06:30 PM', 40, '2015-06-05 13:56:04');

У меня есть время, указанное в пт 7:16

Я хочу сформулировать запрос, выбрав ту строку, которая имеет 'Fri 7:16' BETWEEN CONCAT( CONCAT( whichday, ' ' ) , start_time ) AND CONCAT( CONCAT( tillday, ' ' ) , end_time )
Мой запрос

   SELECT `ds_id` , `whichday` , `start_time` , `tillday` ,
`end_time` , STR_TO_DATE( CONCAT( CONCAT( whichday, ' ' ) , start_time ) , '%a %h:%i %p' ) AS 'Start',
STR_TO_DATE( CONCAT( CONCAT( tillday, ' ' ) , end_time ) , '%a %h:%i %p' ) AS 'End',
STR_TO_DATE( 'Fri 07:16 AM', '%a %h:%i %p' ) AS 'Current'
FROM `wp_my_default_settings`
WHERE `user_id` = '11'
AND (
STR_TO_DATE( 'Fri 07:16 AM', '%a %h:%i %p' )
BETWEEN STR_TO_DATE( CONCAT( CONCAT( whichday, ' ' ) , start_time ) , '%a %h:%i %p' )
AND STR_TO_DATE( CONCAT( CONCAT( tillday, ' ' ) , end_time ) , '%a %h:%i %p'         )
)
AND `max_no_msg` >= (
SELECT COUNT( `offer_id` )
FROM `wp_user_offers_by_adv`
WHERE `offer_user_id` = '11' )

Вместо того, чтобы ссориться с ds_id=10 Я получаю строки с ds_id 8 а также 9

Пожалуйста помоги

2

datetime-format mysql php

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Проблема в том, что STR_TO_DATE( 'Fri 07:16 AM', '%a %h:%i %p' ) результаты в 0000-00-00 07:16:00 и это происходит со всеми вашими «датами». Это означает, что вы теряете информацию о дне недели.

Вы можете исправить это, заменив день недели на дату. Например.
Ты можешь использовать 2015-06-01 как понедельник и 2015-06-07 как воскресенье

Но многое можно улучшить во всей структуре таблицы.

Храните время в TIME поле.
Это экономит около 6 байт (или 66%) на поле и может упростить запросы.
Сохраните день недели как число (от 1 до 7) в TINYINT поле.
Это экономит вам около 3 байтов (или 75%) и позволяет сделать запрос следующим образом:
```
... WHERE (whichday < input_day
OR whichday = input_day AND start_time <= input_time))
AND
(tillday > input_day
OR tillday = input_day AND end_time >= input_time)))
```
Больше нет необходимости CONCAT а также STR_TO_DATE что означает, что вы можете лучше использовать индексы.
В качестве альтернативы первым двум пунктам вы можете сохранить день и время в DATETIME поле, использующее дату, которая имеет правильный день недели. Если вы поместите все даты в одну и ту же неделю (например, неделю с 2015-06-01 по 2015-06-07), вы можете делать запросы так же, как
```
... WHERE input_date BETWEEN start_datetime AND end_datetime
```

1