Newbies Bill Taggart Posted December 13, 2004 Newbies Posted December 13, 2004 This is my initial post as a Novice. I'm using a FM (7.0) database of parsed server access logs to study visitor usage patterns to my site (www.the-intuitive-self.org). I've used FM on a modest scale for simple off-line databases. Now I'm tackling ScriptMaker for the first time. I've got my first script up and running (after many false starts) with one exception. I'm trying to calculate an elapsed visit time (DT Visit) using TimeStamp variables to represent the date and time of the most recent request (DT Request) and the first request (DT First) in a visitor session, e.g.; DT Visit = DT Request - DT First where all variables are defined as TimeStamp and stored in the format; 11/10/2004 20:50:46. The calculation appears to work as long as "DT Request" and "DT First" are no more than 24 hours apart even when they are on consecutive days, e.g.; 01/02/2004 00:35:57 - 01/01/2004 23:35:03 successfully yields 1/1/0001 1:00:54. However; 11/10/2004 20:50:46 - 11/05/2004 23:29:47 yields "?" which I assume means undefined. I expect the result to be approximately 1/5/0001 23:35:25, i.e. 4 days, 23 hours, etc. The references I've checked haven't shed light on the problem. Any guidance will be appreciated. Thanks, Bill
Barbecue Posted December 14, 2004 Posted December 14, 2004 As a matter of fact I was just noticing this same problem yesterday. I believe FileMaker treats the date and time portions of a timestamp field as two separate pieces, and doesn't seem to be very smart about carrying the time piece over to the date piece. I'd call it a bug, since it doesn't work as described in the docs, and produces unexpected results. (The help file says you can't have a negative timestamp, but in fact you can.) As a workaround, express the difference between the two timestamps as a number instead of a timestamp, then take the resulting number and force it into a timestamp value. The result will be the timestamp you would normally expect to see.
RalphL Posted December 14, 2004 Posted December 14, 2004 The difference between 2 timestamps should be the number of seconds that they are separated.
Newbies Bill Taggart Posted December 14, 2004 Author Newbies Posted December 14, 2004 Thanks to Barbecue and RalphL. Your suggestions put me on the right track. But I'm still a bit stumped. I found the function for converting Time to seconds (Seconds) and the function for reversing the process of converting total Seconds into a TimeStamp (GetAsTimestamp). To complete the process of converting from a TimeStamp to seconds, is there a function that wasn't obvious to my searching eye for converting a Date to seconds? With the latter unknown third function, I would take the TimeStamp givens in my calculation (DT Request and DT First); 1. Convert their Time (Seconds) and Date into total seconds, 2. Use that result to calculate the difference in equivalent seconds, 3. Finally convert that difference back into a TimeStamp (GetAsTimestamp). Is there a function for converting a Date to seconds? If not what is a recommended work around? Thanks again, Bill
RalphL Posted December 14, 2004 Posted December 14, 2004 Try the following text calculation: Case ( not (IsEmpty ( TimeStamp1 ) or IsEmpty ( TimeStamp2 )) and TimeStamp1 > TimeStamp2 ; "TimeStamp2 must be later than TimeStamp1" ; not (IsEmpty ( TimeStamp1 ) or IsEmpty ( TimeStamp2 )) ; Let ( [ Dif = TimeStamp2 - TimeStamp1 ; D = Div ( Dif ; 86400 ) ; H = Div ( Mod ( Dif ; 86400 ) ; 3600 ) ; M = Div ( Mod ( Dif ; 3600 ) ; 60 ) ; S = Mod ( Dif ; 60 ) ] ; D & " Days " & H & " Hours " & M & " Minutes " & S & " Seconds") ; "")
Newbies Bill Taggart Posted December 14, 2004 Author Newbies Posted December 14, 2004 RalphL - ELEGANT! - Thanks, Bill
Barbecue Posted December 15, 2004 Posted December 15, 2004 With the latter unknown third function, I would take the TimeStamp givens in my calculation (DT Request and DT First); 1. Convert their Time (Seconds) and Date into total seconds, 2. Use that result to calculate the difference in equivalent seconds, 3. Finally convert that difference back into a TimeStamp (GetAsTimestamp). Is there a function for converting a Date to seconds? If not what is a recommended work around? There's an easier way to achieve this result. To get the difference between two timestamps into a TimeStamp field without using any functions: DiffTS = 1 * (EndTS - StartTS) Where DiffTS, EndTS, and StartTS are all timestamp fields. The reason this works is that all of the date and time values are really just numeric values with special formatting and operations associated with them. Multiplying by a numeric value forces an implicit conversion from timestamp to number, but then the result is immediately converted back into a timestamp again when it's stored. Date fields are actually numbers indicating a number of days since an arbitrary starting point in the past (January 01, 0001). Times and Timestamps are really just the number of seconds. Times are measured from the most recent midnight, timestamps from midnight of the arbitrary first date. For example, if TS is a timestamp field and N is a numeric field, Set Field[ N, TS] will put the number of seconds in N. Date * 86400 + Time = TimeStamp This also means you can do some really silly things, like multiplying and dividing dates. For example, Bastille Day divided by the death of Pope John II equals Wednesday. Hopefully, FileMaker will fix this with the next patch, so Timestamp calculations work correctly.
RalphL Posted December 15, 2004 Posted December 15, 2004 What does the result of your calculation mean? You have some meaningless date with a time. You don't handle dates this way. I think you need to rethink what a timestamp is. It is the number of seconds from 1/1/0001 00:00:00. It is stored as a number and displayed as date and time.
Barbecue Posted December 15, 2004 Posted December 15, 2004 What does the result of your calculation mean? You have some meaningless date with a time. You don't handle dates this way. I think you must have missed the part where I said... "...you can do some really silly things..." The implication of the word silly is that it's NOT something useful or meaningful. I think you need to rethink what a timestamp is. It is the number of seconds from 1/1/0001 00:00:00. It is stored as a number and displayed as date and time. Not sure I understand your point. This is exactly what I just said in my last post. What is it that you think I don't understand about timestamps?
RalphL Posted December 17, 2004 Posted December 17, 2004 I think displaying the difference between 2 timestamps as a timestamp is meaningless. Would you show the difference between 2 date as a date?
Barbecue Posted December 17, 2004 Posted December 17, 2004 First, in my defense I have to point out that I was merely providing what the original poster asked for, a way to get the difference between two timestamp fields into another timestamp field and work around a FileMaker bug. And that's what I showed him how to do. Second, I must respectfully disagree that the result is meaningless. While not perfect and not elegant, it is a quick-and-dirty way to see the magnitude of a Delta-T value in a meaningful format without having to do any calculations whatsoever. All you have to do is remember that date values start at one instead of zero, and you can tell at a glance the elapsed time. For example: [06/02/1975 17:02] - [05/10/1975 13:40] = [01/24/0001 03:22] I can quickly see the difference between the two dates is 23 days, 3 hours, and 22 minutes, and I don't have to do any calculations or create another field to get there. Re-read Bill Taggart's original post, you'll see that he also recognized that this, when he said... I expect the result to be approximately 1/5/0001 23:35:25, i.e. 4 days, 23 hours, etc. Based on that comment I assumed Bill was probably doing the same sort of thing I do, just trying to get a quick answer with as little effort as possible. Is this best practices? Of course not. Is it sloppy? Sure. I wouldn't ever use something like this in an end-user solution. But when I just want to get the results on the screen quickly so I can move on to the next task, quick-and-dirty shortcuts like this are a great way to save a little time.
Recommended Posts
This topic is 7350 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now