5

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?

Improve this question

1 Answer 1

Reset to default
8

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.

Improve this answer
2
  • My preference would be to use --- "Timestamp" LIKE '%12:%' ...and I say that because if it was my dataset, for some reason, there would be a <space> at the beginning and the selection would then grab everything observed in December or the 12th day of each month, depending.
    – user23715
    Jul 24, 2014 at 21:17
  • In this case, however, it would not, especially given the colon at the end of the 12--even December would not match that pattern. Obviously it's a very data-specific answer, and your answer would work for other data.
    – Darren Cope
    Jul 25, 2014 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.