How to get the end of a day?
date-arithmetic, postgresql, sql
Solution
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.
Problem
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.