how do I group by by hour in postgresql with a "time" field?

SqlPostgresql

Sql Problem Overview

I have a table with a column ctime of type time without time zone.

  |   cdate    |  ctime   
  +------------+----------
  | 2016-12-24 | 12:02:17
  | 2016-12-24 | 12:02:32
  | 2016-12-24 | 12:03:00
  | 2016-12-24 | 12:02:10

I would like to group by both cdate and ctime but would like for ctime to count only hours.

Sql Solutions

Solution 1 - Sql

use date_trunc:

group by cdate, date_trunc('hour', ctime)

Solution 2 - Sql

I think date_part('hour', ctime) is a better choice if you just want to group based on the hour.

date_part('hour', timestamp '2001-02-16 20:38:40')	  ---> 20
date_trunc('hour', interval '2 days 3 hours 40 minutes')	---> 2 days 03:00:00
Categories
Recommended Sql Solutions
Recommended Postgresql Solutions

Previous Solution:

Correct syntax for mutate_if

RDplyrNa

Next Solution:

Prevent dismissal of BottomSheetDialogFragment on touch outside

AndroidBottom Sheet

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDervin ThunkView Question on Stackoverflow
Solution 1 - SqlVao TsunView Answer on Stackoverflow
Solution 2 - SqlhaomingView Answer on Stackoverflow