How to Round a Timestamp Up or Down to the Nearest Minute in PostgreSQL

In PostgreSQL, you can achieve rounding to the nearest minute using the date_trunc function. This function takes two arguments:

1. Unit: Specifies the time unit to which you want to truncate (round down). In this case, use the string 'minute'.
2. Timestamp: The timestamp value you want to round.

Here's the basic syntax:

SELECT date_trunc('minute', your_timestamp_column) AS rounded_timestamp;

Explanation:

1. date_trunc('minute', your_timestamp_column): This part truncates the your_timestamp_column to the beginning of the current minute. For example, if your_timestamp_column is "2024-05-22 17:14:32", this would become "2024-05-22 17:14:00".

However, this approach only truncates downward. To round to the nearest minute (up or down), we can leverage a clever trick:

1. Adding Half a Minute: We add an interval of '30 seconds' (half a minute) to the timestamp. This effectively shifts the timestamp towards the next minute boundary.
2. Truncating Again: We use date_trunc again, but this time, since the timestamp might have been pushed past the minute boundary due to the added 30 seconds, date_trunc will round it down to the nearest minute (either the current or the previous one).

Combining the Steps:

Here's the improved query that rounds to the nearest minute:

SELECT date_trunc('minute', your_timestamp_column + interval '30 seconds') AS rounded_timestamp;

This query will round the timestamp in your_timestamp_column to the nearest minute, regardless of whether the original time was closer to the beginning or the end of the minute.

Example:

Assuming your_timestamp_column has the following values:

• "2024-05-22 17:14:32" (closer to the end of the minute)

Running the query would produce:

• "2024-05-22 17:14:00" (rounded to the current minute for the first timestamp)

-- Assuming a table named "my_table" with a timestamp column named "timestamp_col"

SELECT 
  timestamp_col AS original_timestamp,
  date_trunc('minute', timestamp_col + interval '30 seconds') AS rounded_timestamp
FROM my_table;

This query selects both the original timestamp and the rounded version from the "my_table" table.

-- Assuming a specific timestamp variable named "my_timestamp"

SELECT 
  my_timestamp AS original_timestamp,
  date_trunc('minute', my_timestamp + interval '30 seconds') AS rounded_timestamp;

This method utilizes the extract and floor functions:

1. extract(epoch FROM timestamp): This extracts the timestamp value in seconds since the epoch (January 1, 1970, 00:00:00 UTC).
2. floor(extract(epoch FROM timestamp) / 60): This expression divides the epoch representation of the timestamp by 60 (seconds per minute) and then uses floor to round down to the nearest whole minute.
3. to_timestamp(...): Finally, we convert this integer representing the number of minutes since the epoch back to a timestamp using to_timestamp.

SELECT to_timestamp(floor(extract(epoch FROM your_timestamp_column) / 60)) AS rounded_timestamp;

Method 2: Using CASE Expression

This method employs a CASE expression to handle rounding based on the seconds component of the timestamp:

SELECT 
  CASE
    WHEN extract(second FROM your_timestamp_column) >= 30 THEN
      date_trunc('minute', your_timestamp_column + interval '1 minute')
    ELSE
      date_trunc('minute', your_timestamp_column)
  END AS rounded_timestamp;

• This CASE expression checks if the seconds component is greater than or equal to 30 (half a minute).
  • If true, it adds one minute to the timestamp before truncating, effectively rounding up to the next minute.
  • If false, it simply truncates to the current minute (rounding down).

Choosing the Right Method

• The first method (extract and floor) might be slightly less performant on large datasets because of the extra calculations.
• The second method (CASE expression) is generally more readable but could be less efficient for frequent rounding operations.