Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 7350 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Posted

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

Posted

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.

  • Newbies
Posted

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

Posted

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") ;

"")

Posted

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. crazy.gif

Hopefully, FileMaker will fix this blush.gif with the next patch, so Timestamp calculations work correctly.

Posted

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.

Posted

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?
Posted

I think displaying the difference between 2 timestamps as a timestamp is meaningless. Would you show the difference between 2 date as a date?

Posted

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. tongue.gif 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. cool.gif

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.