Our company has been having this issue for a couple months now. Mostly because I dont know how to approach this issue...
ShiftDateTime ({Table.Column}, ",0,GMT","")
The following function, found all over the Internet, is supposed to convert GMT times from a DB into the user's local time. Which is all fine and dandy when I'm set in EST (Which I'm actually in)
If I set my machine to Pacific time, certain database times get skewed by up to two hours, but only in hour intervals, showing me this is having trouble converting timezones. Usually the problem times/dates are close to the dates we move the clocks forward and back. (11/4 & 3/4)
Can anyone assist? Or offer insight? I have no clue why it's doing this, and specifically on/near those dates.
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Posted: 17 Apr 2012 at 6:02am
since you mention that the error occurs around changes to/from daylight savings...well, not all countries use daylight savings, and those that do, do not always start and stop on the same dates. In addition, not all states implement it...and when you go south of the equator, the time shifts reverse.
I haven't needed to use the function, but those are some of the issues that occur with time shifts that I have looked into...making time shifts (as a generalize function) really hard. I would assume that the function is using the settings on the computer, so it would know if it is in daylight savings or not, but not when the shift occurred, which might be some of the issue. If it is all in or out of the 'shifting period' it is correct, but the boundary is unknown and subject to errors.
I tried these out and they all worked correctly...
I do not because we need them to convert to any user's timezone, as the company we're selling to could have users need the reports to be run in any timezone they might be in at any given time. (Traveling and the like)
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Posted: 18 Apr 2012 at 3:17am
it would seem that you would want to have a parameter that is the offset...probably in hours, then use the parameter converted to minutes to create your other timezone string...I guess the timezone could be a parameter, but I guess that depends on whether or not you display the timezone when displaying the time.
If the user doesn't know which timezone their in, how would the report?
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Posted: 18 Apr 2012 at 3:26am
Are you certain all of your start times are GMT,0?
The only thing I can assume after all the posts regarding this is that your date fields include DST adjustments in them - to fix this all you should need to do is remove the DST adjustments from those fields and then convert to local timezone - after which I don't think you'd need to make any manual adjustments for DST (I remember this being one of the problems in your original post).
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
Posted: 18 Apr 2012 at 3:26am
Originally posted by lockwelle
it would seem that you would want to have a parameter that is the offset...probably in hours, then use the parameter converted to minutes to create your other timezone string...I guess the timezone could be a parameter, but I guess that depends on whether or not you display the timezone when displaying the time.
If the user doesn't know which timezone their in, how would the report?
Crystal does know the timezone of the report, as it's correctly converting GMT to each time zone until we get to a fringe date like this.
Originally posted by rkrowland
Are you certain all of your start times are GMT,0?
The only thing I can assume after all the posts regarding this is
that your date fields include DST adjustments in them - to fix this all
you should need to do is remove the DST adjustments from those fields
and then convert to local timezone - after which I don't think you'd
need to make any manual adjustments for DST (I remember this being one
of the problems in your original post).
Or whatever you may think works to rid the original datefield of it's DST adjustments.
Regards,
Ryan.
Where do you get the idea my date fields have DST in them? They're stored as GMT/UTC in our database... and im pulling them out to do a timezone conversion. There is no DST adjustments here unless Crystal itself is doing it, and if so, it has nothing to do it with, as you see above in my data, the problem times are coming from before the conversion to DST at 2 am, or are unaffected by a change the way it should be.
Sorry if I came off rash there. Just, there's a set way we need to do this, which is converting those DB values from UTC/GMT to the current users timezone, then i can think about doing DST conversions.
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Posted: 18 Apr 2012 at 4:42am
After a closer look at this, your shiftdatetime calculations appear to be working correctly (in some snese of the word atleast).
I'm guessing this only happens on the days DST changes? Clocks go back/forward at 2am.
Let's say we've got a GMT time of 9am the day the clocks went forward an hour IE PDT just became PST.
We're converting to PST - 8 hours right? So that gives us a PST time of 1am, but wait.... if it's 1am PST that means the clocks haven't gone forward yet, which means it's still PDT in that timezone converting that time to PDT rather than PST (which would be correct) would give a return of 12am.
All very complex but it appears as though Crystal is trying to do DST adjustments for you based on the user's local timezone.
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
Posted: 18 Apr 2012 at 5:43am
Originally posted by rkrowland
After a closer look at this, your shiftdatetime calculations appear to be working correctly (in some snese of the word atleast).
I'm guessing this only happens on the days DST changes? Clocks go back/forward at 2am.
Let's say we've got a GMT time of 9am the day the clocks went forward an hour IE PDT just became PST.
We're converting to PST - 8 hours right? So that gives us a PST time of 1am, but wait.... if it's 1am PST that means the clocks haven't gone forward yet, which means it's still PDT in that timezone converting that time to PDT rather than PST (which would be correct) would give a return of 12am.
All very complex but it appears as though Crystal is trying to do DST adjustments for you based on the user's local timezone.
Regards,
Ryan.
My GMT times are 9:59:59am and 10:00:00am.
CST (-6:00GMT) post function 3:59:59am 4:00:00am GOOD
ARIZONA (-7:00GMT) post function
1:59:59am
2:00:00am Not good... we lost an hour somewhere.
This. If it was correct, Arizona should read
ARIZONA (-7:00GMT) post function
2:59:59am
3:00:00am
and if it was correct, and doing DST, PST should look like this, jumping forward! (feel free to correct me on these)
PST (-8:00GMT) post function
1:59:59am
3:00:00am
This whole issue is why it is subtracting an hour when it shouldnt... is it trying to do DST from a certain timezone that doesnt depend on the user? Or why is it messing up? Is this a bug?
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
