Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..
169 views

Definition of "today" in date range search

We currently have a saved search where we specify the date range as "today" instead of an actual date.

If we run the search in the AM it brings up records that were actually created the previous working day. If we run the search late PM it brings up the correct results 

I have checked the system option on the client and our GMT offset is set to 10 hours which is for Sydney AU - AEST

It seems like the time is still based on GMT. Is it possible the WG servers are not on the incorrect time causing incorrect search results 

Running CM 9.3.1

Cheers

Andrew 

 

0 Likes
7 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Definition of "today" in date range search

I just activated the Activity log in my client and did a 'today' search.  In the Dataset tab I got the query below.  The 'today' clause was converted to a date range with a start time of 2020-05-19T14:00, which is midnight last night.

What do you see in your activity log?

SELECT uri FROM TSRECORD  WITH (NOLOCK)  WHERE ( regDateTime BETWEEN '20200519140000' AND '202005201359591' AND rcRecTypeUri IN ( 1,2,9000000000,9000000001,9000000002,9000000003,9000000004,9000000005,9000000006,9000000007,9000000008,9000000009,9000000010,9000000011,9000000012,9000000013,9000000014,9000000015,9000000016,9000000017,9000000018,9000000019,9000000020,9000000021,9000000022,9000000023,9000000024,9000000025,9000000026,9000000027,9000000028,9000000029,9000000031 ) AND rcDateRecycled IS NULL AND uri > 0 )

 


Blog | Samples | CM SDK Docs
**Any opinions expressed in this forum are my own personal opinion and should not be interpreted as an official statement on behalf of MicroFocus**
0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Re: Definition of "today" in date range search

Hi David 

This is what I get

like yours it was midnight last night

SELECT uri FROM TSRECORD WITH (NOLOCK) WHERE ( regDateTime BETWEEN '20200519140000' AND '202005201359591' AND rcRecTypeUri IN ( 3,8,9,10,11,12,13,14,15,17,18,23,24,25,30,33,34,36,39,40,42,53,60,62,64,67,71,72,89,92,95,96,97,98,100,587,589,1089,1090,1092,1093,1094,1095,1096,1099,1100,1598,1599,1600,1601,1602,1604,1605,1606,1607,1608,1609 ) AND uri > 0 )

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Definition of "today" in date range search

Do you have access to the WorkGroup server log to see what it says the SQL is?

Blog | Samples | CM SDK Docs
**Any opinions expressed in this forum are my own personal opinion and should not be interpreted as an official statement on behalf of MicroFocus**
0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Re: Definition of "today" in date range search

Hi David - will speak to our Infrastructure guys 

0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Re: Definition of "today" in date range search

Hi David

WG server times have been confirmed as correct. They have to go through a vendor to access SQL so still waiting for that 

This is from their query

SELECT uri FROM TSRECORD  WITH (NOLOCK)  WHERE ( regDateTime BETWEEN '20200526140000' AND '202005271359591' AND rcRecTypeUri IN ( 3,8,9,10,11,12,13,14,15,17,18,23,24,25,30,33,34,36,39,40,42,53,60,62,64,67,71,72,89,92,95,96,97,98,100,587,589,1089,1090,1092,1093,1094,1095,1096,1099,1100,1598,1599,1600,1601,1602,1604,1605,1606,1607,1608,1609 ) AND uri  > 0  )


I am more convinced that "today" means the past 24 hours and not "today" as in working day 

0 Likes
Highlighted
Trusted Contributor.. Trusted Contributor..
Trusted Contributor..

Re: Definition of "today" in date range search

Just to add -  if I create an additional date only field (not a date/time field) the search query appears to work correctly even though the search string is the same as the ones mentioned in this thread. So maybe a date only additional field is the way to go to generate the correct search results

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Definition of "today" in date range search

Dates are a fun place to be. The answer will be clear once you can see the actual data stored in the date column for the Record you are looking for.

Blog | Samples | CM SDK Docs
**Any opinions expressed in this forum are my own personal opinion and should not be interpreted as an official statement on behalf of MicroFocus**
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.