I already tried option 1 with Timeformat=1 for the avg and didn't work.

Have you changed the TimeFormat at the process or system level?
If at the process level, then most likely you have changed this value in one process, and are executing the query in another. Therefore, there is no effect.
If at the system level, then the query should work.

To avoid uncertainty, change the definition of the field

Property PackingTimeSpent As %Time;

and execute in SMP for Display Mode

select PackingTimeSpent from MSDS_Serenity.KitlabelAssoc

The hh:mm:ss format string should be displayed.

so eg. select %external(CAST(+avg(166.38) as TIME)) didn't work for me

Two remarks:

• this will work as intended only if you have changed the TimeFormat=1 at the system level
• avg(166.38) - it's pointless to write like that.

you mentioned the example above and I'm not sure to which one are youi refering to with so many replies, so can you point me to which one exactly

See Time-to-String Conversion^link

Try this (does not depend on the TimeFormat value of the current locale):

select TO_CHAR(avg(PackingTimeSpent),'HH24:MI:SS') average from MSDS_Serenity.KitlabelAssoc where label='00007IT4'

PS: By the way, I noticed in your screenshots the differences in queries:
somewhere you write

where label='00007IT4'

somewhere you write

where ID='00007IT4'

Is this how it should be?
To avoid unnecessary questions, please do not change everytime the names of the fields in the queries. This is misleading.

If the data already exists, then this is a non-trivial task, especially if inheritance or Parent/Child is present, since this will lead to a change in the storage scheme of your data.

The easiest way to do this is through an intermediate (temporary) table:

1. create a new class with the same structure, but with a new primary key;
2. move data from the old class into it using SQL (not the merge command);
3. delete data/indexes in the old class, then change the primary key in it;
4. move data from the new class to the old class, using the merge command;
5. delete the new class with the data;
6. rebuild the indexes if there are any.

Useful links:
MERGE
Persistent Objects and InterSystems IRIS SQL
Introduction to Persistent Objects

If you feel insecure with Caché/IRIS, it is better to ask WRC for help.

From doc:

AVG returns either NUMERIC data type values or DOUBLE data type values. If expression is data type DOUBLE, AVG returns DOUBLE; otherwise, it returns NUMERIC.

For non-DOUBLE expression values, AVG returns a double-precision floating point number.^proof

The specification of the field [%Time(FORMAT=1)] plays absolutely no role in the case of an aggregate function, since AVG returns just a number (DOUBLE or NUMERIC).

Above I gave links to examples of how a number can be converted to TIME, then to STRING.

For example:

1. if in the current locale TimeFormat = 1
   select %external(CAST(+166.38 as TIME))

Important: it is necessary to convert a float number to an integer type, otherwise you will get zero.

2. if in the current locale TimeFormat <> 1
   select TO_CHAR(166.38,'HH24:MI:SS')

Try other methods/queries of this class, such as Dump*, ProcessList, ConnectionList, etc., which return the number of connections.

It is very strange that the mechanism ^%SYS("CSP", "MimeFileClassify") does not work for you.

According to the sources of %CSP.StreamServer, it can be seen that all the work takes place in the FileClassify method, where ^%SYS("CSP", "MimeFileClassify") is also used.

I used ZEN on Caché and all was fine.

Can you give a simple CSP example to reproduce your situation?

This may be useful to you:

• LogRollback
• Transactions and Savepoints
• Rolling Back Incomplete Transactions

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

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

Which option do you choose?

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);

By default, data in the global is stored as

glbD(ID)=$LB(%%CLASSNAME,prop1,prop2,..,propN)

The total size of the string cannot exceed 3641144. Therefore, if you have a field length >3.6E6, and there are several such fields, the limit is exceeded. To work around this, you need to change storage for your class.

For example so:

glbD(ID)=$LB(%%CLASSNAME)
glbD(ID,"prop1")=prop1
glbD(ID,"prop2")=prop2
...
glbD(ID,"propN")=propN

Class dc.test Extends %Persistent
{

Index mySimilarityIndex On SettingsJSON(KEYS) [ Data = SettingsJSON(ELEMENTS) ];

Index mySimilaritybackupIndex On SettingsJSONbackup(KEYS) [ Data = SettingsJSONbackup(ELEMENTS) ];

Property SettingsJSON As %Text(LANGUAGECLASS = "%Text.English", MAXLEN = 3600000, SIMILARITYINDEX = "mySimilarityIndex");

Property SettingsJSONbackup As %Text(LANGUAGECLASS = "%Text.English", MAXLEN = 3600000, SIMILARITYINDEX = "mySimilaritybackupIndex");

ClassMethod Test()
{
  
  d ..%KillExtent()
  
  s json=$tr($j("",3600000)," ","0")
  &sql(insert into dc.test(SettingsJSON,SettingsJSONbackup) values(:json,:json))
  
  w $l(json),":",SQLCODE
}

Storage Default
{
<Data name="backup">
  <Attribute>SettingsJSONbackup</Attribute>
  <Structure>node</Structure>
  <Subscript>"SettingsJSONbackup"</Subscript>
</Data>
<Data name="json">
  <Attribute>SettingsJSON</Attribute>
  <Structure>node</Structure>
  <Subscript>"SettingsJSON"</Subscript>
</Data>
<Data name="testDefaultData">
  <Value name="1">
    <Value>%%CLASSNAME</Value>
  </Value>
</Data>
<DataLocation>^dc.testD</DataLocation>
<DefaultData>testDefaultData</DefaultData>
<IdLocation>^dc.testD</IdLocation>
<IndexLocation>^dc.testI</IndexLocation>
<StreamLocation>^dc.testS</StreamLocation>
<Type>%Library.CacheStorage</Type>
}

}