Timestamp Issue With Indexes and ExecuteSQL

[adellos]

Sample file for displaying timestamp index bug with ExecuteSQL

timestamp test.fmp12.zip

[adellos]

I've found what I have to think is a bug with indexes and ExecuteSQL.

I've attached a very simple sample. To see the buggy behavior, do the following.

Go into the "Define Database" window. Set the indexing value for the field 'timestamp_with_index' to 'none'. Run the script: 'timestamp_lookup'.

Now go back into the "Define Database" window and set the indexing value for the field 'timestamp_with_index' to 'all'. Run the script: 'timestamp_lookup'.

The second time you run it, the left "column" in the dialog should have no id values. This seems to be a bug with explicitly creating an index for a timestamp field and using ExecuteSQL to search against that same timestamp field.

I'm running FileMaker Pro Advanced 12.0v2 on Mountain Lion (10.8.2)

Anthony

[Lee Smith]

Automatic message

This topic has been moved from "Community Resources → Articles, Tips, Techniques & Solutions" to "Database Schema & Business Logic → FQL - Internal SQL".

I've merged your two topics because they are apparently about the same thing, although you didn't provided any information in the Article post. Therefore, I'm not sure why the Double Post?

The Articles topic is reserved for the members to post Articles on How to accomplish something in FileMaker.

If you have any questions about this action, just send me a Private Message.

Lee

[adellos]

Lee,

Sorry about uploading the sample file for my post (I did write a post in the forum you indicated was the correct one) in the wrong place.

This is the first time I've attached a file and didn't realize the posting mechanism has an "attach file" at the bottom, I was looking for it in the "toolbar" instead. Thank you for moving the file for me.

Anthony

[David Jondreau]

Looks buggy to me.

[adellos]

David - I take it you got the same behavior?

I can't work out a way that it makes sense that indexes should break SQL queries against timestamp fields. I've been out of the FileMaker 'world' for awhile. Is there a best practice for submitting bugs to FMI?

[David Jondreau]

I am getting inconsistent behavior between the indexed and non-indexed versions. But there is something else going on (either in place of or in addition to the indexing issue). If I change your WHERE criteria to a more recent date (say 09-20) I get a null result. That's pretty odd.

[adellos]

Right - it's flakey. As long as I leave the index off, the ExecuteSQL has worked as expected for me.

I reported this as in issue in the "issue reports" forum @ forums.filemaker.com.

[dansmith65]

Have you tested on Windows? I don't think I'm getting the same result as you. The script shows an empty dialog both times for me.

Using this calculation, I get a list of all records id's for both fields (indexed and non-indexed):

ExecuteSQL ( "SELECT id FROM timestamp_test WHERE timestamp_with_index > ?" ; "," ; ¶ ; Timestamp ( Date ( 1; 1; 1900); Time ( 0; 0; 0)) )

[David Jondreau]

Right - it's flakey. As long as I leave the index off, the ExecuteSQL has worked as expected for me.

Really? Regardless of the index settings, if I change your timestamp form '1900-01-01 00:00:00' to '2012-09-20 00:00:00' it returns null.

[adellos]

Try this query: "SELECT id FROM timestamp_test WHERE timestamp_no_index > '2012-20-09 00:00:00'" I think the day has to come before the month in the string literal used as the timestamp value in the WHERE condition.

[David Jondreau]

I don't think that's it. 1) Because the SELECT shouldn't find any records if it's reading the timestamp as 12/9/12 and 2) If I change it to the other format, I still get incorrect results.

My gut feeling is there's two issues...a problem with how ExSQL works with indexing timestamp and date fields and how we're testing those fields.

and 3) The result is in the yyyy-mm-dd format

I am confused.

Edited October 2, 2012 by David Jondreau

[adellos]

Dan - That's a hair "apples to oranges" since you're using the timestamp function (which returns a date like this '1/1/1900 12:00:00 AM') while I was using a string literal with a SQL format date.

FWIW using a string literal with the same format as what the timestamp function returns also works fine,so:

"SELECT id FROM timestamp_test WHERE timestamp_no_index > '1/1/1900 12:00:00 AM'" works just as well as using the Timestamp function

It's still buggy behavior to have the SQL date format work properly for non-indexed timestamp fields, but not for indexed timestamp fields. Indexing should not make SQL syntax become invalid IMHO.

Anthony

David - In regards to this:

Try this query: "SELECT id FROM timestamp_test WHERE timestamp_no_index > '2012-20-09 00:00:00'" I think the day has to come before the month in the string literal used as the timestamp value in the WHERE condition.

The date shouldn't have to be formatted that way, it's backwards, but it does work. The SQL-92 standard says it should be YYYY-MM-DD, so not sure why it's backwards with FMP.

I'm going the route Dan suggested and using the format Timestamp returns or just using the Timestamp function which seems to always work.

Anthony

[adellos]

David - Yup - confusing. I'd say there's some bugs in how the ExecuteSQL is implemented internally related to how it parses the SQL string and date values.

[David Jondreau]

It appears the way Filemaker stores dates and timestamps, with / slashes works find, index or no. Putting your date into "SQL format" with dashes causes problems.

[beverly]

You should be able to pass the date 'mm/dd/yyy' and time 'hh:mm:ss' (24-hour time) as well as the way David suggested.

Add these "constants" to your query and see the formats returned:

, CURRENT_TIMESTAMP , STRVAL ( CURRENT_TIMESTAMP ) , COALESCE ( CURRENT_TIMESTAMP , '' )

[Priyabrata]

Hi All,

 

Today I also faced a bug while trying to create an execute SQL calculation for a field which needs to compare the time stamps.

 

While we make a comparison of an time stamp data in an execute SQl statement with the Filemaker time stamp fields.
The comparison wasn't made based on time stamp rather it was comparing only the time part from the time stamp and wouldn't consider the dates in the time stamp for comparison.

For Example:

if we want to compare an time stamp value in an Execute SQl statement that is

2013-09-04 12:00:00  > 2013-09-03 21:00:00   

It compares the time from the time stamps and returns 0.

 

2013-09-04 12:00:00  < 2013-09-03 21:00:00   

And for the above the condition it returns 1.

 

I tried to make the above comparison by converting the time stamp in filemaker to the same format that results out from an SQL query.

 

 

Thanks 

Priyabrata Sahoo

Filemaker Developer

Mindifire Solutions

www.mindfiresolutions.com