How to get the end of a day? How to get the end of a day? sql sql

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.

Many ways.

Using the column name "ts" in my examples. "date" for a timestamp column would be misleading.


Cast to date. (Here we need the type date. In other expressions, date_trunc() is just as well.). Then add 1 (integer) before subtracting the interval 1 second:

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


However, xxxx-xx-xx 23:59:59 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 xxxx-xx-xx 23:59:59.999999:

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:

db<>fiddle here