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 it always returns zero rows.

Has anyone used BETWEEN this way?

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:

I did 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')

IT'S:

February, 25 2014 04:27:30+0000

demo on sqlfiddle

You are probably not getting the result because:

  1. The columns StartDate and EndDate are of type DATE and do not have time information. In this case, you can use the date() function 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 that of item #1, do this:

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

Scroll to Top