none
write property date time format in search RRS feed

  • Question

  • I want to get all documents created/updated during a specific hour. This query write<=2011-04-11T06:28:28Z AND write>=2011-04-11T05:28:28Z returns all documents on a particular day. Is there any good way to get correct results or I need to use another property for searching.

     

    Thanks

    Raheem

    Monday, April 11, 2011 10:39 AM

Answers

  • The query:

    (Write>=2011-04-12T00:00:00Z AND Write<2011-04-13T00:00:00Z)

    Is translated into the following FQL once executed:

    and("write":range(datetime("2011-04-11T23:00:00.0z"), max, from="ge", to="le"), "write":range(min, datetime("2011-04-12T23:00:00.0z"), from="ge", to="lt"))"

    As you can see, the times are adjusted 1 hour, due to my time zone settings, ignoring the Z from the original query. But it will limit the results to just one day, and is the option you have. And the query is not so different from my original FQL, it uses two filters instead of one, but with the same result.

    Also note that I use >= and < in order to not match the first second of the new day.

    You cannot enter FQL directly into the search box for the core results web part.

    -m


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Tuesday, April 12, 2011 6:54 AM
  • You are absolutely right. It ignored any hours or minutes you pass in.

    The Search Center UI strips away the hours/minutes/seconds, so you cannot get it to work via the UI, only with the API directly.

    Regards,
    Mikael 


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/

    Thursday, April 14, 2011 11:22 AM

