DQl queries through DA or webtop giving contentserver local time instead of giving client local mach

http://msroth.wordpress.com/2012/11/12/time-zone-technical-notes/

Look at Scott Roth's whitepapers on timezone offset feature:

HI Johnny,

Thanks for your reply.

I went through the link,this is what i understood.

QUERY :- select r_tz_aware,r_normal_tz from dm_docbase_config

RESULT :- r_tz_aware = 1, r_normal_tz = 0

This means that all times will be stored as UTC times and converted to client local time when they are displayed.

Actually we can change these attributes to get the 5.3 behavior r_tz_ware to F and r_normal_tz to -18000,but my application will effect globally.

the below are the brief explanation of my requirement.

For example:

reportname:The user want to see the all documents inside the folder he is belongs to.

Query used for the report:

select r_object_id,object_name,owner_name,r_creation_date from dm_document where folder('/cabinet/foldera',descend) where user_name ='sessionuser'

My requirement is generating reports,for reports i am passing Dql queries like above in my behaviour class.

once i have click execute button ,query result are displaying in webtop UI.

present o/p: In report results creation date (example:7.30pm IST) and At webtop UI level creation date is different(contentserver timezone is getting example;3pm CET) different.

Exepected output:

suppose if i run the above report in india,that document date should be display in india time and if run the same report in US ,it should display the US timezone and if run the same report in Europe timezone users,it should display that timezone in report results.

but its giving contentserver timezone in report results(query results),but if we go for webtop mentioned folder location and if we verify the timezone,then both are different.

Note:
As per known behavior of documentum,dql queries will fetch the contentserver timezone instead of giving client machine timezone.

My envronment:6.5 sp2.

Please advise me what is way i can approach to achieve my requirement.

Thanks&Regards

Hari

Why dont you ask Scott? Since he wrote the whitepapers, he knows more about this.

HI Johnny,

Thanks for your reply.

I went to his blog(

http://msroth.wordpress.com/2012/11/12/time-zone-technical-notes/) and try to post a comment.

Leave a Reply text box is not enabled for me ,to write my problem about timezone.

but i have seen his other posts like

Responses to xCP2.0 Tidbits from Momentum DevCon 2012. for these posts Leave a Reply text box is enabled. to post a comment if we need.

Please suggest me How can i ask Scott.

Thanks&Regards

Hari.

Wiesnoepersad

Can verify the next:

1) dm_docbase_config.r_tz_aware = True

2) <app_root>/wdk/app.xml

<client_timezone_awareness>

</client_timezone_awareness>

After these changes, import/check-out ant file from WDK-based application. The imported/checked-out files must have the client time zone.

I think the issue is that webtop datagrid my not be Timezone aware like attribute list widget.

