Problems with CONVERT_TZ and BETWEEN in Mysql 5.1.73

Question

I’m trying to do this search with CONVERT_TZ and BETWEEN in Mysql 5.1.73 but always returns zero lines.

Has anyone ever used BETWEEN thus?

SELECT 
x.UserName, 
x.StartDate , 
x.EndDate 
FROM tabela AS x 
WHERE (x.UserName='usuario') AND
CONVERT_TZ('2014-02-25 18:27:30','+11:00','-03:00') BETWEEN x.StartDate AND x.EndDate

Answer 1

I made the following example and it worked:

CREATE TABLE tabela (
    UserName varchar(100),
    StartDate datetime,
    EndDate datetime
  );

insert into tabela (UserName, StartDate, EndDate)
values
  ('eu', '2014-02-25 04:00:00', '2014-02-25 05:00:00'),
  ('me', '2014-02-25 03:30:00', '2014-02-25 04:30:00'),
  ('jo', '2014-02-25 03:00:00', '2014-02-25 04:00:00');

SELECT *
FROM tabela AS x 
WHERE CONVERT_TZ('2014-02-25 18:27:30','+11:00','-03:00') 
    BETWEEN x.StartDate AND x.EndDate

Note that the time returned by the code:

CONVERT_TZ('2014-02-25 18:27:30','+11:00','-03:00')

IS:

February, 25 2014 04:27:30+0000

Demo on the sqlfiddle

You’re probably not getting the result because:

1. The columns StartDate and EndDate are of the type DATE and have no time information. In this case, you can use the function date() to remove the time at the time of comparison.
2. There are no records whose date/time returned is within the range. Maybe you were expecting another result.

If your case is item #1, do so:

SELECT *
FROM tabela AS x 
WHERE date(CONVERT_TZ('2014-02-25 18:27:30','+11:00','-03:00'))
    BETWEEN x.StartDate AND x.EndDate

Demo on the sqlfiddle