Menu
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.
690
Discussion: To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.
PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
You can use \timing only with the command line client psql , since this is a psql command. It is a switch that turns execution time reporting on and off: test=> \timing Timing is on.
use date_trunc:
group by cdate, date_trunc('hour', ctime)
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
