Selecting results within diffrerent timesets in one sql query

How can select the last records added in 5 seconds, 5 minutes, 5 hours, 5 days, 5 weeks, 5 months, 5 years i开发者_如何学Cn one query?

Is it possible to be selected in one query?

sample records include

name | added_timestamp
v | last_five_minutes
k | last_hour
l | last_hour

the timestamps can be pseudo for the actual time

Although you haven't stated it, I suspect you want totals for the various time periods:

SELECT
  SUM(date_added > NOW() - INTERVAL 5 SECOND) as total_in_last_5_seconds,
  SUM(date_added > NOW() - INTERVAL 5 MINUTE) as total_in_last_5_minutes,
  SUM(date_added > NOW() - INTERVAL 5 HOUR) as total_in_last_5_hours,
  SUM(date_added > NOW() - INTERVAL 5 DAY) as total_in_last_5_days,
  SUM(date_added > NOW() - INTERVAL 5 WEEK) as total_in_last_5_weeks,
  SUM(date_added > NOW() - INTERVAL 5 MONTH) as total_in_last_5_months,
  SUM(date_added > NOW() - INTERVAL 5 YEAR) as total_in_last_5_years
from records;

Edited: Added alternative prompted by comment

If you want the actual records, this will categorize them:

SELECT
  *,
  case
    when date_added > NOW() - INTERVAL 5 SECOND then 'last_5_seconds'
    when date_added > NOW() - INTERVAL 5 MINUTE then 'last_5_minutes'
    when date_added > NOW() - INTERVAL 5 HOUR then 'last_5_hours'
    when date_added > NOW() - INTERVAL 5 DAY then 'last_5_days'
    when date_added > NOW() - INTERVAL 5 WEEK then 'last_5_weeks'
    when date_added > NOW() - INTERVAL 5 MONTH then 'last_5_months'
    when date_added > NOW() - INTERVAL 5 YEAR then 'last_5_years'
    else 'ancient'
  end as time_category
from records;

SELECT
  *,  /* now just don't be lazy but specify whatever columns you want to pull */
  (Timestamp >= NOW() - INTERVAL 5 SECOND) AS AddedWithinSeconds,
  (Timestamp >= NOW() - INTERVAL 5 MINUTE) AS AddedWithinMinutes,
  (Timestamp >= NOW() - INTERVAL 5 HOUR)   AS AddedWithinHours,
  (Timestamp >= NOW() - INTERVAL 5 DAY)    AS AddedWithinDays,
  (Timestamp >= NOW() - INTERVAL 5 WEEK)   AS AddedWithinWeeks,
  (Timestamp >= NOW() - INTERVAL 5 MONTH)  AS AddedWithinMonths
FROM atable
WHERE Timestamp >= NOW() - INTERVAL 5 YEAR
ORDER BY
  AddedWithinSeconds DESC,
  AddedWithinMinutes DESC,
  AddedWithinHours   DESC,
  AddedWithinDays    DESC,
  AddedWithinWeeks   DESC,
  AddedWithinMonths  DESC

Get the last records added in 5 years:

SELECT * FROM records WHERE date_added > NOW() - INTERVAL 5 YEAR

Then extract the records added in 5 seconds, 5 minutes, 5 hours, 5 days, 5 weeks, 5 months from the results.