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:
SELECT 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:
SELECT 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.
SELECT 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.
Posted by Arne Hartherz to makandra dev (2015-04-08 07:23)