Time Analysis

[kieso]

I have two questions

I have a date time field set to Prime Meridian time, that I have adjusted to New England’s time zone via the following formula;

{START_DATE/TIME} – 300

When I looked up old archive threads to get the answer to (what is now) my second question, I found a reference to “datedif” as a superior method to make these adjustments. Have I correctly interpreted these old posts, and why is the datedif command different that a simple subtraction?

Checking old archives is supposed to make me feel a little smarter, not raise even more questions!!!!! )



All right, now for my original query.


I’ve calculated the length of a trip, and I want the result to show as HH:MM

I’ve taken the original date time fields and subtracted them
{TRIP TIME}=({END_DATE/TIME} – {START_DATE/TIME}) * 60

and then

{TRIP HOURS} = TRUNCATE ({TRIP TIME},0)
and
{TRIP MINUTES} = ({TRIP TIME} – {TRIP HOURS}) * 60

Then I build my report by putting up the two fields and putting a text field in with the semicolon to make it look like a time

[{TRIP HOURS}] [ : ] [{TRIP MINUTES}]

I have a sneaking suspicion that I’m going to get an “A” for creativity {he says while looking for some small validation from Yoda: } and a “D” for elegance. Surely one of you “Crystal Guru” types has a simpler solution for a Crazy Cunuck like me.

Take Care, all

Rodger

 

[Reebo99]

Ok, I used the following as an answer to a thread a while ago and got slated because you can't use it in a crosstab. Hopefully it should help in this instance :

dateadd("s",{secondstotal},Datetimevalue(0000,00,00,00,00,00))

This only works if your result is guaranteed not to exceed 24 hours. If you cannot guarantee this for the future then (sorry all you crystal syntax people)in Basic Syntax:

Dim Hours as number
Dim Minutes as number
Dim Seconds as number

Hours = truncate({@SecondTotal}/3600)
Minutes = truncate({@SecondTotal}/60) - (hours*60)
Seconds = {@SecondTotal} - (hours*3600) - (minutes*60)

formula = cstr(hours,0,"&quot&":"&cstr(minutes,0,"&quot&":"&cstr(seconds,0,"&quot

If anyone out there knows a better way, then please let me know, because I use this calc alot. Also, if someone could tell me in Crystal Syntax, then this would be appreciated also. Hope this helps.


Reebo
Scotland (snowed in)


The idea is basically the same, however I would create (@secondtotal) formula with :
datediff("s",{START_DATE/TIME},{END_DATE/TIME})
Reebo
Scotland (sunny? honest, it really is!)

 

[kieso]

Thanks Reboo

My particular database date time fields calculate to the minute, not the second, but I see what you mean. It looks like you're using the same approach I've used, except you're using another formula to glue the Hours calc and the Minutes calc together into one formula result

In excel the feature that does this in Concatenate. I'm working at home today without Crystal, but I thought I saw this command in the menu of Crystal last time I was using it.

I guess I get a C for ellegance, instead of a D after all.

)

Thanks Reboo


Rodger

 

[Naith]

Rodger,

I tend not to hardcode values to derive another timezone, because when daylight saving changes affect one of the zones, it means your formula is suddenly spurious. If you wanted to work out time differences between datetime fields, you could download the DateTimeDiff from Crystal.

As it stands, the way you're deriving the time difference now; is it accurate? It seems like it would give odd answers.

e.g.
{TRIP TIME}=({END_DATE/TIME} – {START_DATE/TIME}) * 60

translated into

(DateTime(2003,01,05,10,05,00) - DateTime(2003,01,05,08,55,00))*60

eventually turns out an answer of 2:55, using your approach, which doesn't seem to be correct.

Perhaps I've misunderstood what you're doing, but if I haven't, I can show you an alternative.

Naith

 

[kieso]

Hmmmm...... well I don't know, Naith.

To be honest with you I'm still at the stage of endless trial and error, so I'm not sure why it works, but the date time fields in my tables seem to facilitate my approach.

I'm looking at one record showing a driver arriving at a store at 07:50, and leaving at 08:12. My report calculates this as 22 minutes (thank goodness too, because if it ever shows this as 0.62 of an hour, the poor bloke will get fired).

Good to know that my database is smarter than I am, eh??



Take care

Rodger

 

[Reebo99]

datediff("n",DateTime (2003,01,05,07,50,00),DateTime (2003,01,05,08,12,00))

gives a value of 22, i.e. the total difference in minutes from the first time to the second time. Reebo
Scotland (sunny? honest, it really is!)

 

[kieso]

Is Reebo's datedif function the "alternative" to which you refer, Naith?

I can see the benifit of it now. you don't need to worry about how the database writes a date time field. It will always give the appropriate time.

I like it. Now all I need to do is get with UPS and find out what their little report viewer supports

Thanks to you both for your insights, and Juan.... enjoy your small sliver of Scotish sun.
)

