Jump to content

select between rate range at set time.


MoFish
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 |
+----+------------+------------+---------------+---------------------+
Link to comment
Share on other sites

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

 

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

Link to comment
Share on other sites

  • 2 weeks later...

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)
Link to comment
Share on other sites

  • Solution

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)

 

Link to comment
Share on other sites

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 by MoFish
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.