0

I have a view in PostGIS that has a bunch of points and I want to symbolize in QGIS (3.2.2) by the age of the points. I am trying to use this expression (or something like it):
day(age(ts, now()))

But that doesn't appear to do anything (returns null). The column is a timestamp (UTC). Is there anything I need to do the ts timestamp value before passing into the age(,) function.

I know I could force it from the PostgreSql by adding a derived column in the view, but I feel like I wouldn't need to do that.

Improve this question
3
  • Could you add to your PostGIS View a ROW_NUMBER column that sorts the data by the method you've outlined, which then you could then use in QGIS to render / sort by? – DPSSpatial Oct 10 '18 at 18:47
  • so...I exited out QGIS. I opened it again and now the expression works. weird. – Jay Cummins Oct 10 '18 at 20:54
  • Oh - that's good! – DPSSpatial Oct 10 '18 at 21:12
1

In QGIS try using a conversion function on your timestamp field and the Now() Function:

age(to_date(ts),to_date(now()))

This will allow you to calculate whole days rather than fractions of days.

If you go the derived field route:

The following block of SQL Code should do the trick:

SELECT date(now()) - date(ts)) AS age FROM my_table
Improve this answer
2

My original expression (and approach) was correct. Once I exited the QGIS application and re-opened, everything worked correctly. And I am unable to reproduce that original behavior that prompted me to ask the question in the first place.

2 workarounds were suggested and both would work. @Rob's answer produces a better explain plan when using a predicate with the view. @DPSSpatial's row_number() executes a bit slower--unless you need the view ordered by the timestamp every time, I would opt for the former.

Improve this answer
2
  • That is strange that it wasn't working at first. Were you attempting to use a Categorized symbology or some other type? I tried using graduated and it was returning NULL and nothing would happen when I attempted to classify the data. I attempted to edit my original answer with additional information on how you could use a conversion function in a QGIS expression to alter the timestamp field to a date field in order to get rounded off days instead of decimal days. – Rob Oct 10 '18 at 21:18
  • Update: I attempted to use the Graduated Symbology method and by explicitly using the expression day(age(now(),ts)) I was able to get the results I was looking for. I also wanted to add further confirmation that using the conversion function to_date() in this exercise yielded whole days instead of decimal days. – Rob Oct 10 '18 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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