Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
That works very well! As you say, not the prettiest, but works. I ended up with a complete expression: '20' || substr(cmt,8,2) || '-' || replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(‌​replace(replace(substr(cmt, 4, 3), 'JAN', '01'), 'FEB', '02'), 'MAR', '03'), 'APR', '04'), 'MAY', '05'), 'JUN', '06'), 'JUL', '07'), 'AUG', '08'), 'SEP', '09'), 'OCT', '10'), 'NOV', '11'), 'DEC', '12') || '-' || substr(cmt, 1, 2) || ' ' || substr(cmt, 11, 8) – Simbamangu Nov 23 '11 at 18:47
wow! Seeing this really makes me want to add python support to the field calculator to make this kind of thing nicer. – Nathan W Nov 24 '11 at 9:13
@NathanW that'd be great! – mattwigway Nov 25 '11 at 3:21
@mattwigway - looks like we've got that functionality now with the fieldpycalculator plugin. – Simbamangu Sep 29 '12 at 9:21

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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