How to get the end of a day?
Take the date, truncate it, add one day and subtract one second:
select date_trunc('day', date) + interval '1 day' - interval '1 second'
You can put the logic in an
update if you want to change the data in the table.
Of course, you can also add 24*60*60 - 1 seconds:
select date_trunc('day', date) + (24*60*60 - 1) * interval '1 second'
But that seems less elegant.
Using the column name "ts" in my examples. "date" for a
timestamp column would be misleading.
date. (Here we need the type
date. In other expressions,
date_trunc() is just as well.). Then add
integer) before subtracting the interval
SELECT ts::date + 1 - interval '1 sec' AS last_sec_of_day
Add a single interval
'1 day - 1 sec'. No need for two operations, Postgres interval input allows a single expression.
SELECT ts::date + interval '1 day - 1 sec' AS last_sec_of_day
Simpler yet, add the desired time component to the date:
SELECT ts::date + time '23:59:59' AS last_sec_of_day
is not the "end of the day". The Postgres
timestamp data type (currently, but unlikely to change) stores values with microsecond resolution.
The latest possible timestamp for a day is
SELECT ts::date + interval '1 day - 1 microsecond' AS last_ts_of_day
SELECT ts::date + time '23:59:59.999999' AS last_ts_of_day -- or interval
This last expression should be fastest besides being correct.
However, the superior approach typically is to operate with the start of the next day as exclusive upper bound, which does not depend on implementation details and is even simpler to generate:
SELECT ts::date + 1 AS next_day
The actual first timestamp of the next day:
SELECT date_trunc('day', ts) + interval '1 day' AS next_day_1st_ts
All work in any version since at least Postgres 8.4. Demo in Postgres 13: