New post
Question
Rochdi Badis · Apr 26

CAST an average time problem

#Ensemble

Hi guys,

I have a %Time filed with (Format=1) to allow showing hh.mm.ss and the field is showing fine in display mode eg:

 

it should show 00:07:30  but the seconds are cut off from it as below :

 

any ideas pls?

 

thanks

Product version: Caché 2014.1
0
0 149
Discussion (8)1
Log in or sign up to continue
Rochdi Badis · Apr 26

Thanks  Vitaliy, you answers has been very helpful in many ways, but as you can see I've tried options from your simple sample with the queries you provided but it came up empty as attached, could it be the Ensemble version 2014?

Thanks

0 0
Vitaliy Serdtsev · Apr 27

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

    2. 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')
0 0
Rochdi Badis · Apr 27

Thanks again Vitaliy but we still going on cycles 😊 as I already you examples and I send you the a screenshot with the result and still not working, maybe I'm missing what you trying to say but I already tried option 1 with Timeformat=1 for the avg and didn't work.

your solution 1 or 2 works fine if querying the required field but not with avg, so eg. select %external(CAST(+avg(166.38) as TIME)) didn't work for me, maybe as you mentioned it's treating as double with floating precision that why it's cutting off the seconds, 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 because select %external(CAST(+avg(166.38) as TIME)) wouldn't work?

Thanks

0 0
Vitaliy Serdtsev · Apr 27

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 Conversionlink

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.

0 0
Rochdi Badis · Apr 27

Thanks you very much Vitaliy this worked:

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

Thanks

0 0
Robert Cemper · Apr 26

this worked for me

select CAST(0+AVG(PackingTimeSpent) as TIME) average ....

the 0+ forces Integer, then CAST understands you
and pls. don't ask why. I just tried 
the output from AVG() is definitely NOT Integer

0 0
Rochdi Badis · Apr 26

Sorry guys but still no luck, + or 0+ doesn't get anything as below (same in my Zen page)

FYI, here is the property definition:

Thanks

0 0