Since the seconds are from 0 (1970-01-01 00:00:00 UTC
), you should look for every multiple of 60
SELECT * FROM mytable WHERE MOD(TimeStamp,60)=0;
or if TimeStamp is indexed, you can do
SELECT T.* FROM
(SELECT TimeStamp FROM mytable WHERE MOD(TimeStamp,60)=0) M
INNER JOIN mytable T USING (TimeStamp);
Give it a Try !!!
SUGGESTION #1
You should store the timestamp of the minute and index it
ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);
Then, you can do MIN aggregation on MinuteTimeStamp.
SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;
and use it get those records
SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);
It was tactfully pointed out that triggers would degrade performance
Perhaps doing INSERTs like this may help
INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
uniquevalue,
UNIX_TIMESTAMP(NOW()),
UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);
SUGGESTION #2
Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.
CREATE TABLE MinuteKeys
(
MinuteTimeStamp INT UNSIGNED NOT NULL,
UniqueKey INT UNSIGNED NOT NULL,
PRIMARY KEY (UniqueKey)
KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;
Then, you could use that table for the aggregation
SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);
EPILOGUE
Other suggestions are possible but you should really consider normalization of the table
See my post Too many columns in MySQL as to why
Your first HUGE mistake (as @ypercube pointed out) is not using a DATETIME type for a date-time type variable. TIMEs are not VARCHARs and doing this will mess up your queries, make your app non-portable and will confuse the optimiser.
The other HUGE problem is MySQL. It doesn't perform this sort of query properly. Your query is ambiguous - PostgreSQL for example will throw an error if you try and run such a query.
For example
mysql> SELECT id, MAX(posted_date) FROM example GROUP BY id;
+------+------------------+
| id | MAX(posted_date) |
+------+------------------+
| 1 | 2015-03-27 |
| 2 | 2015-03-15 |
+------+------------------+
2 rows in set (0.00 sec)
mysql>
is unambiguous and gives the correct answer.But, because you have tried to use a SELECT * FROM example, the MySQL engine doesn't know which values to return and (stupidly enough) appears (at least in the several examples of this problem that I have seen) to return the correct unambiguous values, but the other are random - so you could get id = 1 (correct) with cname = 'ghi' (incorrect).
Check out MySQL's SQL modes - there's one (ONLY_FULL_GROUP_BY) that disallows this behaviour. It should be switched on at ALL times.
To get your query to work properly, for starters, use a DATETIME type (makes the subquery below easier) and then do a subquery for cname where your DATETIME value matches that for the correct cname. HTH,
[EDIT]
Using this schema and data, your query becomes relatively easy.
CREATE TABLE example
(
id int(11),
cname varchar(10),
posted_datetime datetime
);
INSERT INTO example
VALUES (1, 'abc', '2015-03-26 04:25');
INSERT INTO example
VALUES (1, 'def', '2015-03-27 16:30');
INSERT INTO example
VALUES (2, 'ghi', '2015-03-11 02:25');
INSERT INTO example
VALUES (2, 'jkl', '2015-03-15 12:25');
And your working query is:
SELECT e.id, e.cname, e.posted_datetime
FROM example e
JOIN
(
SELECT id, MAX(posted_datetime) AS posted_datetime
FROM example
GROUP BY id
) m
ON m.id = e.id
AND m.posted_datetime = e.posted_datetime ;
or check it out here: SQLfiddle
Best Answer
You can use dynamic sql for this purpose
The if statement chekcm if there are markups if so it tales the firwst query into a user defined variable
@sql
and the executes it (see manual)