Dealing with time format as string
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
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ž
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:
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
What locale are you using? I use "rusw".
Try to do the following in SMP:
then
What are your results?
I have the following displayed (for DISPLAY mode):
I'm using SMP and it's still not showing the seconds (see below):
and if I set my field to 166 seconds it show 00:02, so for some reason it's not showing the seconds?
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:
Thank you very much the format fixed it:
but the only problem is that the average function brings up the logical mode not display!?
Rochdi, please be attentive.
Above I already led an example with AVG (see spoiler "Simple sample").
avg trancated the second as before:
As I wrote above, there are two ways to solve:
See ##class(%SYS.NLS.Format).SetFormatItem,
Configuring National Language Support (NLS).
See Time-to-String Conversion
Which option do you choose?
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue