Comparing dates 1

[jby1]

Hi

I require a query that will see if a stored date is more than a certain interval before the current date. Using an interval of 10 hours, the query would be something like:

SELECT * FROM utable
WHERE now() - utimestamp > interval '10 hours';

The problem I am having is that the interval is not fixed, and I would like to be able to store it as a value in a database table. The unit of the interval can, however, be fixed, and will almost certainly be fixed to hours.

I have not been able to find a way of doing what I require, does anybody have any ideas to what I need to do?

Cheers.

 

[rycamor]

You are aware that INTERVAL is also a datatype in PostgreSQL, right? You can just create a column for that time interval, so I don't see why you wouldn't be able to do what you want. In fact, you should be able to do it without using the interval keyword, because the left-hand side of this evaluation (now() - utable.utimestamp) automatically returns as interval type:

SELECT * FROM utable
WHERE (now() - utable.utimestamp) > utable.my_interval;

Remember, with INTERVAL, try the most intuitive thing, and its probably right ;-).

See the following:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype-datetime.html

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-datetime.html

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[jby1]

Thanks for that!

However, I cannot use an interval type, as we are using Torque to build our database, and I cannot find a Torque type that maps to interval in Postgres.

However, I have managed to get a solution to work, my new query is:

SELECT * FROM utable
INNER JOIN uinttable ON utable.utype = uinttab.utype
WHERE (now() - utable.utimestamp) > uinttab.numhrs * interval '1 hour';

where

uinttab.numhrs

is of type float.

Cheers.

 

[rycamor]

Torque can't handle INTERVAL??? That's an ANSI-standard SQL type. And I would think that any decent Java/DB framework would include support for user-defined types and domains, which have been available in Oracle and other high-end DBMSs for years.

Time to either re-check the Torque specs or move to another framework, IMHO.

Or, at least check what version of PostgreSQL and the PostgerSQl JDBC driver you are using. See

http://db.apache.org/torque/postgres-howto.html

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[rycamor]

Also see

http://db.apache.org/torque/db-adapters.html

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html