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.
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.