我正在使用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
也就是说,每个日期都将设置为一天的结束。
很多方法。
我们正在谈论类型 timestamp
.Postgres 中的首选类型是 timestamptz
。请注意区别!看:
- 在 Rails 和 PostgreSQL 中完全忽略时区
在我的示例中使用列名ts
。 时间戳列的"日期"会产生误导。
当天最后一秒
1.
ts::date + 1 - interval '1 sec'
投射到date
,加integer '1'
,然后减去interval '1 second'
。
添加日期 + int 是唯一实际需要类型 date
的情况。这个答案中的所有其他表达式也适用于date_trunc()
- 甚至稍微快一点。
阿拉伯数字。
ts::date + interval '1 day - 1 sec'
date_trunc('day', ts) + interval '1 day - 1 sec'
Postgres 间隔输入允许具有多个时间单位的单个表达式。无需两次操作。
3.
date_trunc('day', ts) + interval '23:59:59'
更简单的是,将所需的时间部分添加到日期(或一天的开始)。
4.
date_trunc('day', ts) + time '23:59:59'
与类型 time
相同.
同一天的最后一个时间戳
xxxx-xx-xx 23:59:59
不是"一天的结束"。Postgres timestamp
数据类型(当前不太可能更改)以微秒分辨率存储值。一天的最晚可能时间戳为 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'
最后一个表达式除了正确之外,应该是最快的。
第二天开始
通常,以第二天开始作为独家上限进行操作是更好的方法。不依赖于实现细节,甚至更容易生成。
8.
ts::date + 1 -- returns date!
9.
date_trunc('day', ts) + interval '1 day' -- returns timestamp
至少从 Postgres 8.4 开始,所有版本都可以在任何版本中工作。Postgres 15 中的演示:
小提琴
取日期,截断它,加一天减去一秒:
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'
但这似乎不那么优雅。