How to get to the end of the day?
I am using PostgreSQL 8.4
. I have a table column my_tbl
that contains dates ( timestamp without timezone
). For example:
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 a SQL query that returns all dates in the format:
xxxx-xx-xx 23:59:59
That each date is set at the end of the day.
source to share
Take a date, truncate it, add one day, and subtract one second:
select date_trunc('day', date) + interval '1 day' - interval '1 second'
You can put logic in 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.
source to share
Just showing. Shorter and faster than date_trunc()
. Add 1
(integer) before subtracting the interval 1 second
:
SELECT date::date + 1 - interval '1 sec' AS last_sec_of_day FROM my_tbl;
Or just add spacing '1 day - 1 sec'
. No need for two operations, the input interval in Postgres can take both in one step:
date::date + interval '1 day - 1 sec' AS last_sec_of_day
Or, even easier, just add the desired time component to the date:
date::date + '23:59:59'::time AS last_sec_of_day
However, it does not correspond to the end of the day. The Postgres data type stores values in microsecond resolution.
The most recent possible timestamp for the day is : xxxx-xx-xx 23:59:59
timestamp
xxxx-xx-xx 23:59:59.999999
date::date + interval '1 day - 1 microsecond' AS last_ts_of_day
date::date + '23:59:59.999999'::time AS last_sec_of_day
The last expression should be faster than the correct one .
Typically, an excellent approach is to use the next day's date as an exclusive upper bound , which is even easier to generate:
date::date + 1 AS next_day
SQL Fiddle for pg 8.4. The same works in the current version 9.4.
Also: I would not name the column timestamp "date"
, which is misleading. It is also a reserved word in standard SQL and the base type name in Postgres and should not be used as an identifier at all.
source to share