CODEWITHSUNDEEP
mysqltimeintervals
hearmeroar
hearmeroar

Reputation: 307

Get time interval in mysql

is there a query for me to get the time interval - One minute, five minutes, quarter hour, half hour, hour, and day? I use MySQL as a database.

Upvotes: 12

Views: 22539

Answers (2)

AwokeKnowing
AwokeKnowing

Reputation: 8236

to get a range, like from 30 to 45 minutes ago, do like this

SELECT * FROM tbl 
WHERE tbl.mydate > DATE(DATE_sub(NOW(), INTERVAL 45 MINUTE)) 
AND tbl.mydate < DATE(DATE_sub(NOW(), INTERVAL 30 MINUTE));

Upvotes: 12

Filipe Silva
Filipe Silva

Reputation: 21677

You are probably looking for date_sub:

SELECT * FROM YOURTABLE t
WHERE t.timestamp > date_sub(NOW(), interval 1 hour);

For different intervals you can change the 1 hour to 5 days, 5 weeks, etc).

From the documentation:

DATE_SUB(date,INTERVAL expr unit)

The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a “-” for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted.

The following table shows the expected form of the expr argument for each unit value.

unit Value     Expected expr Format

MICROSECOND    MICROSECONDS
SECOND         SECONDS
MINUTE         MINUTES
HOUR           HOURS
DAY            DAYS
WEEK           WEEKS
MONTH          MONTHS
QUARTER        QUARTERS
YEAR           YEARS

Upvotes: 8

Related Questions