Problems with CONVERT_TZ and BETWEEN in Mysql 5.1.73

Question

Problems with CONVERT_TZ and BETWEEN in Mysql 5.1.73

Navigation

#1 by (1 votes)

2

I'm trying to do this search with CONVERT_TZ and BETWEEN in MySQL 5.1.73 but always returns zero rows.

Has anyone ever used BETWEEN in 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

mysql

asked by anonymous 26.02.2014 / 15:48

1 answer

Insert Android Application inside another Android application Session in database

score 1 · Accepted Answer

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

It is:

February, 25 2014 04:27:30+0000

Demo on sqlfiddle

You're probably not getting the result because:

The StartDate and EndDate columns are DATE type and have no time information. In this case, you can use the date() function to remove the time at the time of the comparison.

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