sql - mysql timestamp query, select all but first records for a given minute -
i have table stores data snapshots ip cameras take. generally, cameras take multiple snapshots per minute, although 1 of cameras configured take 1 snapshot per minute.
i purge items table (and disk), retain according following rules:
- for first 7 days, images kept
- anything on 7 days old, keep first snapshot per hour of day
- anything on 4 weeks, keep first snapshot in 06th, 12th , 18th hour of day
- anything on 3 months old, keep first snapshot in 12th hour of day.
the following current query, works ok, except keeps snapshots taken during first minute of hour.
select camera_id, timestamp, frame, filename snapshot_frame ((timestamp < subdate(now(), interval 7 day) , minute(timestamp) != 0) or (timestamp < subdate(now(), interval 4 week) , (hour(timestamp) not in (6, 12, 18) or minute(timestamp) != 0)) or (timestamp < subdate(now(), interval 3 month) , (hour(timestamp) != 12 or minute(timestamp) != 0))) how can retain first snapshot per minute timestamp on 7 days old in accordance above rules?
in case helps, table/index structure:
mysql> describe snapshot_frame; +-----------+--------------+------+-----+---------+-------+ | field | type | null | key | default | | +-----------+--------------+------+-----+---------+-------+ | camera_id | int(11) | no | | null | | | timestamp | datetime | no | mul | null | | | frame | int(11) | yes | | null | | | filename | varchar(100) | yes | uni | null | | +-----------+--------------+------+-----+---------+-------+ 4 rows in set (0.04 sec) mysql> show index snapshot_frame; +----------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment | +----------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | snapshot_frame | 0 | filename | 1 | filename | | 3052545 | null | null | yes | btree | | | | snapshot_frame | 1 | idx_time_camera | 1 | timestamp | | 3052545 | null | null | | btree | | | | snapshot_frame | 1 | idx_time_camera | 2 | camera_id | | 3052545 | null | null | | btree | | | +----------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.42 sec) mysql> select count(*) snapshot_frame; +----------+ | count(*) | +----------+ | 3030214 | +----------+ 1 row in set (18.47 sec) update: have managed create query provides of snapshots want keep, according rules:
select camera_id, timestamp, frame, filename snapshot_frame timestamp >= subdate(now(), interval 7 day) union (select camera_id, timestamp, frame, filename snapshot_frame timestamp < subdate(now(), interval 7 day) , timestamp >= subdate(now(), interval 4 week) , minute(timestamp) = 0 group camera_id, year(timestamp), month(timestamp), date(timestamp), hour(timestamp), minute(timestamp)) union (select camera_id, timestamp, frame, filename snapshot_frame timestamp < subdate(now(), interval 4 week) , timestamp >= subdate(now(), interval 3 month) , hour(timestamp) in (6, 12, 18) , minute(timestamp) = 0 group camera_id, year(timestamp), month(timestamp), date(timestamp), hour(timestamp), minute(timestamp)) union (select camera_id, timestamp, frame, filename snapshot_frame timestamp < subdate(now(), interval 3 month) , hour(timestamp) = 12 , minute(timestamp) = 0 group camera_id, year(timestamp), month(timestamp), date(timestamp), hour(timestamp), minute(timestamp)) i trying work out how reverse now, return result set contains rows snapshot_frame aren't in above query.
any pointers?
the solution using populate temporary table rows wish keep:
create temporary table if not exists retain_frames (index idx_time_camera (timestamp, camera_id))as select camera_id, timestamp, frame, filename ( (select camera_id, timestamp, frame, filename snapshot_frame timestamp >= subdate(now(), interval 7 day)) union (select camera_id, timestamp, frame, filename snapshot_frame b timestamp < subdate(now(), interval 7 day) , timestamp >= subdate(now(), interval 4 week) , minute(timestamp) = 0 group camera_id, date(timestamp), hour(timestamp), minute(timestamp)) union (select camera_id, timestamp, frame, filename snapshot_frame c timestamp < subdate(now(), interval 4 week) , timestamp >= subdate(now(), interval 3 month) , hour(timestamp) in (6, 12, 18) , minute(timestamp) = 0 group camera_id, date(timestamp), hour(timestamp), minute(timestamp)) union (select camera_id, timestamp, frame, filename snapshot_frame d timestamp < subdate(now(), interval 3 month) , hour(timestamp) = 12 , minute(timestamp) = 0 group camera_id, date(timestamp), hour(timestamp), minute(timestamp))) e and select stale snapshots using following query:
select camera_id, timestamp, frame, filename snapshot_frame not exists (select camera_id, timestamp, frame, filename retain_frames b a.camera_id = b.camera_id , a.timestamp = b.timestamp , a.frame = b.frame) the issue creation of temporary table takes 2 minutes , seems lock db causing sporadic operationalerror: (1205, 'lock wait timeout exceeded; try restarting transaction') being thrown in python code thread attempting insert same table.
Comments
Post a Comment