Rodger

 

[Naith]

The DateDiff and DateTimeDiff are a bit different.

If the driver was at a joint longer than an hour, the DateDiff in the earlier post will come up 81 mins rather than 1:21. But you can manipulate the output, and you probably wouldn't have that long a time frame to consider. (?)

But you're right, the DateDiff/DateTimeDiff functions are a much cleaner way of deducing this kind of thing.

Naith

PS: Reebo is lying about the sun. It's grim in Scotland. I just turned a contract down over there for this very reason. We're on to you, Reebo.

 

[kieso]

DateTimeDif sounds like exactly the elegant solution I was looking for. Thanks Naith. Am I to understand that in order to use these functions I need to download a patch? Where do I get it?

Rodger

P.S. I just checked out

http://www.onlineweather.com/v4/uk/overviews/Scotland5.html

and I gotta say, Reebo, that Naith has a point. They have a little picture of Scotland and there really is an awful lot of little clouds covering the map. Turn off your sunlamps look out the window, and THEN tell us if you see any sunlight! Okay?

 

[Reebo99]

OK. Naith, what the hell is datetimediff, I don't have this function as an option?

For the record, I'm in Stirling which has been sunny for the last 2 days, phone the met office if you need confirmation. Reebo
Scotland (Mist! who said that?)

 

[Naith]

Go to

http://support.crystaldecisions.com/downloads

and look for Ufldtdif.exe.

Naith

Stir·ling(stûrling)
A borough of central Scotland on the Forth River west-northwest of Edinburgh. Its medieval castle was the birthplace of James II of Scotland. It is always cold and perpetually rains. (See also 'bleak'). Population: 38,400.

Revised Unabridged Dictionary, C 1996, 1998 MICRA, Inc

 

[kieso]

I wonder if that will work if I'm using a proprietary program to view the reports? I'm going to check with UPS logistics to find out what they support

P.S.

http://www.onlineweather.com/v4/uk/city/Edinburgh.html

says bright or sunny "spells" in Edinburgh. In New England we have another name for bright or sunny "spells" we call that "cloudy"

Probably some crazy Yank thing, eh?

 

[Reebo99]

OK, let's get his sorted out :

Monday :Overcast/cold
Tuesday :Sunny/cold
Wednesday :Sunny/warm
Thursday :Overcast/grey and miserable
Friday on't care, I go back home, as long as the plane takes off

Apart from the comments regarding the weather, Thank you everyone for your invaluable input. Reebo
Scotland (Raining)

 

[Crystalguru]

I was 'purusing' the tek-tips sites for a date/time function and came across Kieso's thread. At first I was not going to read the thread since it didn't pertain to my issue at the moment. But after scrolling down, the weather banter "sucked me in!"... I will have to say, this thread was entertaining as well as educational. I will hang on to it for future reports.

I do have one thing to add, poor Kieso... I was at UPS Logistics for a bit and it was always "cloudy". Good luck!

 

[Reebo99]

Anyone who is really interested in the weather in Stirling should check out this site:

http://www.onlineweather.com/v4/uk/city/Stirling.html

Who's smiling now? Reebo
Scotland (Raining)

 

[kieso]

Woah, CG!

Just wait one second!!!!

Are you saying you worked on the Crystal applications at UPS logistics????

We need to talk!

All of the "canned" reports in Mobilecast only work in Mobilecast. I can't get them working properly in Crystal. Moreover, they are filled with code that looks like greek. Has UPS included some patch that I don't have for their reports, or are they using a different version?

For a fellow on the inside, I have a whole list of questions.

I'll talk about rain if you'll answer me! (in Scotland they call it sun) I'll talk about blissards and hail (in Scotland they call it "fair with bright patches" I'll even talk about el Nino (in Scotland they call it Mexican food)
Just tell me about these friggin' reports!!

 

[Crystalguru]

Kieso,

I don't mind helping you out. Not sure what is left in my brain from those times....

e-mail me at carrie_franks@yahoo.com with your phone#.

 

[kieso]

Cool, Carrie

I'll e-mail you