date/time conversions and select expert

[Cyked]

Long story short our DB stores data in GMT, and I need to report on PST. So I convert that with a formula:

NumberVar i;
DateTimeVar gmtDateTime;
DateVar searchDate;
DateVar beginDST;
DateVar endDST;
NumberVar timeDiff;
DateTimeVar localDateTime;

//Find the DST begin date (which is always the first Sunday in April)
//for the year of the "gmtDateTime" argument.
For i := 1 To 30 Do
(
searchDate := Date(Year({PROBSUMMARYM1.OPEN_TIME}), 04, i);
If (DayOfWeek(searchDate) = 1) then Exit For
);
beginDST := searchDate;

//Find the DST end date (which is always the last Sunday in October)
//for the year of the "gmtDateTime" argument.
For i := 30 To 1 Step -1 Do
(
searchDate := Date(Year({PROBSUMMARYM1.OPEN_TIME}), 11, i);
If (DayOfWeek (searchDate) = 1) then Exit For
);
endDST := searchDate;

//The difference between GMT and U.S. Central time zone is 7 hours during
//DST or 8 hours if not.
If gmtDateTime in beginDST to endDST then timeDiff := 7 else timeDiff := 8;

//Now convert GMT to Local
date(localDateTime := DateAdd ("h", -timeDiff, {PROBSUMMARYM1.OPEN_TIME}))

This formula field in the query expert. Because of this the reports returns about 50k rows because it has to grab everything and THEN convert to PST and show me the results from that. Is there a way around this?

If I use {PROBSUMMARYM1.OPEN_TIME} I can miss anywhere from 2 records to up to 100 based on when tickets are opened given there is a 7 or 8 hour swing between GMT and PST. This absolutely starting killing the DB a few days ago. Up until now it has been fine, other than it being a poorly written query.

Any thoughts?

Thanks!

 

[IanWaterman]

If its killing your DB server you will be better off converting date time on DB server. Create a view of the table which converts time and then use that report rather than table.

What is the DB there are fora on here which will help you with the SQL.

If you can not use a view then use a Crystal Command to write bespoke query.

Ian

 

[Madawc]

How long a time-span? It might be easier to hard-code the change-over dates for the various years. Or put them in an Excel spread-sheet that you link to your report. Or get them added to the database, if you are allowed to add to it.

You also might get better results if you could do your date-check as an SQL Expression Field. More generally, it would be better to use formula fields rather than variables.



Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

There is a shiftdatetime function that you might want to read about in the Help section.

-LB

 

[Cyked]

Doing {PROBSUMMARYM1.OPEN_TIME} - 1/3 works, for now... until its DST and i have to offset by 7 hours instead of 8.

Is this right? It works, and I get the right corrected time.... Will this also work in march when DST starts, and correct for 7 hours?
ShiftDateTime ({PROBSUMMARYM1.OPEN_TIME}, "GMT,0", "PST,480")

 

[Cyked]

ShiftDateTime ({PROBSUMMARYM1.OPEN_TIME}, "GMT,0", "PST,480")

For this, looks like during DST i need to use PST 420. Outside of DST I need to use 480.

 

[Cyked]

So, my original formula above for GMT conversion... Edited a bit after finding if the date is in DST or not, and then acts accordingly...
Thanks lbass, who help as always!

NumberVar i;
NumberVar weekCounter;
DateTimeVar gmtDateTime;
DateVar searchDate;
DateVar beginDST;
DateVar endDST;
NumberVar timeDiff;
DateTimeVar localDateTime;

//Find the DST begin date (which is always the second Sunday in March)
//for the year of the "gmtDateTime" argument.
weekCounter:=0;

for i := 1 To 30 Do
(
searchDate := Date(Year({PROBSUMMARYM1.OPEN_TIME}), 03, i);
if (DayOfWeek(searchDate) = 1) then
weekCounter:=weekCounter+1;
if weekCounter = 2 then
Exit For
);
beginDST := searchDate;

//Find the DST end date (which is always the first Sunday in November)
//for the year of the "gmtDateTime" argument.

For i := 1 to 30 Do
(
searchDate := Date(Year({PROBSUMMARYM1.OPEN_TIME}), 11, i);
If (DayOfWeek (searchDate) = 1) then Exit For
);
endDST := searchDate;

//The difference between GMT and U.S. Pasfic time zone is 7 hours during
//DST or 8 hours if not.
If {PROBSUMMARYM1.OPEN_TIME} in beginDST to endDST then ShiftDateTime ({PROBSUMMARYM1.OPEN_TIME}, "GMT,0", "PST,420")
else ShiftDateTime ({PROBSUMMARYM1.OPEN_TIME}, "GMT,0", "PST,480")

//Now convert GMT to Local
//localDateTime := DateAdd ("h", -timeDiff, {PROBSUMMARYM1.OPEN_TIME})

 

[Cyked]

Hmmm.... This doesn't help me the select statement though, since I can't use formulas in this way.

 

[Cyked]

When I do ANYTHING with writing an SQL expression I get this:

---------------------------
Crystal Reports
---------------------------
Error in compiling SQL Expression :
Database Connector Error: '42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'if'. [Database Vendor Code: 156 ]'.
---------------------------
OK
---------------------------

 

[lbass]

What does this thread have to do with a SQL expression?

-LB

 

[Cyked]

Because using formulas in the select statement pulls EVERY record from the DB and THEN crystal does the conversions, this taxes the DB. I looked at using the shiftdate time, but I was not able to figure out a way to automate it. Meaning during DST I would need to use 420 in the formula to subject 7 hours from the GMT time in the DB to convert to PST. Outside of DST I would have to change it manually to be 480 to do the conversion to subtract 8 hours.

Someone suggested using an SQL expression, which is more "automated" in that I can hard code dates for years and not have to worry about it each year we change in and out of DST.

Here is what I came up with:

(
SELECT

CASE

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '11/03/2008' and '03/07/2009'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.3333333333

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '03/08/2009' and '10/31/2009'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.29166666

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '11/01/2009' and '03/13/2010'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.3333333333

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '03/14/2010' and '11/07/2010'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.29166666

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '11/8/2010' and '03/12/2011'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.3333333333

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '03/13/2011' and '11/06/2011'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.29166666

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '11/7/2011' and '03/10/2012'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.3333333333

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '03/11/2012' and '11/04/2012'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.29166666

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '11/5/20112' and '03/09/2013'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.3333333333

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '03/10/2013' and '11/03/2013'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.29166666

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '11/04/2013' and '03/08/2014'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.33333333

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '03/09/2014' and '11/02/2014'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.29166666

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '11/03/2014' and '03/07/2015'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.33333333

WHEN "PROBSUMMARYM1"."OPEN_TIME" between '03/08/2015' and '11/01/2015'
THEN "PROBSUMMARYM1"."OPEN_TIME"-.29166666

END
)