Date/time operations for statistical analysis

[mmd-osm]

Motivation: https://wiki.openstreetmap.org/wiki/Overpass_API/Overpass_API_by_Example#Alternative_that_also_checks_the_syntax_of_the_collection_time

Frequent requirement is to classify objects by their age (timestamp()), or some date in one of its fields ("last_checked"). This would require to do some calculations based on timestamps, e.g. comparing an object timestamp with the current timestamp, extracting a number of years (interval).

• now()

• current_timestamp()

• current_date()

• handling intervals, adding & substracting date / time / intervals

Some ideas in: https://www.postgresql.org/docs/9.1/static/functions-datetime.html

• extract( ... , ) : Extract year, month, day, hour, ... from timestamp
• date_trunc() - normalize timestamp to day, week, quarter, year

SQLServer uses:

• datediff( ... )