Sorry to weigh into this discussion late. Did you resolve your TZ issue? The EMC paper listed here (http://msroth.wordpress.com/2012/11/12/time-zone-technical-notes/) does a good job of addressing this. (BTW, the comment link is located at the top of the post in the slug line. It works fine for me. Sorry you had trouble with it.)

Hi Scott,

Thanks for your participation.

Still i am having Timezone issue.

the bellow are the my configuration result.

r_tz_aware = 1, r_normal_tz = 0

If i have created any folder/document in my webtop UI ,then all times will be stored as UTC times and converted to client local time when they are displayed.

This is working fine for me.

The problem Here is :

suppose for example if one folder is created in webtop UI in Secacus at 11:00Am.created by some one else.

if i have seen same folder in india on Webtop UI,it Is displaying in my localtime 9:30PM IST.

upto this is fine(this same folder will display in my server

5PM CET.)

Note: MY servers(contentservers,appservers) are in CET timezone.

If i run the DQL query Now To get the folder creation time.( : example:select r_creation_date from dm_folder where object_name ='foldername')

dQl query is giving the servertimezone(5PM CET).but now in my webtop UI ,the folder creation time is 9:30PM IST.

my requirement is:

once if i run the DQl query ,Dql query results given time and webtop level UI folder creation time should be same(.

same behavior should happen in all timezones.

Actully Dql queries are giving servertimezones alway whenever run a query.according to my understanding this is documentum behavior currently.

Please suggest me ,how can i approach.

Thanks&Regards

Hari

HI Wesnoepersad,

Thanks for your comment.

I have verifed your posted coments.r_tz_aware =T and

<client_timezone_awareness> is also true.in my app.xml.

but still no luck.

Thanks&Regards

Hari.

DocBuilder

Hi NewWindow_Hexaware,

Did you get a solution to your problem.Even I am stuck in the same situation.Kindly help me if u have the solution with you

HI DocBuilder,

I didn't get solution yet.Still i am facing the issue.if the issue is resolved ,i will update the solution in this thread.

If you have the solution with you,please update in this same thread

Regards

Hari

I don't have access to DCTM right now so I can't give you an exact answer; however, I can offer this:

- DQL will return the date as it is stored in the database without correction for the local time zone. You can, of course, change the display format.

- It seems logical that you could get the GMT offset of your local client (Java properties?), and add/subtract that to the values used in your query.

HI Scott,

Thank you very much for your support.

Could you Please Provide the sample code snippet,if you have.

Thanks&Regards

Hari..

You can you dateadd() in your DQL to adjust the values to match the timezone offset or if you dont want to do that, you can custom your jsp page and use one of formatter classes in DFC (there might be WDK control that does this as well - look in the WDK reference manual).

I don't think dateadd() lets you add hours, only month, day , year. A formatter would be a clever way to handle the problem, but I think you would have to write one yourself to do it.

You are correct about dateadd, but I just discovered a neat trick! You can perform arithmetic functions on date value as part of the DQL select:

This adds hr to creation date:

select r_creation_date + 0.04167 from dm_document where r_object_id='...'

That's cool! That makes this solution really easy.

What does the 0.04167 represent? One hour out of the year?

.04167 is 1/24 of day = 1 hr

HI Johnny Gee/Scott,

Thank you so much for your support.

This adds hr to creation date:

select r_creation_date + 0.04167 from dm_document where r_object_id='...'

In my Requirement:

same query should dispaly the results at different timezones,if it's a single timezone i can use like above you have mentioned.

For example: if i have created document in india,12:AM, if i have run the query in Secacus ,it should display 1:30 PM,and if i run in india it should display.12AM.

How can i use different timezone in this query.?

is it possible through query level.to directly solve this prob?

Please give me your suggestions.

Thanks&Regards

Hari

If you using timezone aware function, that means you have an app server in each timezone. Therefore, you could write a properties file that has different tz offset that you can query against as part of your DQL generation statement.

For example, in your properites file, you would store .04167 or some multiple of this. Then you would read the properties file and insert the appropriate value into as part of DQL statement.

Hi Johnny Gee,

Thanks once again for your quick responce.

I am not using any timezone aware function.My all appservers and content server are in CET Timezone only.But My webtop application is used by lot of users around the Globe(All different timezones like IST,EST,CET..etc.).

Suppose if i run any query:(select r_creation_date from dm_document where r_object_id='...') like this,that should convert the utc time into their localtimezones while displaying results.that means it should display the creation date in different timezones,to which timezone the login user belongs to .

is it possible to do in query level ?

Please suggest me how to handle this Problem.

Thanks&Regards
hari

Scott already said that DQL results do NOT get converted to localtimezone; all results are in UTC (server time).

You will need to see if there is some javascript function that you can use to determine client side timezone and the do the math from there.

HI Scott,

You have mentioned that:

- DQL will return the date as it is stored in the database without correction for the local time zone. You can, of course, change the display format.

But i have found late

DQl is not returing date as it is stored in the database.it's converting UTC time,to the client timezone while dispalying results, we can observe this behaviour if we run the dql query through OOTB Webtop/DA Dql editor

I need same behavior in my custom component also.

for this i have modified dqleditor.jsp according to my requirement.

I am thinking that this timestamp conversion is happening in dqleditor.jsp from below peace of code block.

<dmf:datagridRow>

<%for(int index =0; index < dqlEditor.getAttributeCount(); index++)

{%>

<%if (dqlEditor.getColumnType(index) == IDfAttr.DM_TIME)

{

<dmf:datevalueformatter>

<dmf:label datafield='<%=dqlEditor.getColumnLabel(index)%>'/>

</dmf:datevalueformatter>

}

</dmf:datagridRow>

from Where this timestamp conversions is happening.apart from this.

Please suggest me if i went wrong.

Regards

Hari

Hi Johnny,

select r_creation_date + 0.04167 from dm_document where r_object_id='...'

If i run the below query i am getting error.like below:

Error occured during query execution :[DM_QUERY_E_CURSOR_ERROR]error: "A database error has occurred during the creation of a cursor (' STATE=22005, CODE=206, MSG=[Microsoft][SQL Server Native Client 10.0][SQL Server]Operand type clash: datetime2 is incompatible with numeric

')."

I am using sql server database in my backend.

Please give me your hands,to acheive my problem,

thanks&Regards

Hari

This must be a Oracle "feature" then.

Thanks a lot for your responce.

DateAdd(HOUR,2,r_creation_date) this function is working in sql server ,if run the query like this:

select r_creation_date,DateAdd(HOUR,2,r_creation_date) As NewDate from dm_documentu where r_object_id =''

if i want to use DateAdd () to add the hour Dql is not supporting Hours.So how can i run my above query ,in Dql editor.

Is there any way to execute that query.

Please give me your suggestions.

Regards

hari

pettitk

Do you have clients in places like Arizona, or other timezones that don’t honor daylight savings? Arizona, they are always MST. So, if your server is in New York, part of the year, your query would be DateAdd(HOUR,2,r_creation_date), and the other part of the year DateAdd(HOUR,3,r_creation_date). Doesn’t seem like the right design.

Hi Kyle Pettit,

I have the clients in different timezones like IST,CET,EST.etc(My All servers are in CET timezone). I am trying the following ways to acheive my timezone issue.

Query level:

Is it possible to add the Hours or minutes to the DateAdd () funtion in Dql query level.I have tried ,but i couldn't execute the query.?

I can add minutes to DateAdd() function.DateAdd(Minute,330,r_creation_date) in sql server.In my backend(sql server) i can execute the query and able to see the results.

once if i can execute the DateAdd() with Hours or Minutes in Dql query level,then i want to implement the code, based on the different timezone session user.if the Sessionuser is belongs to

CET timezone then DateAdd(Minute,60,r_creation_date)

IST timezone then DateAdd(Minute,330,r_creation_date) like this it should execute,

2.Where timezone convertion is happening in OOTB webtop/DA dql editor component.

if i run the Dql query in OOTB webtop/DA dql editor then the query results is displaying local client timezone.it means while displaying results,converting servertimezone into corresponding client local timezone.

but dql query executed through my custom component is getting directly servertimezone but i need client local timezone..here timezone convertion is not happening in my custom component.

where this timezone converstions are happening.

Please suggest me if any one have any ideas and thoughts

Your help is highly apprecitated..

Regards

Hari