Question

Explain the following code. What language? What does it do? What is the result?

SELECT date_trunc('month',date),avg(value)

FROM rain

GROUP BY date_trunc ('month',date)

ORDER BY date_trunc('month',date);

Answer 1

It is PostgreSQL.

The code can be explained as PostgreSQL Date functions.

The DATE_TRUNC function rounds a timestamp value to a specified interval, which allows you to count events. Here you can round off a timestamp.

The DATE_TRUNC syntax looks like this: DATE_TRUNC('interval',timestamp).

For example, SELECT DATE_TRUNC('day','2015-04-12 14:44:18') would return a result of 2015-04-12 00:00:00.

You can isolate the month of the visit with DATE_TRUNC.

SELECT DATE_TRUNC('month',occurred_at) AS month
  FROM demo.web_events
 WHERE occurred_at BETWEEN '2015-01-01' AND '2015-12-31 23:59:59'

To return a count of web visits each month by channel, add the channel column and a COUNT to the SELECT statement, then group by month and channel. (Since month and channel are the first two values in your SELECT statement, you can GROUP BY 1,2), like this:

SELECT DATE_TRUNC('month',occurred_at) AS month,
       channel,
       COUNT(id) AS visits
  FROM demo.web_events
 WHERE occurred_at BETWEEN '2015-01-01' AND '2015-12-31 23:59:59'
 GROUP BY 1,2

Finally, use ORDER BY 1,2 to organize your results chronologically (by month) and alphabetically (by channel).

SELECT DATE_TRUNC('month',occurred_at) AS month,
       channel,
       COUNT(id) AS visits
  FROM demo.web_events
 WHERE occurred_at BETWEEN '2015-01-01' AND '2015-12-31 23:59:59'
 GROUP BY 1,2
 ORDER BY 1,2    

In Mode, you can build a line chart to visualize the query results.

Here, from the given code we can plot the graph between the rain avg value to the specific month.

Date will be truncked here.