Inconsistency assigning datetime-tz to datetime

to what is your session:display-timezone set?

> On Dec 3, 2015, at 4:10 PM, dbeavon wrote:

>

> Update from Progress Community [https://community.progress.com/]

>

> dbeavon [https://community.progress.com/members/dbeavon]

>

> I did a bit of homework looking for an explanation of the behavior I get when assigning a datetime-tz to a datetime.

>

> Here is a link of more confusion in a similar vein.

>

> community.progress.com/.../9423

>

> Can someone please run this and tell me if the output line marked with "1" is different from the others for you? For me it is different (OE 11.3). Based on the ABL reference, I think all output lines should show the current time in UTC. (session timezone).

>

> /* HERE GOES NOTHING */

>

> SESSION:TIMEZONE = 0.

>

> DEFINE VARIABLE vDate1 AS DATETIME.

> DEFINE VARIABLE vDate2 AS DATETIME.

> DEFINE VARIABLE vDate3 AS DATETIME.

> DEFINE VARIABLE vdateTZ AS DATETIME-TZ.

> vDateTZ = datetime-tz(NOW,0).

> vDate1 = vDateTZ.

> vDate2 = NOW.

> vDate3 = DATETIME(DATE(vDateTZ), MTIME(vDateTZ)).

>

> MESSAGE "SESSION NOW" NOW SKIP "SPECIFIED TZ" vDateTZ

> SKIP "1" vDate1 SKIP "2" vDate2 SKIP "3" vDate3

>

> VIEW-AS ALERT-BOX.

>

> ┌────────────────── Message ───────────────────┐

>

> │ SESSION NOW 12/03/2015 20:40:00.715+00:00 │

>

> │ SPECIFIED TZ 12/03/2015 20:40:00.715+00:00 │

>

> │ 1 12/03/2015 15:40:00.715 │ <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

>

> │ 2 12/03/2015 20:40:00.715 │

>

> │ 3 12/03/2015 20:40:00.715 │

>

> │ ──────────────────────────────────────────── │

>

> │ │

>

> └──────────────────────────────────────────────┘

>

> I am probably going to work on this with Progress tech support for a while but I wondered why I'm the only one having trouble (can't turn anything up from googl'ing).

>

> Thanks, David

>

> View online [https://community.progress.com/community_groups/openedge_development/f/19/t/21789]

>

> You received this notification because you subscribed to the forum. To stop receiving updates from only this thread, go here [https://community.progress.com/community_groups/openedge_development/f/19/t/21789/mute].

>

> Flag [https://community.progress.com/community_groups/openedge_development/f/19/t/21789?AbuseContentId=15fe83ea-702e-46c5-9ce7-643e4f5febd3&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true] this post as spam/abuse.

My session:display-timezone is unspecified.  If I show it (message SESSION:DISPLAY-TIMEZONE. ) it comes back as null (?).

I tried assigning that to zero and it doesn't have any effect.

Well, I put everything in UTC time to make the problem as obvious as possible.  

It is clear that the assignment in question (line "1") is either ignoring my SESSION:TIMEZONE, or is overly sensitive to whatever timezone data is gathered from the host .  My assumption is that it is a bug.  It is *supposed* to use the user's *session* information (the session's specified local timezone) to convert the DATETIME-TZ to a DATETIME.

The additional vDate2 and vDate3 are not totally related to vDate1, but were interesting to look at.

vDate2 is most similar.  It also uses "NOW" (documented to return a DATETIME-TZ), but it immediately assigns to a regular DATETIME instead of using an intermediate DATETIME-TZ variable.  The intermediate DATETIME-TZ is the only difference between the two scenarios ("1" and "2").   That intermediate variable should not have any impact on the final results, IMO.

Finally I should note that vDate3 is actually intended/documented to work differently than the other examples.  (Although it is a source of confusion from time to time).  In this scenario the DATE() and MTIME() functions intentionally ignore the SESSION timezone if argument is a DATETIME-TZ.  This is the only one of the three that is supposed to ignore the ABL session's local timezone.

I believe that this has a huge change of being a variation of Defect PSC00336136 which I logged on 11.5.1.

It is described in KB000061871.

Thanks for the pointer to that article.  I had not seen it.   I assume it was recent since you said it came up for you in 11.5.1.  What is confusing to me is that these issues are coming in an API that has been around since OE 10.  Have there been regressions?  Or is it just not stuff that is used often?

The KB points to a bug in the DATETIME-TZ() function.  But in my case I have a perfectly valid DATETIME-TZ data type (vDateTZ) already.  IE. I'm already beyond the call to the DATETIME-TZ() function.  

In my case it is the assignment operator itself that seems to be buggy (ie. the assignment appears to be using something other than the session's specified timezone to convert to some random "local" time).

Or maybe you are implying that the DATETIME-TZ() function is used internally for the assignment operation.  

In that case the KB should probably be updated to say there are flaws in assignment operations as well.  (As it is, the KB sounds like it applies specificly to the DATETIME-TZ() function - and is related to the "second input parameter" not being specified).  

In other words the KB makes the problem sound like an obscure, corner case.  Whereas it could be written to say more broadly that a client's SESSION:TIMEZONE value is sometimes ignored, even in cases when a "local timezone" is critical for conversion.

It does look like there could be an issue when assigning the datetime to a datetime-tz when using session:timezone other than the system's time zone .  An RFA was logged (PSC00343489) and I will look into it.

Regards,

Robin

Thanks for the update.  Here is another reference number related to datetime assignment problems: PSC00343752

Any updates on this Robin?  The confluence of issues surrounding datetimes and timezones leads me to think it is very unusual for OE/ABL applications to service users in multiple timezones.  This is one of two issues that has bitten us recently.

This one is a very serious issue, in my opinion, since the ABL's API seems to arbitrarily oscillate back and forth, generating unpredictable datetime results.  In one context the API might use the host system's timezone (the equivalent of SESSION:TIMEZONE = ?) and in another context might actually respect the timezone specified in the code (SESSION:TIMEZONE = XYZ).

It has been two months and we are still doing quite a bit of head-scratching and arm-flailing over here...

Tech Support logged PSC00343489 for this issue.  It is currently fixed in 11.3.3.030 and 11.6.1.  According to the call log, Alberto is working on getting the 11.3.3.030  hot fix ready for you.

webappinternal.progress.com/.../caseView.action

Looks like I'm good to go.

Here are the relevant KB's:

knowledgebase.progress.com/.../Inconsistency-assigning-datetime-tz-to-datetime

knowledgebase.progress.com/.../000061871

Thanks for the help.  I'll be closing my support case now.  For anyone else running into issues with SESSION:TIMEZONE (to support multiple client timezones), you may be interested in considering v.11.6.1