How to substract timestamp without timezone in PostgreSQL

Question

I'm currently using PostgreSQL 8.4 and need to substract from now() date on day. What I tried is now() - 1 and it didn't work. Although that page said that we could use operator - to perform operations on date. More preciseli I recieved

ERROR:  operator does not exist: timestamp with time zone - integer

Any ideas?

Unrelated, but: 8.4 is really old and no longer maintained. You should plan an upgrade to 9.3 (or even 9.4) as soon as possible. — user330315
– user330315, Commented Dec 15, 2014 at 8:53
@a_horse_with_no_name You're absolutely right. But these are our production needs to use that version. We just don't have time to migrate to later versions. — St.Antario
– St.Antario, Commented Dec 15, 2014 at 9:02
@a_horse_with_no_name BTW, is such a migration gonna be painful? — St.Antario
– St.Antario, Commented Dec 15, 2014 at 9:03
Probably not that painful. The migration from 8.3 to 8.4 probably was more "painful" because of the stricter datatype checking in 8.4 But apart from the changed bytea wire encoding I don't recall any major differences (8.4 is just too long ago...) — user330315
– user330315, Commented Dec 15, 2014 at 9:05
You can subtract an integer from a DATE, but not from a TIMESTAMP. Check the manual, it's all there: postgresql.org/docs/8.4/interactive/functions-datetime.html — Frank Heikens
– Frank Heikens, Commented Dec 15, 2014 at 9:10

Ye Win · Accepted Answer · 2014-12-15 08:32:49Z

2

PostgreSQL can't use mathematical functions directly I think. But we can use date function such interval function in PostgreSQL will solve your problem.

Try below subtract eg:

select interval '2 day' - interval '1 day';
select now() - interval '1' ;

You can also refer to PostgreSQL 8.4 documentation.

http://www.postgresql.org/docs/8.4/static/functions-datetime.html

answered Dec 15, 2014 at 8:32

Ye Win

2,11817 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Marlowe Over a year ago

It's not about using math directly, it's about types. When you subtract from a date you need to subtract an interval of some kind. a second, a minute, a day etc. OTherwise your explanation makes great sense.

Collectives™ on Stack Overflow

How to substract timestamp without timezone in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related