-1

juat like now, i need 30 rows for last 30 minutes

now returns only one row with current time, I want 30 rows for last 30 minutes for each minute

Kalyan
  • 19
  • 6
  • Why would you just want to log out the last 30 minutes of time itself? Don't you want to additionally log out some data or events that *occurred* over those 30 minutes? – Obsidian Age Jun 13 '19 at 04:46
  • I want to display count of records for each minute from a table for last 30minutes including those missed time, so that i need timestamps – Kalyan Jun 13 '19 at 04:50

1 Answers1

2

For the count per minute the solution doesn't need the sequence:

SELECT MINUTE(timestamp) as m, COUNT(*)
FROM table
WHERE timestamp > DATE_SUB(NOW(), INTERVAL 30 MINUTE)
GROUP BY m

Handle gaps of no entries in your application.

The sequence, if there's ever a need:

db fiddle

This is an extension of a sequence of numbers:

SELECT
    DATE_SUB(NOW(), INTERVAL (TENS.SeqValue + ONES.SeqValue) MINUTE) as t
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 4
    UNION ALL
    SELECT 5
    UNION ALL
    SELECT 6
    UNION ALL
    SELECT 7
    UNION ALL
    SELECT 8
    UNION ALL
    SELECT 9
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10
    UNION ALL
    SELECT 20
    ) TENS
ORDER BY t DESC

MariaDB since 10.0 has a sequence storage engine for a shorter solution:

SELECT
    DATE_SUB(NOW(), INTERVAL seq MINUTE) as t
FROM seq_0_to_30
ORDER BY t DESC
danblack
  • 12,130
  • 2
  • 22
  • 41
  • 1
    Thak you so much @danblack , the 2nd query works fine, could tell me how can I get only Hours &minutes (H:i) only from the 2nd query. – Kalyan Jun 13 '19 at 05:15
  • 1
    [DATE_FORMAT](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) – danblack Jun 13 '19 at 05:17
  • Hi @danblack , the seq is not working in some mysql servers, any other way please ... – Kalyan Jun 14 '19 at 07:03
  • I provided a with and without option there. If the first option is "not working" ask a question and provide a meaningful error message or unexpected results along with the MYSQL version. – danblack Jun 16 '19 at 23:14