How to calculate the time between different timestamps in CE_CALC

[Former Member]

Hi All

   As of now I am using daysbetween function to calculate the no.of days between two dates, suppose if I have to calculate the time difference between two timestamps, I could not find the function specific to that.Request you to provide your ideas .

Thanks

Santosh

[lbreddemann · Accepted solution]

Hello Santosh,

I don't see why you couldn't use the daysbetween() function.

Works just fine for me:

 /********* Begin Procedure Script ************/ 
 BEGIN 
       table1 = CE_COLUMN_TABLE("LARS"."TABLE9M");
     table2 = CE_AGGREGATION (:table1,[MAX (TIMES) AS "MAXTIME",MIN (TIMES) AS "MINTIME"], [L,S]);
     var_out = CE_PROJECTION(:table2, [MAXTIME, MINTIME, 
                        CE_CALC('daysbetween(date("MAXTIME"),date("MINTIME"))',int) as PTIME, L,S]);
END 
/********* End Procedure Script ************/

SELECT "MAXTIME", "MINTIME", "L", "S",
     sum("PTIME") AS "PTIME" 
FROM "_SYS_BIC"."lars/CE_TIMES" 
GROUP BY "MAXTIME",  "MINTIME",  "L", "S"

MAXTIME	MINTIME	L	S	PTIME
21.01.2013 10:44:15.894	19.01.2013 10:44:16.039	2	1	-2
21.01.2013 10:44:15.826	20.01.2013 10:44:15.979	1	1	-1

- Lars

[Former Member · Accepted solution]

Hi Lars

   I am using days between function inorder calculate the difference , but daysbetween function only accepts the date as inputparameter, but it does not accept timestamp as input parameter,

For example I have a timestamp I need to convert it as date using "date" function, suppose if I have two timestamps which have difference of  30 minutes, in that case the output would be 0. below are the sample sql queries. I tried both, in both cases result is '0'.

  select days_between(to_timestamp('20130409091307'),now()) from dummy

  select days_between(to_date('20130409091307'),now()) from dummy

Thanks

Please share your views. When i tried to pass the timestamp as input parameter it throwed me an error as daysbetween accepst only dates.I tried the below.

CE_CALC ('daysbetween(timestamp("col1"),timestamp("col2"))', integer)

[lbreddemann · Accepted solution]

Hi Varada,

ok - so you're looking for a function that returns exactly what?

Seconds? Great, since secondsbetween() is taking longdate values (a timestamp would fit!) and returns the number of seconds between both input parameter values.

If this is not what you're looking for, please be a bit more specific about what exactly you're trying to do.

- Lars

[Former Member · Accepted solution]

Hi Lars

  I tried the below as part of my CE_CALC function, but it throws error saying that timestamp is not a valid node.I checked the date functions supported by CE_CLAC but secondsbetween is not available.

ce_calc('if (isnull("STATUS_B"), ''STILL EVAL PHASE'',   string(secondsbetween(timestamp("CHANGE_TIME_EVAL"), timestamp("CHANGE_TIME_CREATE")))) ',NVARCHAR(35))  as "IS_DAYS_EVAL"]);

I am looking to calcaulate the timedifference between two timestamps, the difference could be seconds, Minutes , Hours or days, if the difference between two timestamps is seconds, I should be able to diaply the number and unit along with that suppose the difference is 2 days 15 Hours 45 minutes  and 21 sec.can we acieve this using CE_CALC functions.Please share your ideas.

Thanks

Santosh

[lbreddemann · Accepted solution]

Varada,

please use the longdate() conversion function instead of timestamp() (have you checked if this function exist at all? You know, the developer guide is there to use it... )

The output of secondsbetween() will always be, well, seconds...

The formatting you're after is not something that should be done in the calculation view or in the model at all.

This kind of stuff is something the client software best takes care of.

There you can define what appropriate time/date/unit formats look like.

- Lars

[Former Member · Accepted solution]

Hi Lars

   Thanks for that pointer, it worked as I expected.

  There exist to_timestamp function similar to to_date, I was under the impression that timestamp would be similar to date in CE_CALC function.I just checked it does not exist in developer guide , sorry for that.

Thanks

Santosh

[Former Member]

Hi Santosh,

component() function should suit your needs. Examples (pseudocode):

SOME_DATE DATE := '2012-01-01';

SOME_TIME TIME := '13:22:03';

CE_CALC('component('component("SOME_DATE", 1)', INTEGER) AS YY;

CE_CALC('component('component("SOME_DATE", 2)', INTEGER) AS MM;

CE_CALC('component('component("SOME_DATE", 3)', INTEGER) AS DD;

CE_CALC('component('component("SOME_TIME", 1)', INTEGER) AS HH;

CE_CALC('component('component("SOME_TIME", 2)', INTEGER) AS MM;

CE_CALC('component('component("SOME_TIME", 3)', INTEGER) AS SS;

Add a bit of logic and you shouldn't have any problems for what you ned.