Written by

Nezla

Question Nezla · Apr 12, 2022

Dealing with time format as string

#Ensemble

Go to the original post @Nezla

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

Go to the original post written by @Nezla

Discussion (11)1

Add reply

Comments

Matjaz Murko · Apr 12, 2022

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, 2022

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

0 0

Nezla Apr 13, 2022 to Vitaliy Serdtsev

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, 2022 to Nezla

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

Nezla Apr 13, 2022 to Vitaliy Serdtsev

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

0 0

Nezla Apr 13, 2022 to Vitaliy Serdtsev

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, 2022 to Nezla

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:

<FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">PackingTimeSpent </FONT><FONT COLOR="#000080">As %Time</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">FORMAT </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">1</FONT><FONT COLOR="#000000">);</FONT>

0 0

Nezla Apr 13, 2022 to Vitaliy Serdtsev

Thank you very much the format fixed it: