Dealing with time format as string

Question

Question

Rochdi Badis · Apr 12, 2022

#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

Discussion (11)1

Log in or sign up to continue

Vitaliy Serdtsev · Apr 13, 2022

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

Vitaliy Serdtsev · Apr 13, 2022

As I wrote above, there are two ways to solve:

change TimeFormat in the locale. Query in this case will be simple.
See ##class(%SYS.NLS.Format).SetFormatItem,
Configuring National Language Support (NLS).
complicate query.
See Time-to-String Conversion

Which option do you choose?

0 0

score 0 · Answer 1 · 2022-04-12T19:45:34-04:00

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ž

score 0 · Answer 2 · 2022-04-13T02:52:42-04: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

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

Class dc.test Extends %Persistent
{

Property t As %Time;

ClassMethod Test()
{
  
  d ..%KillExtent()
  
  &sql(insert into dc.test(t) values(30))
  &sql(insert into dc.test(t) values(303))
  
  zw ^dc.testD

  w !
  
  s st=##class(%SQL.Statement).%New()
  s st.%SelectMode=2
  d st.%ExecDirect(.st,"select t, %internal(t) tINT, %external(t) tSTR, avg(t) average,CAST(+avg(t) as TIME) avgSTR from dc.test").%Display()

  ; or
  w !!
  
  d ##class(%SQL.Statement).%ExecDirect(,"select t, %internal(t) tINT, %external(t) tSTR, avg(t) average,%external(CAST(+avg(t) as TIME)) avgSTR from dc.test").%Display()
}

}

Output:

USER>d ##class(dc.test).Test()
^dc.testD=2
^dc.testD(1)=$lb("",30)
^dc.testD(2)=$lb("",303)

t	tINT	tSTR	average	avgSTR
00:00:30	30	00:00:30	166.5	00:02:46
00:05:03	303	00:05:03	166.5	00:02:46

2 Rows(s) Affected

t	tINT	tSTR	average	avgSTR
30	30	00:00:30	166.5	00:02:46
303	303	00:05:03	166.5	00:02:46

2 Rows(s) Affected

score 0 · Answer 3 · 2022-04-13T07:11:29-04:00

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

score 0 · Answer 4 · 2022-04-13T07:44:04-04:00

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

score 0 · Answer 5 · 2022-04-13T07:57:39-04:00

Rochdi Badis · Apr 13, 2022

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

0 0

score 0 · Answer 6 · 2022-04-13T07:33:19-04:00

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

score 0 · Answer 7 · 2022-04-13T08:20:00-04:00

Thank you very much the format fixed it:

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

score 0 · Answer 8 · 2022-04-13T08:27:57-04:00

Vitaliy Serdtsev · Apr 13, 2022

Rochdi, please be attentive.

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

0 0

score 0 · Answer 9 · 2022-04-13T08:41:28-04:00

Rochdi Badis · Apr 13, 2022

avg trancated the second as before:

0 0