0

i have to query data in certain range of date and time. I have a ClearDB database in heroku with 2 fields to save date and time, these fiels are varchar type, not DATE. Data Structure here and don't look the field names, i have changed below in the querys for better understanding.

I have this query that works:

SELECT 
DISTINCT dates, times, lat, long, ide FROM data 
WHERE ide = 'lcg1234' AND (dates>= '2016-01-27' AND times>='15:00:00') 
 AND (dates<= '2016-01-29' AND times<='16:00:00') 
 ORDER BY times ASC

The problem is when i change the time in the last AND to a lower value like this:

SELECT 
DISTINCT dates, times, lat, long, ide FROM data 
WHERE ide = 'lcg1234' AND (dates>= '2016-01-27' AND times>='15:00:00') 
AND (dates<= '2016-01-29' AND times<='01:00:00') 
ORDER BY times ASC

It not work, why this happend? any ideas are wellcome! and sorry for my bad english.

Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
user2697054
  • 1
  • What is the error that you get? What isn't working? – PeterT Jan 29 '16 at 22:19
  • Is it data issue? is data present for that query? – Somnath Muluk Jan 29 '16 at 22:21
  • The error is when i query for data between 2016-01-27 at 15:00:00 and 2016-01-29 at 01:00:00 not return any data, it look like the time is not considered here, when i query for data between 2016-01-27 at 15:00:00 and 2016-01-29 at 23:00:00 this return my data.... why? – user2697054 Jan 29 '16 at 23:04

1 Answers1

0

You should combine dates and times column and make custom date time field. And then make comparisons.

Check if following query works.

SELECT 
DISTINCT dates, times, lat, long, ide FROM data 
WHERE ide = 'lcg1234' 
AND CONCAT(`dates`,' ',`times`) >= '2016-01-27 15:00:00' 
AND CONCAT(`dates`,' ',`times`) <= '2016-01-29 01:00:00'
ORDER BY times ASC
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
  • I have data for this, the error is when i query for data between 2016-01-27 at 15:00:00 and 2016-01-29 at 01:00:00 not return any data, it looks like the second parameter of time is not considered here, when i query for data between 2016-01-27 at 15:00:00 and 2016-01-29 at 23:00:00 this works and return my data just changing the hour from 01:00:00 to 23:00:00.... why? – user2697054 Jan 29 '16 at 23:07