Better date and time support in QGIS expressions and styles


Version note: This will only work in the latest dev build of QGIS – not in 1.8

The lack of uni for the next couple of weeks has left me some time at night to work on some features that I really wish QGIS had.  One of these features was better date and time support in the expression engine.  Date and time is an important concept when working on inspection data and not being able to style my features in QGIS using date operations was bugging me.  So in good open source fashion I added some.

Here are the current functions (more to come in the future):

  • $nowreturns the current date and time
  • age({datetime},{datetime}) – returns the difference between the two dates
  • todate({string}) – converts a string to date type
  • totime({string}) – converts a string to time type
  • tointerval({string}) – converts a string to a interval type (details below)
  • day({datetime} or {interval}) – returns the day from a datetime type or the number of days in a interval.
  • hour(…) – Same as above but for hours
  • minute(…)  – Same as above but for minutes
  • second(…)  – Same as above but for seconds
  • day(..)  – Same as above but for days
  • week(..)  – Same as above but for weeks
  • month(…)  – Same as above but for months
  • year(…) – Same as above but for years
  • {datetime} – {interval} = {new datetime} – returns a new datetime subtracting the interval 
  • {datetime} + {interval} = {new datetime} – returns a new datetime adding the interval


The interval type

Functions like age(..), tointerval(), {datetime} -/+ {interval}, day(..), hour(..), etc, use, or return, Intervals.  An Interval is a measure of time that we can use for different things.  An example of an Interval is ‘1 Year 2 Months’ this is then converted to a number of seconds and used for any calculations.

For example one can take away 10 days from the current date by doing the following ( -> marks the output ):

todate($now - '10 Days')
-> 2012-06-20

as

todate($now)
-> 2012-06-30

We can also do something like:

todate($now + '2 Years 1 Month 10 Days')
-> 2014-08-10

The age() function will return an interval which we can use extract what information we need.

The number of days between two dates:

day(age('2012-06-30', '2012-06-10'))
-> 20
-- Think of it as '2012-06-30' - '2012-06-10'
-- Note: day(), month(), etc, functions return doubles so you can get
-- 21.135234 days if you are using date & time type rather than just date type
-- wrap the result in toint() to get a more sane output.

Day() will also work on a plain date:

day('2012-06-30')
-> 30

We can even get the number of seconds between two dates:

second(age('2012-06-30', '2012-06-10'))
-> 1728000

Currently the only date format supported is {year}-{month}-{day} as seen in the examples above. Shouldn’t be too hard to add support to the todate(), todatetime(), totime() functions for giving it a pattern to use when converting the string e.g. dd-mm-YYYY, or something like that.

More on this fancy new stuff

When I wrote the new expression builder dialog a while ago I made it dynamic so that any new functions added to the expression engine will show up automatically.  So here they are:

List of new date and time functions.

We can also use these functions in the rule based rending, which is where the power really comes in.  Lets see something like that in action:

Styled using days and years

Should be pretty straight forward to understand. We are using the age() and day() functions to style the events that are older than 30 days, within 30 days, for today, or in the future.  We also check the year of the event using year() and year($now) to make sure we only see this years events, or style them differently depending on if they are last years events or in the future.

This is the result of the above rules:

Result of using date functions in rule based renderer

I’m also using the date functions in the expression based labelling to label the features using the following expression:

CASE
WHEN year( "dateadded") < year($now) THEN
	'Last Year'
WHEN day(age("dateadded", $now)) < 0 THEN
	day(age("dateadded", todate($now))) || ' Days old'
ELSE
	day(age("dateadded", todate($now))) || ' Days to go'
END

Well that’s it. Hope you find it handy in your day-to-day mapping. I know I will be using it a lot.
Thanks to Martin and Jürgen for the code reviews during the process; venturing in an unknown part of the code base always makes me nervous but that is all part of learning, and sometimes you can make some pretty cool stuff.
Some other random notes: The general idea has been modelled of how Postgres handles dates and times, it’s not an exact copy but follows the same kind of ideas. The interval class also uses the same number of seconds for one year that postgres does so that we can be consistent with the output.

About these ads

6 thoughts on “Better date and time support in QGIS expressions and styles

  1. Looks great Nathan…thanks! The data I\’m working with is for water quality sampling taken on a variety of dates at the same location. So basically I end up with a bunch of stacked dots at a given geographical point. The way I view it now is to show the worst results on top using the rule-based rendering. Do you see a way to use what you\’ve addded and rule-based rendering to always display the newest result (by date) on top at a given location? Keep in mind that new samples are being taken continuously over time at a location and the newest result should always be rendered on top without the user having to adjust the rules.

    I’m using the MsSQL server driver to bring my data in if it matters.

    Thanks for the help!
    C

    • Yep. Just click on the rendering order button at the bottom and you will be given this dialog

      Set the style for the most recent feature to have a number higher then the rest this will mean it is always rendered on top. In the picture above my Today event are rendered last(higher number) so they are always on top of everything else.

      I don’t think you could do it with the rule based rendering alone as without join support you can’t really tell which is the newest point out of a collection. I would create a view in MS SQL and return it with a column that says “Recent” or “Past” based on a query in the database, or you could edit your main table I’m not sure of your workflow. You would then just have to add rule that styles based on that column. Status = “Recent”, etc.

  2. Pingback: Batch Geonews: iD OpenStreetMap Editor, GeoIQ Acquired by Esri, Predicting People’s Location, and more « 懒得折腾

  3. What is the language syntax that I should be using to construct the expressions in the box? Is it regular python or is it something else. I don’t know it and spent a long time trying to figure out how to concatenate strings as I did not know the concatenation operator.

    • The language is model off a SQL-Like language and is not Python. The concatenation operator is ||, or you can use the concat() function. Python support will be added in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s