New post
Question
Rochdi Badis · Apr 12

Dealing with time format as string

#Ensemble

Hi Guys,

I'm using  seconds counter then save those seconds in a field defined as string where eg. I do :

S time=30  

S time = $zt(time,1)  

Update myclass se timeSpent = :time

which now shows up as 00:00:30 if I do an sql query which good but the problem if I run a query to get average timeSpent I get 00:00.

having the field defined as integer would allow selecting avg but running a query in my Zen page wouldn’t show me a time format

so how can I get an average on my a filed defined as string in a sql query?

thanks

Product version: Caché 2014.1
0
0 230
Discussion (11)1
Log in or sign up to continue
Matjaz Murko · Apr 12

Hi.

Try this:

select convert(varchar,dateadd(ss,avg(datepart(hour,timeSpent)*3600+datepart(minute,timeSpent)*60+datepart(second,timeSpent)),0),108) from myclass

Regards,
Matjaž

0 0
Vitaliy Serdtsev · Apr 13
having the field defined as integer would allow selecting avg but running a query in my Zen page wouldn’t show me a time format

If you the field is defined as %Integer or %String, then this is incorrect. The field type must be %Time. In this case, a time will be stored in the database as number, and the query will display the time as a string.

Here is a small example:

select 166 seconds,%external(cast(+166.5 as TIME)) "avg"

Output:

seconds = 166
avg = 00:02:46
 
Simple sample
0 0
Rochdi Badis · Apr 13

Actually I did, but the SQL query didn't pick up the seconds .

this my field def:

then quering in logical mode looks ok:

but on Display or ODBC mode, comes up as 00:00

thanks

0 0
Vitaliy Serdtsev · Apr 13

What locale are you using? I use "rusw".

Try to do the following in SMP:

update MSDS_Serenity.KitlabelAssoc set PackingTimeSpent=35000 where label='00007IT4'

then

select

  PackingTimeSpent,
  %internal(PackingTimeSpent) ptsLOGICAL,
  %external(PackingTimeSpent) ptsDISPLAY,
  %odbcout(PackingTimeSpent)  ptsODBC

from MSDS_Serenity.KitlabelAssoc where label='00007IT4'

What are your results?

I have the following displayed (for DISPLAY mode):

PackingTimeSpent = 09:43:20
ptsLOGICAL = 35000
ptsDISPLAY = 09:43:20
ptsODBC = 09:43:20
0 0
Rochdi Badis · Apr 13

I'm using SMP and it's still not showing the seconds (see below):

0 0
Rochdi Badis · Apr 13

and if I set my field to  166 seconds it show 00:02, so for some reason it's not showing the seconds?

0 0
Vitaliy Serdtsev · Apr 13

Most likely, your locale uses TimeFormat = 2 (see tformat)

You can change your locale or explicitly specify the format of the field, for example like this:

Property PackingTimeSpent As %Time(FORMAT = 1);
0 0
Rochdi Badis · Apr 13

Thank you very much the format fixed it:

but the only problem is that the average function brings up the logical mode not display!?

0 0
Vitaliy Serdtsev · Apr 13

Rochdi, please be attentive.

Above I already led an example with AVG (see spoiler "Simple sample").

0 0
Rochdi Badis · Apr 13

avg trancated the second as before:

0 0