Forum OpenACS Q&A: date arithmetic

Collapse
1: date arithmetic
Posted by Jonathan Ellis on 03/16/01 07:00 AM
Maybe this should be added to the oracle porting guide?

In oracle if I want rows older than 1 hour, I query
select ... where sysdate - date_created > 1 / 24

In postgresql the simplest way seems to be
select ... where (abstime(now()) - abstime(date_created)) > 3600

Probably there are more, but that is one I just ran into.  And I
don't have an Oracle installation to play with easily, anymore. :(

Collapse
2: Response to date arithmetic (response to 1)
Posted by Stephen van Egmond on 03/16/01 04:40 PM
If you have an index on date_created, you're subverting that index with your query. (Unless that part's been optimized since...)

This works for me, and is probably more efficient.

WHERE date_created > date_part('epoch','now') + 3600;

date_part epoch computes the number of seconds (like abstime) and adds 3600. This is a constant, which makes your index useful. I believe the query you gave will force a table scan.

Collapse
3: Response to date arithmetic (response to 1)
Posted by Jonathan Ellis on 03/16/01 05:10 PM
Thanks for the tip!  Interesting, though -- as you say, you can query "WHERE date_created > date_part('epoch','now') + 3600"

but you can't do "WHERE date_created - date_part('epoch','now') > 3600"

It knows how to compare the two, but not how to do arithmetic with them.

Collapse
4: Response to date arithmetic (response to 1)
Posted by Roberto Mello on 03/16/01 05:38 PM
Someone just posted a date arithmatic function on the Postgres CookBook. We have a whole section on date manipulation functions, that you can access at http://www.brasileiro.net/postgres/cookbook.

One interesting function is this http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=21

BTW, since OpenACS users are heavy PL/pgSQL users, I hope we'll get lots of "recipes" from this community :)