select between rate range at set time.

[MoFish]

Hi All,

 

I have the following DB structure.

 

schedule_date_start | schedule_date_end | schedule_date_time

2017-01-11                 |  2017-01-11              | 15:00:00

 

I am going trying to pefrom a query every minute to find out what items are found between the:

 

schedule_date_start and schedule_date_end and match the schedule_date_time.

 

I attempted to do this by writing the following SQL statement. I am finding that i never get any results. This is probably because my script executing is not running at exactly 15:00:00 and quite possibly 15:00:45. Is there an easy way to perform a query which would not take into consideration of the seconds? I tried messing around with the minutes() however didn't have much luck.

select * from `domains` where CURDATE() >= `schedule_date_start` AND CURDATE() <= `schedule_date_end` AND CURTIME() = `schedule_time`

Thanks,

 

MoFish

[MoFish]

Sorry - I just noticed that there is a forum for MySQL Help. This should probably be there. Appologies!

[Barand]

you could use EXTRACT()

... AND EXTRACT(HOUR_MINUTE FROM CURTIME()) = EXTRACT(HOUR_MINUTE FROM schedule_date_time)

[MoFish]

Thanks for replying Barand.
 
I tried the following SQL which has no errors - however does not return the result i would have expected.
 

select * from `table` where CURDATE() >= `schedule_date_start` AND CURDATE() <= `schedule_date_end` AND EXTRACT(HOUR_MINUTE FROM CURTIME()) = EXTRACT(HOUR_MINUTE FROM schedule_time)

I set a row in my DB with the following values and waited until 17:06 before performing my query - however nothing was returned. If i remove the time bit you supplied - the correct date results are returned.

schedule_date_start (date)        schedule_date_end (date)        schedule_time (time)
2017-01-11 	                  2017-01-18 	                  17:06:00

Is there something i am missing?

 

Thanks,

 

MoFish

[benanamen]

Why are you storing the time separately? Just use a datetime column.

[Barand]

Are you sure your clock was right? This was my test

mysql> select * from testtime;
+----+------------+------------+---------------+
| id | start_date | end_date   | schedule_time |
+----+------------+------------+---------------+
|  1 | 2017-01-11 | 2017-01-18 | 18:55:00      |
|  2 | 2017-01-11 | 2017-01-18 | 18:56:00      |
|  3 | 2017-01-11 | 2017-01-18 | 18:57:00      |
+----+------------+------------+---------------+

mysql> SELECT id
    -> , start_date
    -> , end_date
    -> , schedule_time
    -> , NOW()
    ->  FROM testtime
    -> WHERE CURDATE() BETWEEN start_date AND end_date
    -> AND EXTRACT(HOUR_MINUTE FROM schedule_time) = EXTRACT(HOUR_MINUTE FROM CURTIME());
+----+------------+------------+---------------+---------------------+
| id | start_date | end_date   | schedule_time | NOW()               |
+----+------------+------------+---------------+---------------------+
|  3 | 2017-01-11 | 2017-01-18 | 18:57:00      | 2017-01-14 18:57:39 |
+----+------------+------------+---------------+---------------------+

[MoFish]

Thanks Barand, turns out my server was using an American timezone - darn!

 

I used a set time_zone to Europe/London and all worked! Not sure if this is the best way to update it however.

SET time_zone = 'Europe/London'; select * from `table` where CURDATE() >= `schedule_date_start` AND CURDATE() <= `schedule_date_end` AND EXTRACT(HOUR_MINUTE FROM CURTIME()) = EXTRACT(HOUR_MINUTE FROM schedule_time)

Thanks very much!

 

MoFish

[Barand]

try using UTC_TIME() instead of CURTIME();

[MoFish]

Hi Barand, sorry to bother you again.

 

I have amended things slightly to use a date/time field instead of a seperate date and time column as it was a bit of an overhead to maintain.

 

My SQL query has been updated as below - however it doesnt appear to work correctly.

 

To clarify on what i am trying to do:

 

I only want to capture results between schedule_start and schedule_end on both the current date and time. i tried UTC_TIMESTAMP also without success.

 

UTC_TIME was used as you mentioned before to help with the SQL TIME i had.

 

The query does seem a little long winded.

 

Regards,

 

MoFish

select * from `table` 
WHERE 
UTC_DATE() >= `schedule_start` 
AND 
UTC_DATE() <= `schedule_end` 
AND 
EXTRACT(HOUR_MINUTE FROM UTC_TIME()) >= EXTRACT(HOUR_MINUTE FROM schedule_start) 
AND 
EXTRACT(HOUR_MINUTE FROM UTC_TIME()) <= EXTRACT(HOUR_MINUTE FROM schedule_end)

[Barand]

So for it to work as it did with a separate time column, are the time elements the same in the two datetime columns?

 

When comparing a date with a datetime column you need to use only the date portion of the datetime.

 

So if my opening assumption is true

SELECT id
, schedule_start
, schedule_end
WHERE
    UTC_DATE() BETWEEN DATE(schedule_start) AND DATE(schedule_end)
        AND EXTRACT(HOUR_MINUTE FROM UTC_TIME()) = EXTRACT(HOUR_MINUTE FROM schedule_start)

 

[MoFish]

Thanks for replying Barand.

 

I have amended your query to suit my needs - the BETWEEN was the key i was missing i think.

 

The following appears to work as expected for the time being - however i'm guessing when UK hits Brittish Summer Time then things may not work quite aswell. Until then, i'll leave it as it currently is. Thanks again, your a star.

SELECT * from table WHERE UTC_DATE() BETWEEN DATE(`schedule_start`) AND DATE(`schedule_end`) AND UTC_TIME() BETWEEN `schedule_start` AND `schedule_end`

MoFish

Edited January 30, 2017 by MoFish