pgSQL日期函数

Question

如何使用pgSQL的date函数调整这个pgSQL选择，以便它总是返回日期#的"01“和时间的"00:00:00”？

SELECT s.last_mailing + '1 month'::interval AS next_edition_date FROM

last_mailing被定义为

last_mailing timestamp without time zone

我想要的结果有：

2015-10-01 00:00:00
2015-11-01 00:00:00
2015-12-01 00:00:00

Answer 1

你在找date_trunc()。

psql (9.5alpha2, server 9.4.4)
Type "help" for help.

testdb=# create table subscriptions as select 1 "id", '2015-07-14T12:32'::timestamp last_mailing union all select 2, '2015-08-15T00:00';
SELECT 2
testdb=# select * from subscriptions;                                                                                            id |    last_mailing     
----+---------------------
  1 | 2015-07-14 12:32:00
  2 | 2015-08-15 00:00:00
(2 rows)

testdb=# select *, date_trunc('month', last_mailing) + interval '1 month' AS next_edition_date from subscriptions;
 id |    last_mailing     |  next_edition_date  
----+---------------------+---------------------
  1 | 2015-07-14 12:32:00 | 2015-08-01 00:00:00
  2 | 2015-08-15 00:00:00 | 2015-09-01 00:00:00
(2 rows)

Answer 2

如果希望下一个月的第一天，请使用：

SELECT (s.last_mailing - (1 - extract(day from s.last_mailing)) * interval '1 day') + 
       interval '1 month'  AS next_edition_date
FROM . . .

如果你不想要时间，那就使用date_trunc()

SELECT date_trunc('day',
                  (s.last_mailing - (1 - extract(day from s.last_mailing)) * interval '1 day') + 
                  interval '1 month'
                 ) AS next_edition_date
FROM . . .