How to get the end of a day?

Asked 29/12, 2014 at 12:58 Answered 1/1, 2015 at 5:24

I'm using PostgreSQL 8.4. I have a table column with timestamps (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 timestamps in the format:

xxxx-xx-xx 23:59:59

Every timestamp will be set to the end of the day.

Tolbooth answered 29/12, 2014 at 12:58 Comment(0)

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.

Hypoglossal answered 29/12, 2014 at 13:0 Comment(2)

Just to add that if you want precision to the millisecond you can replace the 'second' part with 'millisecond', ending up with 'xxxx-xx-xx 23:59:59.999'. – Cis 6/9, 2016 at 9:49

I like select the_date::date + interval '1 day - 1 second'; Also, don't name your columns after reserved words. – Cammycamomile 11/4, 2019 at 11:0

Many ways.

We are talking about the type timestamp. The preferred type in Postgres is timestamptz. Be aware of the difference! See:

Ignoring time zones altogether in Rails and PostgreSQL

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

Last second of the same day

Cast to date, add integer '1', then subtracting interval '1 second'.
```
ts::date + 1 - interval '1 sec'
```
The addition date + int is the only case that actually requires the type date. All other expressions in this answer work with date_trunc() just as well - or even ever so slightly faster.
Postgres interval input allows a single expression with multiple time units. No need for two operations.
```
ts::date              + interval '1 day - 1 sec'
date_trunc('day', ts) + interval '1 day - 1 sec'
```
Simpler yet, add the desired time component to the date (or start of the day):
```
date_trunc('day', ts) + interval '23:59:59'
```

Same thing with type time:

date_trunc('day', ts) + time '23:59:59'

Last timestamp of the same day

xxxx-xx-xx 23:59:59 is not the "end of the day". The Postgres timestamp data type (currently, and not likely to change) stores values with microsecond resolution. The latest possible timestamp for a day is xxxx-xx-xx 23:59:59.999999.

date_trunc('day', ts) + interval '1 day - 1 microsecond'

date_trunc('day', ts) + interval '23:59:59.999999'

date_trunc('day', ts) + time '23:59:59.999999'

This last expression should be fastest besides being correct.

Start of the next day

Typically, operating with the start of the next day as exclusive upper bound is the superior approach. Does not depend on implementation details and is even simpler to generate.

```
ts::date + 1  -- returns date!
```

date_trunc('day', ts) + interval '1 day'  -- returns timestamp

All work in any version since at least Postgres 8.4. Demo in Postgres 16:

fiddle

Grotto answered 1/1, 2015 at 5:24 Comment(0)