All replies

  • This FQL will list all documents from a starting time to the ending time:

    write:range(2011-04-11T00:00:00Z,2011-04-12T00:00:00Z,from="GE",to="LT")

    GE means greater than or equal to the start datetime, and LT means less than the ending time, so you will not have duplicates on the last second.

    Regards,
    Mikael Svenson 


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Monday, April 11, 2011 11:53 AM
  • Well, I need to use query on web front end, Core Search Results web part. I entered this query on search Center and it says "
    Your query is malformed. Please rephrase your query."
    Any help ?
    Monday, April 11, 2011 12:49 PM
  • The query:

    (Write>=2011-04-12T00:00:00Z AND Write<2011-04-13T00:00:00Z)

    Is translated into the following FQL once executed:

    and("write":range(datetime("2011-04-11T23:00:00.0z"), max, from="ge", to="le"), "write":range(min, datetime("2011-04-12T23:00:00.0z"), from="ge", to="lt"))"

    As you can see, the times are adjusted 1 hour, due to my time zone settings, ignoring the Z from the original query. But it will limit the results to just one day, and is the option you have. And the query is not so different from my original FQL, it uses two filters instead of one, but with the same result.

    Also note that I use >= and < in order to not match the first second of the new day.

    You cannot enter FQL directly into the search box for the core results web part.

    -m


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Tuesday, April 12, 2011 6:54 AM
  • Thanks Svenson.

    What I need is to search word documents modified between two date times. And I need to put query in FAST Search Centre. Is there any way of achieving this ?

     

    Any help is highly appreciated.

    Thanks

    Tuesday, April 12, 2011 4:25 PM
  • Yes, you can use the query from my last post:

    (Write>=2011-04-12T00:00:00Z AND Write<2011-04-13T00:00:00Z)

    much like the one you posted as well.

    -m


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Tuesday, April 12, 2011 5:59 PM
  • Thanks Svenson

    (Write>=2011-04-12T00:00:00Z AND Write<2011-04-13T00:00:00Z)

    This query works fine for dates but not for times. What I need to do in order to filter resutls on time(minutes and seconds).

    Any suggestions on this ?

    Thanks

    Thursday, April 14, 2011 9:45 AM
  • You are absolutely right. It ignored any hours or minutes you pass in.

    The Search Center UI strips away the hours/minutes/seconds, so you cannot get it to work via the UI, only with the API directly.

    Regards,
    Mikael 


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/

    Thursday, April 14, 2011 11:22 AM
  • Thanks a lot Svenson for this investigation. I appreciate.
    Friday, April 15, 2011 2:02 PM
  • Note that the resolution in the rank profile relates to freshness relevance boosting, it does not impact the actual results matching
    Friday, April 15, 2011 2:53 PM
  • Knut, you are of course absolutely right. The FQL via the API does second resolution without changing the rank. And I was so sure I actually tested this. I will edit my answer to reflect this.

    -m


    Search Enthusiast - MCTS SharePoint/WCF4/ASP.Net4
    http://techmikael.blogspot.com/ - http://www.comperiosearch.com/
    Friday, April 15, 2011 6:45 PM
  • Hi ,

    I have created one date time managed property,When i search like managedproperty1=2011-09-17T04:00:00Z  ,

    Its returning me the result which has date value of 2011-09-18T04:00:00Z  

    Similarly if we use any of the comparision operators like <=,>= the behaviour is inconsistent as above.

      in the managedproperty1 ..Anyone has faced this issue till now and have solved it??

     

    Thanks,

    Altaf


    ALtaf Ali
    Thursday, September 22, 2011 9:01 AM
  • Altaf,

    I think you are using KQL (via the search center?). Your query will be adjusted for time zones, even if you add Z to it. Both the hours and Z are ignored when using kql syntax as I answered earlier in this thread. Hopefully a bug which will be fixed.

    Regards,
    Mikael Svenson 


    Search Enthusiast - SharePoint MVP/WCF4/ASP.Net4
    http://techmikael.blogspot.com/
    Thursday, September 22, 2011 9:15 AM
  • Svenson,

    Thanks for your reply ,yes i am searching via the search center.  

    but even if i search like managedproperty1=2011-09-17  without specifying the time its returning me the results of value 2011-09-18 ,not sure whether it is a bug??

    let us take as if i want to get the results for 2011-09-17 and greater what query i have to pass??

     When i use the default date time manged property (write) its wiorking as expected but not my custom date time managed property.

    for example :write=2011-09-17  is giving me exact results.

     

    Thanks,

    Altaf.

     

     


    ALtaf Ali
    • Edited by POCCoder Thursday, September 22, 2011 11:15 AM
    Thursday, September 22, 2011 10:24 AM
  • We have observed one more issue with datetime managed property if we search like managedproperty=2011-3-31 (end of the month date) then its gving all inconsistent results .As svenson mentioned it will not be taking the time it should give me the results of managedproperty which has value 2011-3-31 . But its not ..can anyone check or have faced this issues before with the custom date time managed property?? Thanks, Altaf Ali.
    ALtaf Ali
    Thursday, September 22, 2011 3:05 PM
  • Hi,

    When I wrote my kql 2 fql parser I experienced some weirdness on dates as well, but didn't get around to investigate it further. But it was related to end of the month and that the time zone is adjusted afterwards, and giving weird results.

    If you download and install fs4splogger.codeplex.com on your FAST QR server, you can monitor the queries and see how the query is re-written to fql. This might help you establish a pattern on how it fails.

    Regards,
    Mikael Svenson 


    Search Enthusiast - SharePoint MVP/WCF4/ASP.Net4
    http://techmikael.blogspot.com/
    Saturday, September 24, 2011 5:53 PM
  • HI,

    can we pass query as manageproperty="2011-09-18T00:00:00Z " in the extended core results web part and get the exact results??

     I have tried it but it is not giving the results as expected i have checked the query in fs4sp logger the range is between 2011-09-18T05:00:00Z to 2011-09-19T05:00:00Z.

     

    am i going wrong anywhere?? Can anyone help out how to pass date time query in fixed query parameter of core results webpart???

     Thanks,

     Altaf


    ALtaf Ali
    Monday, October 3, 2011 2:26 PM
  • Hi,

    The code at spsearchparts.codeplex.com for the data parsing is as follows:

    DateTime dateTime = DateTime.Parse(propVal);
    propVal = dateTime.ToString("yyyy-MM-ddTHH:mm:ss") + "z";
    


    I haven't tested it extremely well for hour adjustments. Can you try to query like this instead: mp="2011-09-18"

    without specifying hours, minutes and seconds? The logic then will do query for the whole day.

    As for using the fixed query parameter, you can add a normal date query, but with FS4SP it will drop whatever hours and do time zone adjustment as mentioned at the beginning of this thread.

    Regards,
    Mikael Svenson 


    Search Enthusiast - SharePoint MVP/WCF4/ASP.Net4
    http://techmikael.blogspot.com/
    Monday, October 3, 2011 5:14 PM
  • Hi,

    I have searched like managedproperty1="2011-10-28",

    If i check in the FS4SP logger for the above query

    managedproperty1":range(
            datetime(
                "2011-10-28T05:00:00.0z"
            ),
             datetime(
                "2011-10-29T05:00:00.0z"
            ),
             from="ge",
             to="lt"
        )

    I have many recordes with 2011-10-28T0400:00.0z  that are not appearing in the results.We have many records with 2011-10-28T02:00:00.0z that i dont want.  So thats y i thought of passing the time along with the query in the fixed query property of extended core results webpart.

    If we can pass the time we can pass the query as managedproperty1>="2011-10-28T00:00:00z" AND managedproperty1<=23:59:59z" to restrict the results to one day.If we will be able to pass like this we can even avoid last date of the month issue.

    Help is highly appreciated ...

     

    Thanks,

    Altaf.


    ALtaf Ali
    Tuesday, October 4, 2011 7:44 AM
  • Hi,

    The above thing i am able to do it throught keywordquery class by enabling the FQL and passing the FQL query.

    But not with the fixed query property of extended core results web part. Anyone have idea about this?

     

    Can i make fast to take 00:00:00z as the time while passing the query rather than it itself taking the timezone timing?

     

    Thanks ,

    Altaf.


    ALtaf Ali
    Thursday, October 6, 2011 11:30 AM
  • Hi Altaf,

    Fixed query will be evaluated the same as a regular query, so with the oob core results web part it will still remove your hours and adjust the date/time for your timezone.

    This happens in the kql->fql parsing of FAST for SharePoint, which too me is a bug.

    -m


    Search Enthusiast - SharePoint MVP/WCF4/ASP.Net4
    http://techmikael.blogspot.com/
    Thursday, October 6, 2011 4:08 PM
  • Hi ,

    Anyone has got any updates regarding the above issue??  passing exact time in the extended core result web part.

    Do we have any work around to work this out?? Anything from query side or index side??

    In the crawl log we have something to check what records have been crawled in the sepcific range of time?? its giving us the expected results,anyone have the idea what approach they are using there?

     

    Thanks,

    Altaf


    ALtaf Ali
    Tuesday, October 18, 2011 3:32 PM
  • Hi Altaf,

    As per my original answer, any hours is stripped off when using date/time with the oob Core Results web part. You will have to create your own in order to work around it.

    I have not seen any updates on this in any CU's, but we can hope.

    And query against the crawl logs is a sql query operation, search against FAST is very unrelated.

    Regards,
    Mikael Svenson 


    Search Enthusiast - SharePoint MVP/WCF4/ASP.Net4
    http://techmikael.blogspot.com/
    Tuesday, October 18, 2011 4:41 PM
  • Hi Altaf/Mikael,

    Looking at complete blog trail its came to know that like when we tried to search with KQL/FQL for particular date range (considering time too) the result set is inconsistent. is it ?

    If Yes,

    Instead of applying fixed query to core result webpart can you perform the searches by using refinement token query parameter "r". ?

    If Yes,

    With the below query you can get the refinement token in refinement result sets...(Use MOSSMAN developer tool to get the token...or programmatically hit the query to get this token using search service.)

    write(discretize=manual/YYYY-MM-DDThh:mm:ss/YYYY-MM-DDThh:mm:ss)

    Refer this link, http://msdn.microsoft.com/en-us/library/gg984547.aspx

    use above in query XML and get the token from tool and hit the url by forming query parameter "r".

    for ex : http://hostname:1212/sites/search/pages/default.aspx?k=a?r=write=token

    Check for results. If is it consistent..then well and good.

    This is an another approach u could try, I dont know the exact feasibility to sync this implementation with ur current one.

    Thanks and Regards,

    Uday 

     

    Tuesday, October 18, 2011 4:58 PM
  • Hi Uday,

    this is a very good tip indeed :D Excellent work digging it up!

    Regards,
    Mikael Svenson 

     


    Search Enthusiast - SharePoint MVP/WCF4/ASP.Net4
    http://techmikael.blogspot.com/
    Tuesday, October 18, 2011 5:23 PM
  • Thanks,

    Mikael.

    Tuesday, October 18, 2011 5:27 PM
  • Hi Uday/Svenson

    I have one more work around for this using keyword query class as below.

     keywordQuery.EnableFQL=true;

     keywordQuery.QueryText = "\"managedproperty1\":range(datetime(\"2011-05-04T00:00:00.0z\"),datetime(\"2011-05-04T23:59:59.0z\"),from=\"ge\",to=\"le\")";

     But i wanted to do something by using the extended core result web part only which i think is not possible as of now...

    Can we enable FQL in core results web part??

    Sorry uday i havent tried your option but it seems interesting ..have you tried that option?

     

     

     Regards, Altaf.


    ALtaf Ali

    • Edited by POCCoder Friday, October 21, 2011 10:13 AM
    Friday, October 21, 2011 10:07 AM
  • Hi altaf,

    You can enable FQL in your own core result webpart, but you need to customize it a bit. Take a look at the code on my webpart at http://spsearchparts.codeplex.com which enables fql support as an option or by prefixing the query with "fql:".

    Regards,
    Mikael Svenson 


    Search Enthusiast - SharePoint MVP/WCF4/ASP.Net4
    http://techmikael.blogspot.com/
    Friday, October 21, 2011 10:36 AM
  • Had similar trouble.

    Server Time Zone is UTC + 3

    So if I used following query: "...  Date07<={Today}". This yielded results that are from tomorrow as well as 15/06/2013 transferred into "2013-06-14T21:00:00Z" and KQL only took the date part :(

    First attempt was to extend ContentBySearchWebPart with custom {TodayUTC} token (used following article as a starting point: http : // sptalks.wordpress.com/2013/04/23/how-to-programmatically-change-content-search-web-part-querytext/) and replaced it with SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow) with no luck due to the fact that KQL still ignored the time and Z part of it

    Although, managed to find a workaround without harming Time Zone issues.

    Solution:

    Changed the query to "... Date07<={TodayUTC}" and in my extended ContentBySearchWebpart:

    dataProvider.QueryTemplate = currentQueryText.Replace("{TodayUTC}", SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today.ToUniversalTime()));

    This way I wouldn't be dependant on servers time zone and the query returned expected results. Weepee!

    Wednesday, June 19, 2013 1:58 PM