PostgreSQL: How to change attributes of a timestamp

It's generally not trivial to change a datetime's seconds, minutes, etc in SQL. Here is how it works when speaking PostgreSQL.

Consider you have a timestamp column whose seconds you want to zero:

CopySELECT born_at FROM users;
       born_at
---------------------
 2015-05-01 13:37:42

You can the TO_CHAR function to convert date or time values into a string, and do your changes there:

CopySELECT TO_CHAR(born_at, 'YYYY-MM-DD HH24:MI:00') FROM users;
       to_char
---------------------
 2015-05-01 13:37:00

Note that this string value can not be used to do other date/time operations, e.g. when you want to UPDATE a datetime column. In such cases, you need to convert your string back into a datetime using TO_TIMESTAMP.

CopySELECT TO_TIMESTAMP(TO_CHAR(born_at, 'YYYY-MM-DD HH24:MI:00'), 'YYYY-MM-DD HH24:MI:SS') FROM users;
      to_timestamp
------------------------
 2015-05-01 13:37:00+01

Check the PostgreSQL documentation on date type formatting functions Show archive.org snapshot for more information.