How to round timestamp to nearest day with postgresql?

@samara 

You can round a timestamp to the nearest day in PostgreSQL by using the DATE_TRUNC() function in combination with the ROUND() function. Here is an example query:

1

SELECT ROUND(EXTRACT(epoch FROM TIMESTAMP '2021-09-22')/(24*60*60))::int * 24*60*60 AS rounded_timestamp;

In this query, TIMESTAMP '2021-09-22' is the timestamp you want to round to the nearest day. The EXTRACT(epoch FROM ...) function extracts the number of seconds since Unix epoch for the given timestamp. We divide this value by the number of seconds in a day (246060) to get the number of days since Unix epoch. The ROUND() function rounds this value to the nearest integer representing the number of days. We then multiply this rounded number by the number of seconds in a day to get the rounded timestamp in seconds since Unix epoch.

You can adjust the input timestamp and format of the output timestamp as needed for your use case.