What result are you getting? Not sure why there is a need to use SQL here, however you can simply subtract a timestamp from another timestamp. FileMaker will return the result as a time. You can then use GetAsNumber () to calculate the seconds.
Could you post an example solution for what you are trying to do?
Timestamp1 - Timestamp2 gives you the difference in seconds. Use other functions to convert it to dd/hh/mm/ss.
You expect ExecuteSQL to return you a date, but it is a string. You are subtracting one string from another. Try to add GetAsTimestamp() to both.
Sure, here you go...
I need to get the max and min Timestamps from a table so I can calculate the difference between the first and last Timestamps for an average calculation.
Although the Timestamp values appear in FileMaker table view in traditional FileMaker format: "mm/dd/yyyy ....", the ExecuteSQL returns two values like this:
The value it returns, from original posting above, is: 9199983060
Thinking this is possibly in milliseconds, and trying to get Days, I divided by 1,000 (ms -> seconds), then divided by 3600 (seconds per hour), and finally divided then by 24 (24 hours in a day). This gives me an answer of 106.48128541666667. Not right.
Yet, the simpler calculation...
GetAsDate("2/20/2016") - GetAsDate("10/20/2015")
gives me 123, which looks right.
Therefore, I have no idea what the units are in "9199983060" here since this is a huge number and how to convert it to "days".
My goal is to calculate the number of days between these two Timestamps.
a couple of SQL functions:
COALESCE(\"date\",'') // two single quotes on the end
"returns mm/dd/yyyy h:mm:ss A instead of yyyy-mm-dd hh:mm:ss formatting"
- quoting myself. LOL
However, there may be a problem with mixing the Aggregate within the other functions:
may or may not work - I haven't tested.
But I don't think that's what you are asking here.
A timestamp field can store date values from January 1, 0001 to December 31, 4000. Timestamps are measured in seconds.
FileMaker Pro stores timestamps as the number of seconds since midnight on January 1, 0001
That's what makes it possible to perform addition and subtraction (on time and date and timestamp fields).
Well, you did not read my message, You are subtracting two strings! For FileMaker it looks like:
"2015-10-20 15:19:57" - "2016-02-20 13:50:17"
Try to add this to data viewer and you will get the same result. - 9199983060
I was wrong, you can not use GetAsTimeStamp() as the timestamp is in the SQL format. There are CFs which are dealing with this, or you can write your own calc. You will need to convert
"2015-10-20 15:19:57" into "10-20-2015 15:19:57"
After this try
GetAsTimestamp ("10-20-2015 15:19:57") - GetAsTimestamp("02-20-2015 13:50:17")
I am getting 5809:29:40, although I have to convert into UK format first.
Thanks. I found all the answers here helpful, but this answer seemed the most helpful over all.
I wanted to follow up to say that, unless I'm missing something, it's strange that FM has three representations for a Timestamp:
(1) In the DB Table View, it's 11/30/2015 3:09:25 PM
(2) From a Select Statement, it's 2015-11-30 3:09:25, and finally...
(3) A converted TImestamp so you can use FM functions: 11-30-2015 3:09:25
And, to get (3), you have to write a CF? Huh?
Again, assuming I'm not missing something, it seems that FM really, really needs a unified Timestamp (and date) format. Functions should work universally or there should be simple conversion functions, in FM, for example, to take (2) --> (3) above, if necessary.
Using MySQL again as a reference, it's as simple as:
select (UNIX_TIMESTAMP(max(<FieldName))) - UNIX_TIMESTAMP(min(<FieldName))/86400 from <tablename>
Since you're a SQL Server guy, I'm sure it's simple in SQL Server also, or at least unified, right?
It's no wonder Timestamps are so confusing in FM.
I must have gotten the non-helpful "?" 100 times yesterday.
Thanks to your reply posting, I think I now better see the problem I was having.
Thanks again, N.
The Min() and Max() appear to be problematic for FM since they appear to return a text string as Nicolai pointed out..
And, it returns a formatted string that FileMaker functions don't seem to understand (GetAsTimestamp, for example) to do math on, and, without reformatting the SQL-returned Timestamp string, "GetAsTimeStamp()" just returns "?"
(I love that. So helpful: "?". Yes, I know exactly what the problem is now!!!).
it's simple in SQL Server
In SQL Server timestamp does not mean timestamp (it is rowversion)! The actual timestamp as data type is datetime and it looks like this:
"2007-04-30 13:10:02.0474381" , which is the same as the 1 in your list
"11-30-2015 3:09:05" is the same as "11/30/2015 3:09:05". I used Japanese (I think) format as it is easier to convert to.
GetAsTimestamp will work on both
You have a point, FMP could be more consistent or might have some conversion function, but some good CFs are available so it did not bother me much.
Yes! this is a long-time request to allow, manage, convert between these kinds of dates (yyyy-mm-dd) and FMP dates (mm/dd/yyyy or dd/mm/yyyy).
In the meantime, we convert with Custom Function (or create our own):
I don't know if this method would allow you to use the MAX() or MIN() in eSQL:
SELECT ''||MAX(\"date\") // concatenate the two single quotes before the aggregate
This discussion on this forum also mentions this method, but does not test with aggregates:
my "guess" is that this won't work either. and you'll get this error:
Expressions involving aggregations are not supported.
I think the issue is the Max and Min functions keep that from working.
SQL: ExecuteSQL("select max(<FIELD_NAME>) from <TABLE_NAME>" ; ""; "") returns:
in the data viewer, but ...
ExecuteSQL("select GetAsTimeStamp(max(<FIELD_NAME>) ) from <TABLE_NAME>" ; ""; "")
ExecuteSQL("select max(GetAsTimeStamp)<FIELD_NAME>) ) from <TABLE_NAME>" ; ""; "")
return, wait for it ,....
GetAsTimeStamp("11-30-2015 3:09:05") will work, but
GetAsTimeStamp("2015-11-30 3:09:05") does not.
Thanks for your reply. Good to know.
My Workaround (workaround++) was to just do this the FileMaker way (pre-SQL) and create a Cartesian self-join. Then use the min, max, and count functions so I could compute the "$###^!!!#" number. Works fine now, but the calculated field's formula is overly complex looking (three lines) since FM doesn't seem to return simple millisecond difference (that I know of) and you have to thus parse the colon-delimited string. (2093:22:11, or whatever)