MySQL select from a period of time

Question

I'd like to build a query that updates daily at 1pm, which shows static data from 3pm to 11am nexy day.

if now is 2018-06-16 12pm, shows data from 2018-06-15 3pm - 2018-06-16 11am

if now is 2018-06-16 1pm, shows data from 2018-06-16 3pm - 2018-06-17 11am

Here is the query I tried.

UPDATED:

SELECT * FROM config WHERE gt BETWEEN CONCAT(curdate(),' 15:00:00') AND CONCAT(curdate()+ INTERVAL 1 DAY,' 10:59:59')

ISSUES:

When today is 2018-06-15, curdate() -> 2018-06-15, which shows data of today which no issues.

When today is 2018-06-16, curdate() -> 2018-06-16, which shows data of next day way too early, what i want is update next day 1pm daily.

since 3pm to 11am is one continuous series of time, you should be able to just use BETWEEN - see https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql for an example — ADyson, Jun 15 '18 at 11:46

score 0 · Answer 1 · edited Jun 15 '18 at 21:27

0

You can use this sample to build your query:

SELECT * 
FROM table_name
WHERE `date` >= NOW() - INTERVAL 10 DAY;

Here some information on how to work with INTERVAL.

And here is some useful information how to execute query at a particular time.

EDIT:

if now() = 2018-06-16 1pm, shows data from 2018-06-16 3pm - 2018-06-17 11am

Then you ca use this example:

SELECT * 
FROM config 
WHERE DATE(gt) BETWEEN DATE(NOW() + INTERVAL 2 DAY_HOUR) AND -- 2018-06-16 3pm
                       DATE(NOW() + INTERVAL 22 DAY_HOUR);   -- 2018-06-17 11am

DEMO here

Note: the first DATE should be earlier as second DATE in BETWEEN statement. Otherwise it won't work.

edited Jun 15 '18 at 21:27

marc_s

732,580
175
1,330
1,459

answered Jun 15 '18 at 11:24

Andrei Suvorkov

5,559
5
22
48

but it return zero result – Joe Go Jun 15 '18 at 12:20
Working now, please check, and example is added – Andrei Suvorkov Jun 15 '18 at 13:00

score 0 · Answer 2 · answered Jun 15 '18 at 12:44

0

lot of ways you can apply select command. here I mentioned the only 2 ways. this is useful for you.

SELECT * FROM config 
WHERE gt BETWEEN #07/04/1996# AND #07/09/1996#;


select * from config  where gt  and created_at > (now()- interval 10 day)

answered Jun 15 '18 at 12:44

Vipin Pandey

659
8
17

score 0 · Accepted Answer · answered Jun 15 '18 at 13:33

Two cases:

It's between 0:00 and 12:59: you want yesterday 15:00 till today 11:00
It's between 13:00 and 23:59: you want today 15:00 till tomorrow 11:00

The query:

select * 
from config 
where
(
  hour(current_time) between 0 and 12 
  and gt >= current_date - interval 9 hours -- yesterday 15:00
  and gt < current_date + interval 11 hours -- today 11:00
)
or
(
  hour(current_time) between 13 and 23
  and gt >= current_date + interval 15 hours -- today 15:00
  and gt < current_date + interval 35 hours -- tomorrow 11:00
);

MySQL select from a period of time

3 Answers3