CODEWITHSUNDEEP
mysqlsqldatetimemariadb
JoakDA
JoakDA

Reputation: 305

Retrieve data from database in a time period between two dates

I have an HTML table where I show records received in realtime from devices.

I want to allow the users to get the data from a time interval between 2 dates, for example, retrieve all the data between '2019-09-20' and '2019-09-25' at night (from 22:00:00 to 06:00:00 next day) where all the data is stored on UTC.

I am able to get it working from 06:00:00 UTC to 08:00:00 UTC retrieveing many results, but not for this query (22:12:00 UTC TO 08:00:00 UTC).

This query returns results:

SELECT d0_.id AS id_0
     , d0_.uuid AS uuid_1
     , d0_.datetime_utc AS datetime_utc_2
     , d0_.macaddress AS macaddress_3 
     , d0_.channel_name AS channel_name_5
     , d0_.device_id AS device_id_42
     , d0_.country_id AS country_id_43 
  FROM detection d0_ 
  JOIN device d4_ 
    ON d0_.device_id = d4_.id 
  JOIN country c7_ 
    ON d0_.country_id = c7_.id 
 WHERE (DATE(d0_.datetime_utc) BETWEEN '2019-09-20' AND '2019-09-25')
   AND (TIME(d0_.datetime_utc) BETWEEN '06:00:00' AND '08:00:00') 
 ORDER 
    BY d0_.datetime_utc ASC;

This is the result log returned on MySQL Workbench:

1000 row(s) returned

Now, if I run the query to get the results between the same date interval and between (22:00:00 UTC) to (08:00:00 UTC):

SELECT d0_.id AS id_0, d0_.uuid AS uuid_1, d0_.datetime_utc AS datetime_utc_2, d0_.macaddress AS macaddress_3, d0_.channel_name AS channel_name_5, d0_.device_id AS device_id_42, d0_.country_id AS country_id_43 
FROM detection d0_ 
INNER JOIN device d4_ ON d0_.device_id = d4_.id 
INNER JOIN country c7_ ON d0_.country_id = c7_.id 
WHERE (DATE(d0_.datetime_utc) BETWEEN '2019-09-20' AND '2019-09-25')
AND (TIME(d0_.datetime_utc) BETWEEN '22:00:00' AND '08:00:00') 
ORDER BY d0_.datetime_utc ASC;

The query returns this result (although the time interval is bigger than the first one):

0 row(s) returned

I think it is caused because the start time is bigger than the endtime, but I don't know how to resolve it.

To make clear the question, the query is described as:

"Give me all the records received all the days at night (defined by the TIME sentence) between 2 specific dates (defined by DATE sentence)."

Thanks,

Joaquín.

Upvotes: 0

Views: 805

Answers (1)

kshetline
kshetline

Reputation: 13734

Test two intervals, one from 00:00:00 to 08:00:00, and another from 22:00:00 to 23:59:59.

I'm not sure if you have time data with a resolution greater than one second. If you do, you might have to do something like 23:59:59.999.

Upvotes: 1

Related Questions