Nonstop SQLCI: How to use time function in query.

[neeraj kumar]

Hi all,

I am stuck with some sqlci query logic, where I need to do select count(*) in the sqlci where the date and time < now - 5 minutes.

For Now-5 minutes, i have written the logic which subtract time by 5 minutes.

#SET minute_cal [#COMPUTE [now] - 30000]
#SETMANY year month day hour minute second micsec, [#CONTIME [minute_cal]]

But i am facing problem to plot query. I have refereed manual, i got DATETIME function over there, but when i tried i got syntax error.

#APPEND l_in select
#APPEND l_in count(*)
#APPEND l_in FROM =TABLE where
#APPEND l_in ((DATETIME) < ([year][month1][day1], [hour][minute][second][micsec]))
#APPEND l_in browse access;

Please suggest if anyone have used or run similar type of query.

My requirement is to select all the row count where were in table, whose time is delayed by 5 minutes or more

Thank you in advance.

[wbreidbach]

I think what you are intending to do is much easier than you think- SQLCI has the current function. Here an example from one of my queries:
statistics_time >=
current year to minute - interval "1" minute

statistics_time is defined as DATETIME YEAR TO MINUTE.
As far as I understood your column is defined as year to fraction, so you just have to use FRACTION instead of MINUTES and the correct value for interval, of course. Do not forget the quotes, they are mandatory!

[neeraj kumar]

Hi wbreidbach,

Actually my requirement is not to use any table column, i need to fire this on the bases of system date time and if it is delayed by 5 minutes, then the count of that.

For example like in simple sql there is function like Now()

SELECT ProductName, Price, Now() AS PerDate
FROM Products;
so it will display all the rows for current time that can be captured from now() function.

[wbreidbach]

The error is that you did not specify the type of datetime (see below). In addition the format of date and time was not correct. Abother problem is that (DATETIME) should be the name of a table column and because DATETIME is a reserved word there cannot be a column named DATETIME, just replace (DATETIME) with the name of the column you want to examniate.

The current function uses the actual system time but if you prefer using the TACL statemente I am fine with that, just change it like this:

#APPEND l_in select
#APPEND l_in count(*)
#APPEND l_in FROM =TABLE where

#APPEND l_in (<column-name> < datetime "[year]-[month1]-[day1]-[hour]:[minute].[second].[micsec])" year to fraction
#APPEND l_in browse access;

But you should be careful, if your date and time field is not part of a key such a statement will very likely result in a table scan and depending on the size of the table that could last very long.

[wbreidbach]

Oops, there should be a colon between day and hour:...[day1]:[hour]:...

[JShepherd]

In article <1348b132-6a79-4ad6...@googlegroups.com>,
neeraj.k...@gmail.com says...

CURRENT is a function that returns the current local time
as a DATETIME.

>>select id, current year to second from $data.sql.junk;

ID (EXPR)
--------- -------------------
9999 2015-12-29:15:16:31

[neeraj kumar]

Thank you wbreidbach and JShepherd for explanation and help, you guys are genius :)

[wbreidbach]

Am Mittwoch, 30. Dezember 2015 12:34:20 UTC+1 schrieb neeraj kumar:
> Thank you wbreidbach and JShepherd for explanation and help, you guys are genius :)

Glad I could help.