How to get the end of a day?

Question

I'm using PostgreSQL 8.4. I have a column of the table my_tbl which contains dates (timestamp without timezone). For instance:

       date
-------------------
2014-05-27 12:03:20
2014-10-30 01:20:03
2013-10-19 16:34:34
2013-07-10 15:24:26
2013-06-24 18:15:06
2012-07-14 07:09:14
2012-05-13 04:46:18
2013-01-04 21:31:10
2013-03-26 10:17:02

How to write an SQL query which returns all dates in the format:

xxxx-xx-xx 23:59:59

That's every date will be set to the end of the day.

Answer 1

Many ways.

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

1.

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

2.

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

3.

Simpler yet, add the desired time component to the date:

SELECT ts::date + time '23:59:59' AS last_sec_of_day

4.

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

5.

Equivalent:

SELECT ts::date + time '23:59:59.999999' AS last_ts_of_day  -- or interval

This last expression should be fastest besides being correct.

6.

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

7.

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