| Author |
Message |
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
   Topic: ShiftDateTime DST issues
Posted: 16 Feb 2012 at 7:51am |
|
So we are having an issue we cannot resolve here.
I'm running Crystal XI R2 SP6. Updating and rebooting did not solve this issue.
So basically, we are converting from UTC to the customer's time zone. So we do this
ShiftDateTime ({DateTimeField}, ",0,UTC","")
To adjust it to the viewer's time zone. The problem is, this doesn't account for DST at all.
Is there any function/method/update that fixes this? I've been going about doing a giant function checking against hardcoded values if its DST. Before I get too involved in that, does anyone know what I'm talking about or can help?
Thanks! :)
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Feb 2012 at 8:12am |
not sure but maybe htis will help from Crystal Help file ShiftDateTime (v XI)
Example
The following example is applicable to both Basic and Crystal syntax:
ShiftDateTime (DateTime (2000, 1, 1, 12, 0, 0), "EST,300,EDT", "PST,480,PDT")
Returns 1/1/2000 9:00:00AM
In this example, noon Eastern Daylight Time is shifted to 9 am Pacific Daylight Time.
Comments
Time zones are represented by TimeZoneString, which is a string that follows these formats:
"std,offset,dst,[offset];start[/time],end[/time]"
Example: "PST,480,PDT,-60;4.1.0/02:00,10.1.0/02:00"
"std,offset,dst,[offset]" (if the time zone follows the US Daylight Saving Time (DST) rules)
Example "PST,480,PDT,-60"
"std,offset" (if there is no DST)
Example "South Africa,-120"
"" (an empty string indicates the local time zone from the OS settings)
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 16 Feb 2012 at 9:21am |
|
That's the idea. We're using "" to represent the user's local time zone.
Unless something like
ShiftDateTime ({DateTimeField}, ",0,UTC","UTC, 0, DST, 60")
would work, and it doesnt. "time zone field not valid", pointing to the last argument.
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 21 Feb 2012 at 4:43am |
|
bump. anyone?
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 21 Feb 2012 at 4:56am |
not all states use DST, and not all nations either, so I am not sure if there is simple generic solution. a look up table seems the best solution. If every one followed DST, I would have thought that looking up the longitude(not that that is simple) would be a consistent method
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 21 Feb 2012 at 5:02am |
|
My question is why doesnt Crystal do this conversion built into it? Especially six service packs in? Or is mine just bugged?
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 21 Feb 2012 at 5:22am |
If I was you, I'd use the dateadd (or the shiftdatetime - I'm not familiar with this function myself so I'd stick to dateadd) function to convert all your times to GMT (as everyone knows how many hours their timezone differs from GMT).
Then just place a parameter on the report asking the user how their timezone differs from GMT - GMT-12, GMT-11..... GMT+12.. etc - then adjust your GMT times based on this.
Regards,
Ryan.
Edited by rkrowland - 21 Feb 2012 at 5:23am
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 21 Feb 2012 at 6:14am |
|
We shouldnt have to do that though, and due to the requirements, we cannot.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 21 Feb 2012 at 7:09am |
maybe because DST is not standard for any given location, there is no real way to implement it in a simple way. And I know your can't, but my idea and rkrowland are basically the same. The closest you can get is how far you are from an arbitrary point. Case in point, Arizona does use DST, but states above and below do, nor does Indiana, so how would you apply this. Do timezones cut through the middle of states? (they should but politics mess with this). While I understand that you need this to work, I don't think that there is a simple implementation, and for a application that is primarily concerned with reporting data, I could make a case that 99.9% of all reports don't need this functionality and so expending the effort for a non-trivial solution is a poor use of resources. Again, I understand that it is in your specs, but what you would need such a function for? I would think that if you denoted the value as EST or EDT that a locality that wasn't using it would be able to adjust... So adjusting the time and denoting what it is referencing, is about as close as you can come (I think) without massive coding. I am sure that you can find the rules as to when DST starts and stops and use that to update the time stamp...at least that is what I would advocate for with my superiors along with the number of hours to find a solution that won't make the report run like a dog...maybe a table of every zip code in the US and what time zone they are in and if they follow DST or not... Don't know about Canada at all
|
|
IP Logged |
|
turdferguson
Newbie
Joined: 16 Feb 2012
Online Status: Offline
Posts: 30
|
Posted: 21 Feb 2012 at 7:36am |
|
Understandable. But really, why doesn't Crystal have this functionality to adjust for DST in the first place?
And all of our reports for one product need this implemented (around 25), so its not a 99.9%...
I'll forward your info along to the powers that be.
|
|
IP Logged |
|
|
|
