Extract one GPS fix per day from data?

Question

I am in the fortunate position of having 2 years worth of animal movement data. With such a large amount of data I would like to extract only one fix per day to make it easier to work with.

Data = 14 hourly fixes per 24 hours. GPS fixes recorded on the hour between 0500 - 1900

Would like to take the midday fix (1200) from each 14 hour period. Currently, data and time are in the same column but i would need to use the date to extract the midday fix...for example:

Timestamp
29/02/2012 05:00
29/02/2012 06:00
29/02/2012 07:01
29/02/2012 08:00
29/02/2012 09:00
29/02/2012 10:00
29/02/2012 11:00
29/02/2012 12:01
29/02/2012 13:01
29/02/2012 14:00
29/02/2012 15:01
29/02/2012 16:01
29/02/2012 17:00
29/02/2012 18:00
29/02/2012 19:00
01/03/2012 05:00
01/03/2012 06:00
01/03/2012 07:00
01/03/2012 08:00
01/03/2012 09:00
01/03/2012 10:00
01/03/2012 11:01
01/03/2012 12:00
01/03/2012 13:01
01/03/2012 14:00
01/03/2012 15:00
01/03/2012 16:00
01/03/2012 17:00
01/03/2012 18:00
01/03/2012 19:00
02/03/2012 05:00
02/03/2012 06:00
02/03/2012 07:00
02/03/2012 08:00
02/03/2012 09:00
02/03/2012 10:01
02/03/2012 11:01
02/03/2012 12:01
02/03/2012 13:01
02/03/2012 14:01
02/03/2012 15:00
02/03/2012 16:01
02/03/2012 17:00
02/03/2012 18:00
02/03/2012 19:00

How do I go about this in QGIS? Is there a code that can search each date then take the 12:00 timestamp...is it a problem that the minutes change ie 12:00 or 12:01? Would it be good to separate the date and time into different columns?

Answer 1

You should be able to do a simple 'select by expression' where

DATECOLUMN like "% 12%"

This is assuming your date is a string column.

Essentially; the query is using the 'pattern' that is formed, and saying "Find me every record where there's a space, followed by 12, follow by a colon. Since your data is in 24h format, the only records criteria that will fit this criteria are those that are somewhere between 1200 and 1259 hours, so you can ignore the minutes entirely. Note that this doesn't work if you ever have the record captured at 11:59, and the next one at 13:01--in this case you may miss a day. From your sample data, this does not appear to happen.