Rounding time to nearest hourly quarter in postgresql

Question

I have timestamp column as:

"2017-02-14 05:06:34"
"2017-01-17 13:55:44"
"2017-01-17 14:06:14"
"2017-01-17 22:40:14"
"2017-01-18 14:23:43"

and I want to round time to nearest hourly quarter in postgresql. I want to update as 1,2,3,4 quaters

The output I need is as follows:

"2017-02-14 05:06:34"  1
"2017-01-17 13:55:44"  4
"2017-01-17 14:06:14"  1
"2017-01-17 22:40:14"  3
"2017-01-18 14:23:43"  2

I tried the following command but it is not giving me as expected:

SELECT   CURRENT_TIME(0), 
CASE WHEN EXTRACT(MINUTE FROM CURRENT_TIME(0)) - 30   >= 0 
                    THEN CURRENT_TIME(0) + ((60 - EXTRACT(MINUTE FROM CURRENT_TIME(0))) * INTERVAL  '1' MINUTE)
               ELSE CURRENT_TIME(0) -  EXTRACT(MINUTE FROM CURRENT_TIME(0))  * INTERVAL  '1' MINUTE
END  AS Converted_Time from table1;

CURRENT_TIME is the column in my table.

Answer 1

You should use the following conditions for each of the quarters:
1. MINUTE FROM CURRENT_TIME(0)) - 15 <= 0
2. 0 < MINUTE FROM CURRENT_TIME(0)) - 15 <= 15
3. 15 < MINUTE FROM CURRENT_TIME(0)) - 15 <= 30
4. 30 < MINUTE FROM CURRENT_TIME(0)) - 15 <= 45

Answer 2

A few years later I came here to find how to round a timestamp to a 6 hour interval.

Here is my solution adapted to the question given:

SELECT
    DATE_TRUNC('minute', NOW())
    -
    MAKE_INTERVAL(MINS => MOD(EXTRACT(MINUTE FROM NOW())::INTEGER, 15))

Explanation:

1. DATE_TRUNC the timestamp to the minutes
2. EXTRACT the MINUTE from the timestamp
3. Convert the minute to an INTEGER
4. Get the remainder of dividing the minut by 15 (MOD)
5. Convert the result to an interval (MAKE_INTERVAL)
6. Subtract that from the truncated timestamp from 1.