Timestamp vs. Date + Time fields

[Justin Close]

Is there any real difference between using a 'timestamp'-datatype field vs separate 'date'-datatype and 'time'-datatype fields?

Both timestamp and time are a number of seconds.  Date is a number of days, which could be converted to seconds...there is convenience, and probably some indexing affect, but is there something unique about a timestamp field that can't also be achieved with date and time fields?  Is there some inherent difference in their datatypes that enables some beneficial result?

Just wondering.

 

--  Justin

[comment]

The difference is two fields vs. one. This may be more significant than it seems. Consider, for example, a relationship using the > relational operator..

[LaRetta]

Wow, it is indeed significant.  Thank you for reminding me.

Excellent question as well, Justin.

[Justin Close]

Yes, the multiple field difference is a given.    Apart from just having two fields, that's what I was getting at with mentioning possible indexing issues.  Well, not 'issues', but just performance hits:  has to create and/or update multiple indexes (possibly) - makes the file larger, too.

But it is often that we create separate fields, for UI reasons.  Because you want to show the Date in places, and with FM's restrictions on what is editable/configurable for displaying the date (the actual CSS/layout look, not the format of the string necessarily), the end goal for 'look' can best be achieved by a discrete date field.  You just can't quite get it right trying to mold a timestamp into a UI element.

The relationship issue is a very good point, though.  That's the kind of thing I was gist-ing at.  To play devil's advocate, though, wouldn't a two-predicate relation that compared the two fields (in each table) individually yield the same results?  DateA > DateB & TimeA > TimeB?  More awkward with the two fields, yes, and perhaps more of a performance hit (?), but still doable.

Edited August 5, 2015 by Justin Close

[comment]

To play devil's advocate, though, wouldn't a two-predicate relation that compared the two fields (in each table) individually yield the same results?  DateA > DateB & TimeA > TimeB? 

No. Today afternoon is later than today morning - but it wouldn't pass your test.

There are other aspects, too. For example, with timestamp alone, you cannot group records by date.

 

You just can't quite get it right trying to mold a timestamp into a UI element.

I am not sure I understand what you mean by that.

[Justin Close]

Can't get them to look right, without separating things like the border from the text, i.e. use a 'square' graphic to surround the text instead of using borders on the text field.  (It's more my co-worker who goes on about this.  )

Ah, I see the problem with the today times.  An interesting case.  Could just make the Date check use ">=", and the time just use ">".  I think that would work (without testing or spending too much time thinking about it.)

 

Edited August 5, 2015 by Justin Close

[comment]

 Could just make the Date check use ">=", and the time just use ">".  I think that would work

I'm afraid not. The problem is not "with the today times", that was just an example. You cannot use > for time, because any time tomorrow, or on any day in the future, is later than whatever time it was today.

 

Edited August 5, 2015 by comment

[bcooney]

I am enjoying this discussion. I must admit, I create both a createdTS and createdDate (either a calc GetAsDate (createdTS) or just another field using auto-enter, because I cannot format a TS on a layout well either. I wish we could hide the time portion.

[comment]

I wish we could hide the time portion.

You sort of can, by placing a bunch of spaces between the date and the time and making the field sufficiently narrow.

[bcooney]

Bummer, I thought you might come back with something I've missed. Yes, I've done that too. Ideally, the time part of the format could be set to "do not show."

[Justin Close]

Ermmm...how do you place spaces between the two parts?  How would you hide the time part with 'do not show' (I assume you mean 'hide object when')?

My partner found that you can get the TS field to accept your date formats (ala "m/d/yy" or "mm/dd/yy" if you also apply a Time format "hhmm" to it, too.  Its a bit counter intuitive to me, doing that through the inspector.  This was always one of the big gripes with a TS field is that it didn't seem like you could apply formats to the date.

[comment]

My partner found that you can get the TS field to accept your date formats (ala "m/d/yy" or "mm/dd/yy" if you also apply a Time format "hhmm" to it, too. 

I found that too, about 10 years ago. I could have told you, but you didn't ask about that ...

 

how do you place spaces between the two parts?

In the same Inspector window, you can specify a trailing string for each date element (if using a custom format), as well as a leading string for the time.

 

How would you hide the time part with 'do not show' (I assume you mean 'hide object when')?

I am afraid you misread that part. That was wishful thinking on Barbara's part. You cannot hide the time as an object, because it's not an individual object.

Note that you can "hide" the date part, by custom-formatting all date elements to 'None'.

 

Edited August 7, 2015 by comment

[Justin Close]

Ah, got it. 

[info las vegas homes]

I'm currently converting a few of my files, that I've utilized in the real estate industry for a number of years, from Filemaker 9 to Filemaker 14. We were told that a few (undisclosed) scripts may not work on 14 - which has turned out to be the case. One of which, I figured was rather elemental, and that I could figure out on my own. This too has not been the case and I'm reaching out to see if someone can help me? We are trying to get a script that will work as a timestamp. Essentially, clicking on one of my 'button' futures - and that 'button' taking you directly to a field and placing a timestamp (date & time), then allowing you to type. This purpose is to keep a contemporaneous log. Can anyone help? Thanks in Advance! Mike