使用 BETWEEN 的 MySQL DATE_FORMAT 不起作用

Question

我正在使用两个存储函数，它们可以工作，当放入 SELECT 时，它们返回正确的值，但是当我尝试减少并获取这些值的子集时，我得到一个没有警告的空集。

这是我的 SQL。

SELECT
    DISTINCT
    e.ims_event_id,
    e.name,
    e.begin_time bUTC,
    e.end_time eUTC,
    DATE_FORMAT(DATE_ADD(e.begin_time, INTERVAL ptat_getTZOffset(pp.subject_to_dlst, tz.delta_time, e.begin_time, country) MINUTE),'%H:%i') begin_local,
    DATE_FORMAT(DATE_ADD(e.end_time, INTERVAL ptat_getTZOffset(pp.subject_to_dlst, tz.delta_time, e.end_time, country) MINUTE),'%H:%i') end_local,
    DATE_FORMAT(ptat_getEventHalfPoint(pp.subject_to_dlst, tz.delta_time, e.begin_time, e.duration, country),'%H:%i') begin_local_halfPoint,
    DATE_FORMAT(DATE_ADD(pp.start_time, INTERVAL ptat_getTZOffset(pp.subject_to_dlst, tz.delta_time, pp.start_time, country) MINUTE),'%H:%i') ptat_begin,
    DATE_FORMAT(DATE_ADD(pp.end_time, INTERVAL ptat_getTZOffset(pp.subject_to_dlst, tz.delta_time, pp.end_time, country) MINUTE),'%H:%i') ptat_end 
FROM
    event e
    JOIN service s ON e.service_uid = s.service_uid
    JOIN service_ptc sp ON s.service_uid = sp.service_uid
    JOIN service_primetime_period spp ON s.service_uid = spp.service_uid
    JOIN primetime_period pp ON spp.primetime_period_uid = pp.primetime_period_uid
    JOIN time_zone tz ON pp.time_zone_id = tz.time_zone_id 
    JOIN dlst_info dli ON country = 'US' AND dli.year = DATE_FORMAT(e.begin_time,'%Y')
WHERE
    (POW(2,DATE_FORMAT(DATE_ADD(e.begin_time,INTERVAL tz.delta_time HOUR), '%w'))&pp.weekdays) > 0 AND
    e.end_time > NOW() AND 
    e.begin_time < DATE_ADD(NOW(),INTERVAL 8 DAY) AND
    DATE_FORMAT(ptat_getEventHalfPoint(pp.subject_to_dlst, tz.delta_time, e.begin_time, e.duration, country),'%H:%i') BETWEEN
DATE_FORMAT(DATE_ADD(pp.start_time, INTERVAL ptat_getTZOffset(pp.subject_to_dlst, tz.delta_time, pp.start_time, country) MINUTE),'%H:%i') AND
    DATE_FORMAT(DATE_ADD(pp.end_time, INTERVAL ptat_getTZOffset(pp.subject_to_dlst, tz.delta_time, pp.end_time, country) MINUTE),'%H:%i')
ORDER BY e.begin_time;

添加 WHERE 子句的最后 3 行时，返回零。它们被复制并粘贴到 SELECT 中并且工作得很好。

我真的很困惑!

此外，当使用字符串进行比较时(即 ... = '19:00')，我得到了结果。任何人都知道出了什么问题？

编辑:不包括 where 子句时返回的示例行。

|    370617336 | Dancing With the Stars             | 2012-04-04 05:00:00 | 2012-04-04 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371138764 | Escape Routes                      | 2012-04-08 05:00:00 | 2012-04-08 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371138733 | Who Do You Think You Are?          | 2012-04-07 05:00:00 | 2012-04-07 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371138638 | The Biggest Loser                  | 2012-04-04 05:00:00 | 2012-04-04 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371676424 | Survivor: One World                | 2012-04-05 05:00:00 | 2012-04-05 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371676489 | Undercover Boss                    | 2012-04-07 05:00:00 | 2012-04-07 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371676386 | NCIS                               | 2012-04-04 05:00:00 | 2012-04-04 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371412875 | Bones                              | 2012-04-03 05:00:00 | 2012-04-03 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371413006 | American Idol                      | 2012-04-06 05:00:00 | 2012-04-06 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371413149 | Bones                              | 2012-04-10 05:00:00 | 2012-04-10 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    371413041 | The Finder                         | 2012-04-07 05:00:00 | 2012-04-07 06:00:00 | 19:00       | 20:00     | 19:30                 | 19:00      | 22:00    |
|    370617100 | Once Upon a Time                   | 2012-04-09 05:00:00 | 2012-04-09 06:00:00 | 19:00       | 20:00     | 19:30                 | 18:00      | 22:00    |
|    371138792 | Harry's Law                        | 2012-04-09 05:00:00 | 2012-04-09 06:00:00 | 19:00       | 20:00     | 19:30                 | 18:00      | 22:00    |
|    371676549 | The Amazing Race                   | 2012-04-09 05:00:00 | 2012-04-09 06:00:00 | 19:00       | 20:00     | 19:30                 | 18:00      | 22:00    |

编辑#2:我从所有表中取出所有字段，并将其组合到沙盒中的一个表中。我将我的两个函数都设为静态(以始终假设我们处于夏季夏令时)并添加了示例数据。有用!所以我想我必须回溯，SQL 语法或逻辑并没有错。某些列、某些连接或表失败。我也间歇性地得到这个错误:

Error | 1366 | Incorrect decimal value: '' for column '' at row -1

如果我可以附加一个 SQL 文件，我可以附加我的测试模式，我不想将它粘贴到这里。

Answer 1

我在做项目时也发现了一些东西。

想要从 DB 中过滤记录，这些记录介于早上 5 点到下午 1 点之间。我想在查询字符串中传递 AM 或 PM 参数。但是mysql date_format 总是采用24 小时格式。上午或下午无关紧要。

select  poll_voting_result_id,poll_master_id,date_voting,date_format(date_voting,'%Y-%m-%d %r') as 12_hour_format_R,
date_format(date_voting,'%Y-%m-%d %H:%i:%s %p') as 24_hour_format_P from poll_voting_result 
where up_down = 0 
and 
poll_master_id = '11' 
and 
date(date_voting) = '2013-03-20' 
and date_format(date_voting,'%H:%i:%s %r') BETWEEN '05:00 AM' and '13:01:06 PM' 
order by date_voting ASC


select  poll_voting_result_id,poll_master_id,date_voting,date_format(date_voting,'%Y-%m-%d %r') as 12_hour_format_R,
date_format(date_voting,'%Y-%m-%d %H:%i:%s %p') as 24_hour_format_P from poll_voting_result 
where up_down = 0 
and 
poll_master_id = '11' 
and 
date(date_voting) = '2013-03-20' 
and date_format(date_voting,'%H:%i:%s %p') BETWEEN '05:00' and '13:01:06' 
order by date_voting ASC

以上两个查询都工作正常。但我需要通过

在“05:00 AM”和“01:01:06 PM”之间