14

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
St.Antario
  • 26,175
  • 41
  • 130
  • 318

2 Answers2

32

Many ways.

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

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

Last second of the same day

1.

ts::date + 1 - interval '1 sec'

Cast to date, add integer '1', then subtracting interval '1 second'.

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.

2.

ts::date              + interval '1 day - 1 sec'
date_trunc('day', ts) + interval '1 day - 1 sec'

Postgres interval input allows a single expression with multiple time units. No need for two operations.

3.

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

Simpler yet, add the desired time component to the date (or start of the day).

4.

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

Same thing with type time.

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.

5.

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

6.

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

7.

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.

8.

ts::date + 1  -- returns date!

9.

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

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

fiddle

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
22

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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'. – Rafael Bizarra Sep 06 '16 at 09:49
  • I like `select the_date::date + interval '1 day - 1 second';` Also, don't name your columns after reserved words. – Merlin Apr 11 '19 at 11:00