CODEWITHSUNDEEP
mysqlsqldate-format
dlite922
dlite922

Reputation: 1994

MySQL DATE_FORMAT using BETWEEN doesn't work

I'm using two stored functions, They work and when put into SELECT, they return proper values, however when i try to reduce and get a subset of those values, I get an empty set with no warnings.

Here's my 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;

The last 3 lines of the WHERE clause when added, return zero. They are copy and pasted in to SELECT and work just fine.

I'm absolutely baffled!

Also, when using a string for comparison (i.e. ... = '19:00'), I get results. Anyone have any idea what's wrong?

Edit: Sample Rows returned when not including the where clause. 

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

EDIT #2: I took all the fields from all the tables and combined it into one table in a sandbox. I made both my functions static (to always assume we're in summer daylight savings season) and added sample data. IT WORKS! So I have to backtrack I guess and it's nothing that's wrong with the SQL syntax or logic. Some column, some join or table is failing. Also I intermittently get this error: 

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

If I could attach an SQL file, i could attach my test schema, I don't want to paste it here.

Upvotes: 1

Views: 1333

Answers (1)

Pragnesh Karia
Pragnesh Karia

Reputation: 519

I also found a little while doing my project stuff.

Wanted to filter records from DB , which are between 5 AM to 1 PM. I want to pass , AM or PM parameter in query string. But mysql date_format always take 24 hours format. AM or PM doesn't matter.

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

Above Both query working fine. But i need to pass

BETWEEN '05:00 AM' and '01:01:06 PM'

Upvotes: 1

Related Questions