Why Is This Running So Slow

[goduke]

I am using the formula below to convert a unix time stamp into central standard time, and account for DST. However, when I use the formula in my select criteria, like @closed_date in lastfullmonth, it takes forever to run. Is there any way to speed this up?

@closed_date
local datetimevar rec_date := dateadd("h",-6,dateadd("s",{call_req.close_date},date(1970,01,01)));
local datetimevar begin_date := datetime(year(rec_date),1,1,2,0,0);
local datetimevar work_date := dateadd("m",3,begin_date);
local datetimevar DLSStart := work_date - dayofweek(work_date) - 13;
work_date := dateadd("m",10,begin_date);
local datetimevar DLSEnd := work_date - dayofweek(work_date) + 8;

//if in DLS Timeframe, add 1 hour
if rec_date in DLSStart to DLSEnd then
local datetimevar result_date := DateAdd("h",+1,rec_date) else
local datetimevar result_date := rec_date;

result_date;

 

[Turkbear]

Hi,
I expect that the formula, since it is local to the report, is only used after ALL records have been returned ( depending on what other selection criteria you have) and then for EACH record the Date test is made...Try creating a view in the database, if possible,( you did not specify what database is used), that converts the UNIX format into the format and setting needed.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[goduke]

I apologize for not specifying - I am using a SQL database. All my date/times are stored in Unix. I only have access to the tables directly, so if I cant get someone to create a view for me, then how would I use one of those date/times in my select criteria?

 

[Turkbear]

Hi,
You could use a Command Object instead of using the tables directly..Instead of selecting a table after setting the database connection, use the Command option and write the SQL needed to return that data - I am not familiar enough with SqlServer syntax ( I assume that is what you meant by a SQL database) to show you an example, but I am sure someone on this forum can. ( Or look at the SQL created by CR for a start)








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IanWaterman]

The code you have specified is only required if time is really important as it adjust for daylight saving time. If you are just after a date try

call_req.close_date >= datediff("s",minimum (lastfullmonth),date(1970,1,1)) and
call_req.close_date < datediff("s",maximum (lastfullmonth),date(1970,1,1))

Ian

 

[goduke]

Hi IanWaterman,

Unfortunately that did not return any records. I even took the rest of my criteria out, and just put in your date range, and still didnt get any records.

FYI - I dropped datediff("s",minimum (lastfullmonth),date(1970,1,1)) into the report and it returned
-1,267,401,600.00. Is it suppose to return a date?

 

[IanWaterman]

I was trying to return a Unix date, which is number of seconds from 01/01/1970.

Looks like I have the formula the wrong way round, try this

datediff("s",date(1970,1,1) ,minimum (lastfullmonth))

Ian

 

[goduke]

I think what you gave me is working the way you inteded it to work, however, its still not exactly what I needed. I get results, however, I am using my converted date to display the closed date in the report, and I am getting all records between 2/28 and 3/30 instead of 3/1 and 3/31. That date that you were giving me would have to account for DST, since it would have to match up with the dates in the report. I guess I am just going to have to find someone to create a view for me and do the conversion on the server. Thanks again for your help.

 

[IanWaterman]

If it is consistently one day short just change formula slightly


datediff("s",date(1970,1,1) ,minimum (lastfullmonth)+1)


Ian

 

[IanWaterman]

Unix time is based on GMT.

If you are in USA you need to adjust, still does not cater for Daylight saving though

If East Coast add 5 hours

datediff("s",datetime(1970,1,1, 5,0,0) ,minimum (lastfullmonth)+1)


Ian

 

[goduke]

Hi IanWaterman,

Your help was very useful. I think since the data in my report is converted using DST, then I wont get a match, or I will be excluding particular records. I will see if I can use a command and SQL to get what I need. Thanks again for your help.

Clint