date calculations in Postgresql

Classic Servoy
1

I need to find records that are 20 days overdue. For a few years I’ve been using the following code, but now that I’ve upgraded to Postgresql it no longer works.

var maxReturnedRows = 1000;
var today = new Date();
var query = "SELECT crid FROM cr WHERE expiration_date > (? - 20)";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[today],maxReturnedRows);
controller.loadRecords(dataset);

This produces the following error:
ERROR: operator does not exist: timestamp without time zone < integer
Wrapped java.lang.RuntimeException: com.servoy.j2db.dataprocessing.DataException: ERROR: operator does not exist: timestamp without time zone < integer

How can I correctly calculate (today - 20)

2

Hi,

PostgreSQL has has a lot of date/time functions and operators you can use:
http://www.postgresql.org/docs/9.0/stat … etime.html

In your case you can use the following SQL:

SELECT crid FROM cr WHERE expiration_date > (? - interval '20 days')

Hope this helps.

3

@Roclasi
Is it better to use db independent calculations or customize for each db that one uses?
If I change DB server for another client perhaps the same code could work if it was independent?

4

Hi Tom,

In this case the query can get the date passed could already be calculated minus 20 days by Servoy and therefor be portable to other SQL vendors. But in other situations things might be (way) more efficient to do it in the database layer.
So it very much depends on the use-case to what is better.

5

I tried ```
SELECT crid FROM cr WHERE expiration_date > (? - interval ‘20 days’)


ERROR: operator does not exist: timestamp without time zone < interval

The field expiration_date is a DATETIME field in servoy. If this could be more easily done by servoy, can you advise how to do that?

thanks!
6

I see Servoy/JDBC driver doesn’t cast the date object to a timestamp. Here are 4 ways to get the result you want, 2 in plain SQL and 2 in Javascript (and then SQL):

var _d = new Date();

// using CAST() in SQL
var _sQuery = "SELECT * FROM rss_worked_hour WHERE wkh_datetime_creation > (cast(? as timestamp) - interval '20 days')";
	_ds = databaseManager.getDataSetByQuery(controller.getServerName(), _sQuery, [_d], -1);

// using ::timestamp syntax to cast in SQL (PostgreSQL only syntax)
_sQuery = "SELECT * FROM rss_worked_hour WHERE wkh_datetime_creation > (?::timestamp - interval '20 days')",
_ds = databaseManager.getDataSetByQuery(controller.getServerName(), _sQuery, [_d], -1);

// Using Javascript to calc the correct date and then pass it
_sQuery = "SELECT * FROM rss_worked_hour WHERE wkh_datetime_creation > ?";
_d = new Date();
_d = new Date(_d.getFullYear(), _d.getMonth(), _d.getDate() - 20, _d.getHours(), _d.getMinutes(), _d.getSeconds())
_ds = databaseManager.getDataSetByQuery(controller.getServerName(), _sQuery, [_d], -1);

// Using mod_datejs to calc the correct date and then pass it
// https://www.servoyforge.net/projects/mod-datejs
_sQuery = "SELECT * FROM rss_worked_hour WHERE wkh_datetime_creation > ?";
_d = new Date();
_d.addDays(-20);
_ds = databaseManager.getDataSetByQuery(controller.getServerName(), _sQuery, [_d], -1);

Hope this helps.

7

Robert, many thanks. I went with the Javascript option and it works great.

thanks!