Postgres has a type called "interval" to deal with these kinds of
situations. As you might expect, an interval is a duration of time, as
opposed to a fixed time ordinate like date or timestamp. You can add
and subtract interval values from dates and timestamps. Intervals need
to be specified as strings and then cast to interval. You can do this
in two ways:
interval '5 days'
'5 days'::interval
So to get "five days ago", you would use
now() - interval '5 days'
Similarly, to get 40 minutes into the future
now() + interval '40 minutes'
The resultant date or timestamp value can then be expressed in whatever
format you please by using to_char()
BJ
Neil Zanella wrote:
>Hello,
>
>I know that PostgreSQL, like most database management systems, has a
>function
>call called NOW() that returns the current date. Is there a way to
>return a datein PostgreSQL such that the output is in ISO 8601 format
>(Unix 'date -I' format)but such that the date is not "today"'s date
>but the date two days ago or five
>days ahead of now? I have tried something like NOW() + 5 but that did
>not work
>(because the data types are incompatible, and SELECT NOW() +
>'0000-00-01' does
>not work either. I get the error:
>
>ERROR: Bad interval external representation '0000-00-01'
>
>Thanks,
>
>Neil
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>