Does QGIS support parsing of date strings in tables?

Question

It is straightforward to load GPX files (GPS downloaded tracks / points) into QGIS, then save and manipulate them as shapefiles. However, the recording date (in the comment (cmt) field) of waypoints is in the format YY-MMM-DD HH:MM:SS (e.g. 28-AUG-11 11:57:51) and it would be possible to sort by date only if the format could be parsed to something closer to the ISO 8601 standard (YYYY-MM-DD ...).

String manipulation in the field calculator can take you partway: '20' || substr(cmt,8,2) || '-' || substr(cmt, 4, 3) || '-' || substr(cmt, 1, 2) || ' ' || substr(cmt, 11, 8) However, you're then left with YYY-MMM-DD HH:MM:SS - i.e. AUG instead of 08.

Is there a straightforward way to parse the date string directly, providing integer months? (Ideally, something like the R strptime() function)

Answer 1

If you're willing to use PostGIS, you can convert the GPX date field to a Postgres timestamp field like so (this works as long as the months are always abbreviated to 3 letters, which I believe is the case):

to_timestamp('28-AUG-11 11:51:57', 'DD-MON-YY HH:MI:SS');

Or, in the field calculator, you can do something like this:

replace(replace(replace(substr(date, 4, 3), 'JAN', '01'), 'FEB', '02'), 'MAR', '03')

(of course, put in more nested replaces for all the months). It isn't pretty but it works.