How to group dates by week in PostgreSQL?

Question

PostgreSQL

MAIN_TABLE table. It stores the time value ( timestamp ) and the speed recorded at that time ( float8 ).

|      DATE_TIME      | SPEED |
|---------------------|-------|
| 2018-11-09 00:00:00 | 256   |
| 2018-11-09 01:00:00 | 659   |
| 2018-11-09 02:00:00 | 256   |
|    остальные даты   | xxx   |
| 2018-11-21 21:00:00 | 651   |
| 2018-11-21 22:00:00 | 515   |
| 2018-11-21 23:00:00 | 849   |

There is a time interval. Suppose from November 9 to November 21. Does PostgreSQL have a built-in ability to split this time into weeks. To be more precise, I'm trying to get the average speed over the weeks.

|      DATE_TIME      | AVG_SPEED |
|---------------------|-----------|
| 9-11 November       | XXX       |
| 12-18 November      | YYY       |
| 19-21 November      | ZZZ       |

MySQL, for example, has a WEEK() function that returns the week number, which can be used to group dates by week. Is there anything similar in PostgreSQL 10.4 ?

Answer 1

There is
EXTRACT(WEEK from DATE_TIME)