PostgresSQLでの日付の加算減算

質問

PostgresSQLで2日前の日付を取得するにはどうすればいいでしょうか？

回答No.3

guuman

gomidb=# create table gomitabley(a timestamp,b text);
CREATE TABLE
gomidb=# insert into gomitabley values('2005-9-15 11:12:13','a');
INSERT 17315 1
gomidb=# insert into gomitabley values('2005-9-16 11:12:13','b');
INSERT 17316 1
gomidb=# insert into gomitabley values('2005-9-17 11:12:13','c');
INSERT 17317 1
gomidb=# insert into gomitabley values('2005-9-18 11:12:13','d');
INSERT 17318 1
gomidb=# insert into gomitabley values('2005-9-19 11:12:13','e');
INSERT 17319 1
gomidb=# insert into gomitabley values('2005-9-20 11:12:13','f');
INSERT 17320 1
gomidb=# insert into gomitabley values('2005-9-21 11:12:13','g');
INSERT 17321 1
gomidb=# select * from gomitabley;
a | b
---------------------+---
2005-09-15 11:12:13 | a
2005-09-16 11:12:13 | b
2005-09-17 11:12:13 | c
2005-09-18 11:12:13 | d
2005-09-19 11:12:13 | e
2005-09-20 11:12:13 | f
2005-09-21 11:12:13 | g
(7 rows)

gomidb=# select * from gomitabley where date(a)-2<=date(now());
a | b
---------------------+---
2005-09-15 11:12:13 | a
2005-09-16 11:12:13 | b
2005-09-17 11:12:13 | c
2005-09-18 11:12:13 | d
2005-09-19 11:12:13 | e
(5 rows)

gomidb=#

回答No.2

guuman

gomidb=# create table gomitablex(a date,b text);
CREATE TABLE
gomidb=# insert into gomitablex values('2005-9-12','a');
INSERT 17294 1
gomidb=# insert into gomitablex values('2005-9-13','b');
INSERT 17295 1
gomidb=# insert into gomitablex values('2005-9-14','c');
INSERT 17296 1
gomidb=# insert into gomitablex values('2005-9-15','d');
INSERT 17297 1
gomidb=# insert into gomitablex values('2005-9-16','e');
INSERT 17298 1
gomidb=# insert into gomitablex values('2005-9-17','f');
INSERT 17299 1
gomidb=# insert into gomitablex values('2005-9-18','g');
INSERT 17300 1
gomidb=# insert into gomitablex values('2005-9-19','h');
INSERT 17301 1
gomidb=# select * from gomitablex;
a | b
------------+---
2005-09-12 | a
2005-09-13 | b
2005-09-14 | c
2005-09-15 | d
2005-09-16 | e
2005-09-17 | f
2005-09-18 | g
2005-09-19 | h
(8 rows)

gomidb=# select * from gomitablex where a-2<=current_date;
a | b
------------+---
2005-09-12 | a
2005-09-13 | b
2005-09-14 | c
2005-09-15 | d
2005-09-16 | e
2005-09-17 | f
2005-09-18 | g
(7 rows)

gomidb=#

回答No.1

guuman

select current_date-2;

mysql:
select current_date-interval 2 day;

ブランクを忘れるとout

補足

SELECT * FROM abc
WHERE date_trunc('day', a-2) <= date_trunc('day', now())
aというカラムの2日前の日付を今日の日付を比べたい場合に上のように書くとエラーになるのですが、どのように書く必要があるんでしょうか？