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!
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!