Как получить конец дня?

Я использую PostgreSQL 8.4. У меня есть столбец таблицы my_tbl, который содержит даты (timestamp without timezone). Например:

       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

Как написать SQL-запрос, который возвращает все даты в формате:

xxxx-xx-xx 23:59:59

То есть каждая дата будет установлена ​​на конец дня.


postgresql sql date-arithmetic
person St.Antario    schedule 29.12.2014    source источник

Ответы (2)


arrow_upward
20
arrow_downward

Возьмите дату, обрежьте ее, добавьте один день и вычтите одну секунду:

select date_trunc('day', date) + interval '1 day' - interval '1 second'

Вы можете поместить логику в update, если хотите изменить данные в таблице.

Конечно, вы также можете добавить 24*60*60 - 1 секунду:

select date_trunc('day', date) + (24*60*60 - 1) * interval '1 second'

Но это кажется менее элегантным.

person Gordon Linoff    schedule 29.12.2014
comment
Просто добавим, что если вам нужна точность до миллисекунды, вы можете заменить «вторую» часть на «миллисекунду», в результате чего получится «xxxx-xx-xx 23:59:59.999». - person Rafael Bizarra; 06.09.2016
comment
Мне нравится select the_date::date + interval '1 day - 1 second'; Кроме того, не называйте столбцы зарезервированными словами. - person Merlin; 11.04.2019

arrow_upward
18
arrow_downward

Много способов.

Использование имени столбца ts в моих примерах. дата для столбца timestamp может ввести в заблуждение.

1.

Трансляция на date. (Здесь нам нужен тип date. В других выражениях date_trunc() точно так же.). Затем добавьте 1 (integer) перед вычитанием интервала 1 second:

SELECT ts::date + 1 - interval '1 sec' AS last_sec_of_day

2.

Добавьте одиночный интервал '1 day - 1 sec'. Нет необходимости в двух операциях, Postgres интервальный ввод позволяет одно выражение.

SELECT ts::date + interval '1 day - 1 sec' AS last_sec_of_day

3.

Еще проще, добавьте к дате желаемую временную составляющую:

SELECT ts::date + time '23:59:59' AS last_sec_of_day

4.

Однако xxxx-xx-xx 23:59:59 — это не конец дня. Postgres timestamp тип данных (в настоящее время, но вряд ли изменится) хранит значения с микросекундным разрешением.
Самая поздняя возможная отметка времени для дня: xxxx-xx-xx 23:59:59.999999:

SELECT ts::date + interval '1 day - 1 microsecond' AS last_ts_of_day

5.

Эквивалент:

SELECT ts::date + time '23:59:59.999999' AS last_ts_of_day  -- or interval

Это последнее выражение должно быть самым быстрым, кроме правильного.

6.

Однако лучше всего использовать начало следующего дня в качестве исключающей верхней границы, что не зависит от деталей реализации и его еще проще сгенерировать:

SELECT ts::date + 1 AS next_day

7.

Фактическая первая временная метка следующего дня:

SELECT date_trunc('day', ts) + interval '1 day' AS next_day_1st_ts

Все работает в любой версии, начиная хотя бы с Postgres 8.4. Демо в Postgres 13:

db‹›fiddle здесь

person Erwin Brandstetter    schedule 01.01.2015