Question 20722 in Mysql Question posted in
The official MySQL documentation can be found here.

sql query to get last 7 day results between time ranges – Mysql

I’ve query which gives the results with a column name called alternative_time.
It has the values like 2023-02-18 09:30:04 . I can do a query like NOW() -INTERVAL 7 DAY) and also time range like 03:00:00 and 10:00:00 but how do i combine both to get the results of last 7 days having the time range of between ’03:00:00′ and ’10:00:00′. can i something like

T.alternative_time > NOW() -INTERVAL 7 DAY ('03:00:00' and '10:00:00')
  • but this is not working.

Do we need to split the time and then store in a variable and then search with that string? can someone please help on this? Thanks!

select name 
from Profile_table 
and T.alternative_time > NOW() -INTERVAL 7 DAY ('03:00:00' and '10:00:00')

Tried with this but not working

Tags:

2

Answers


  1. 0

    With a bit of complex jiggling to get a string containing the right date and time and then convert that to a DATETIME you can do this

    select `name`
from Profile_table 
WHERE `alternative_time` BETWEEN 
        STR_TO_DATE(CONCAT(DATE(NOW() - INTERVAL 7 DAY), ' 03:00:00'),'%Y-%m-%d %h:%i:%s') 
        AND 
        STR_TO_DATE(CONCAT(DATE(NOW()), ' 10:00:00'), '%Y-%m-%d %h:%i:%s')

    You could also just do

    SELECT * FROM apitracker
WHERE `alternative_time` BETWEEN CONCAT(DATE(NOW() - INTERVAL 7 DAY), ' 02:00:00') 
                AND CONCAT(DATE(NOW()), ' 10:00:00')

    and forget about the conversion to a DATETIME data type

    Login or Signup to reply.
  2. 0

    To filter results from the last 7 days with a specific time range, you can use the following query:

    SELECT name
FROM Profile_table
WHERE alternative_time BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
    AND TIME(alternative_time) BETWEEN '03:00:00' AND '10:00:00';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search