| Author |
Message |
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
   Posted: 05 Mar 2012 at 6:16am |
|
^ fix doesnt work. the dates we're checking are march and november... the 7 is the number of days to add. Check the formula explanation on page 3.
Also having issues with PST when it comes to 11/4/2012. On EST, 8:59am -> 3:59am, and the finish time comes to 9:00am-4:00am.
Now on PST, we get 1:59:59am, and 1:00:00am.
Note this is 11/4, not 11/11.
I'm not sure at ALL why its doing this. the Dateadd returns ("d", 4.00, 3/7/2012), so we should get 3/11/2012. In fact, ALL runs of this function for 2012 should return 3/11/2012. So I'm not sure why 11/4 is doing this...
Edited by turdferguson - 05 Mar 2012 at 6:18am
|
 IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 05 Mar 2012 at 10:57am |
|
The big issue we are experiencing is that on these specific days, 11/4, 11/11, and 3/11, the DateTimeShift is mucking up hardcore on timezones outside of our own.
For example,
If I run a report with a RecId that has a date in December, lets say 12/17/2012, it will display the correct times with both equations.
If it happens to have a fringe date, such as both days that DST changes fall on, or for some reason, 11/4, then it blows up on timezones other than EST (Which is in my case, the timezone im actually in)
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 05 Mar 2012 at 10:22pm |
Ok just to investigate this, create 2 new formulas, one containing just the dststart and the other the dstend.
Drag it in your data and make sure it is returning the correct dates for all records. From their we can investigate what's happening.
Also, you most definetely have to apply the timezone adjustment before checking the dst period as DST (or British Summer Time as it's known here) in GMT starts 8 hours before it would start in PST.
Also, you may need to make an adjustment for seconds on the dststart and dstend formulas as I'm pretty sure the clocks change at 23:59:59 on the second sunday, at the minute the formulas reference 00:00:00 on that sunday (almost a full 24 hours out).
Regards,
Ryan.
Edited by rkrowland - 05 Mar 2012 at 10:23pm
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 06 Mar 2012 at 4:13am |
Originally posted by rkrowlandOk just to investigate this, create 2 new formulas, one containing just the dststart and the other the dstend.
Drag it in your data and make sure it is returning the correct dates for all records. From their we can investigate what's happening.
Also, you most definetely have to apply the timezone adjustment before checking the dst period as DST (or British Summer Time as it's known here) in GMT starts 8 hours before it would start in PST.
Also, you may need to make an adjustment for seconds on the dststart and dstend formulas as I'm pretty sure the clocks change at 23:59:59 on the second sunday, at the minute the formulas reference 00:00:00 on that sunday (almost a full 24 hours out).
Regards,
Ryan. I've done all of that. It's all the same data being returned. but the last suggestion is interesting, but it doesnt help my problem. my big current problem that i keep posting about is that the UTC to local conversion formula is giving me the wrong times on those days, with or without the DST formula.
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 06 Mar 2012 at 4:31am |
Have you tried using dateadd to add/deduct hours for the timezone conversion rather than the dateshift function?
I have no idea how that function works, I'm fairly competent with complex dateadd/datediff formulas and pretty much any time calculation can be done with them so I never bothered learning anything else.
I can't really offer support on a function I don't understand myself. ;-)
Regards,
Ryan.
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 06 Mar 2012 at 4:36am |
Originally posted by rkrowlandHave you tried using dateadd to add/deduct hours for the timezone conversion rather than the dateshift function?
I have no idea how that function works, I'm fairly competent with complex dateadd/datediff formulas and pretty much any time calculation can be done with them so I never bothered learning anything else.
I can't really offer support on a function I don't understand myself. ;-)
Regards,
Ryan. by function you mean ShiftDateTime (fieldname, ",0,UTC", "") ? I cant use dateadd because we have many different customers in different timezones and they could also be running the report from a different timezone depending on each location. Coding each of our 25+ reports for each location anyone in the company might be is just ridiculous and out of the question, so we need this function. It works for the most part and is all over google. http://community.landesk.com/support/docs/DOC-11762 main topic. Sorry Ryan :/
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 06 Mar 2012 at 5:24am |
I've just read the function description, although PST isn't working for you (I'm assuming you enter the PST settings manually as it isn't your local timezone) I'm guessing it would for someone who's timezone is currently set to PST.
As you say it's working correctly for EST which is your local timezone?
Rather than testing in Crystal - perhaps try changing your Windows OS Timezone to PST?
Sorry I can't offer a more definitive solution but it's sort of the blind leading the blind at the minute haha!
Regards,
Ryan.
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 06 Mar 2012 at 7:16am |
|
That is how I've been testing, by setting my timezone on the windows OS to different zones. Each one except EST is incorrect.
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 07 Mar 2012 at 2:39am |
Well I think maybe to fix the 11-Mar-12 and the 11-Nov-12 issue we need to work from the second Monday, rather than the second Sunday.
It's has to be something with our calculation for it to be wrong on those specific dates. To change our formula to work for Monday instead of Sunday replace all of the following;
dayofweek(date)
with this;
dayofweek(date,crMonday)
As for the 11-Apr-12 I have no idea what could be causing that, try the above and let me know if it fixes the other 2 dates and we'll get to April after.
Regards,
Ryan.
Edited by rkrowland - 07 Mar 2012 at 2:39am
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 08 Mar 2012 at 3:40am |
Originally posted by rkrowlandWell I think maybe to fix the 11-Mar-12 and the 11-Nov-12 issue we need to work from the second Monday, rather than the second Sunday.
It's has to be something with our calculation for it to be wrong on those specific dates. To change our formula to work for Monday instead of Sunday replace all of the following;
dayofweek(date)
with this;
dayofweek(date,crMonday)
As for the 11-Apr-12 I have no idea what could be causing that, try the above and let me know if it fixes the other 2 dates and we'll get to April after.
Regards,
Ryan. Again, its helpful, but the issue is I'm NOT using that formula and I'm getting bogus times. I'm just using the DateTimeShift on our UTC database value.
|
|
IP Logged |
|
|
|
