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

Mysql – How can I pick same time of day across multiple days?

Column utc time created is a datetime field of when entry was made in a table that’s updated hourly. Contains 24 entries for each day.

I want to query for same time of day across a set of days (might want all 4pm records for 10/1-10/10) but don’t want records for other times in that date range. I don’t want all 4pm records in the table (the whole month is in the table).

How to do this for both SQLite and MySQL?

Tags:

3

Answers


  1. 0

    Just filter by date and then hour:

    SELECT 
    * 
FROM 
    TableName
WHERE 
    CAST(UTCTimeCreated AS DATE) BETWEEN '2022-10-01' AND '2022-10-10'
    AND HOUR(UTCTimeCreated) = 16 -- or whatever
    Login or Signup to reply.
  2. 0

    Similar to what John K. have (SQL):

        SELECT 
    * 
FROM 
    TableName
WHERE 
    UTCTimeCreated BETWEEN '2022-10-01' AND '2022-10-10' AND DATEPART(Hour,UTCTimeCreated ) = 16
    Login or Signup to reply.
  3. 0

    To do this portably between sqlite and mysql, you can use substr.

    select *
from foo
where
    utc_created_time >= '2022-10-01' and
    utc_created_time < '2022-10-14' and
    substr(utc_created_time,12,8)='16:00:00'

    Avoid between if you might be looking for midnight times; datetime between date and date behaves differently between mysql and sqlite. So just do < the day after your end date.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search