Re: Min/Max for Date Fields in 4D

Hello everyone, Arnaud's comments led me to realize that I was testing on a virtual machine that was part of the real machine that also hosts the virtual machine that run 4D server. So communications between the 2 are ultra fast, never needing to utilize the LAN. So today I tested this on an

Re: Min/Max for Date Fields in 4D

Arnaud, Your point finally caused me to realize this morning that my testing has been on a virtual machine hosted by the same real machine that hosts the virtual machine that runs 4D Server. So communications between my 4D Client and 4D Server are ultra fast. I will try to test this on a computer

Re: Min/Max for Date Fields in 4D

There are plenty more variables to consider that could make a huge difference in performance. If the table is large (say 20 million records) and the selection size is above a certain size (roughly 15,000 records), DISTINCT VALUES is very slow for b-tree indexed fields. I'm seeing times over 10

Re: Min/Max for Date Fields in 4D

It is CS actually! All my testing was CS. Alan sent from my Note 4 On Oct 12, 2017 6:40 PM, "Arnaud de Montard via 4D_Tech" < 4d_tech@lists.4d.com> wrote: > > > Le 12 oct. 2017 à 22:37, Alan Tilson via 4D_Tech <4d_tech@lists.4d.com> > a écrit : > > > > ​Another interesting point is that with a

Re: Min/Max for Date Fields in 4D

> Le 12 oct. 2017 à 22:37, Alan Tilson via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > ​Another interesting point is that with a larger selection of 446.5k > records and a warm database I got: > 3 ticks for DV approach > 6 ticks for the record sorting approach Guessing… Only twice more for

Re: Min/Max for Date Fields in 4D

> Le 12 oct. 2017 à 16:44, Chip Scheide via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > Distinct values ? > sort (resulting) array > get first and last element Hi Chip, best idea, DV is a must. But the sort is useless, the resulting array is sorted. -- Arnaud de Montard

Re: Min/Max for Date Fields in 4D

> Le 12 oct. 2017 à 22:00, David Adams via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > you would want to balance the cost of pulling more data over the network in > once call with pulling tiny bits of data over the network with multiple > calls. I'd say it doesn't matter in LAN. But in WAN,

Re: Min/Max for Date Fields in 4D

​Another interesting point is that with a larger selection of 446.5k records and a warm database I got: 3 ticks for DV approach 6 ticks for the record sorting approach and on a warmed up indexed longint field​ 23 ticks for Min then Max, the 4D functions So our DV approach is faster than the 4D

Re: Min/Max for Date Fields in 4D

​Thank you everyone for your continued input on my question! I was pretty satisfied with the sorting approach but distinct values might be an interesting approach! I just ran another test on 240k records first using the approach that sorts the records and then using the selection to array

Re: Min/Max for Date Fields in 4D

On Thu, Oct 12, 2017 at 6:57 AM, Arnaud de Montard via 4D_Tech < 4d_tech@lists.4d.com> wrote: > > It's a solution if the selection is huge (is it?). But sort selection > (I've always found 4d sort is slow), then 2 selection to array, that's 3 > server calls, at end. I'd prefer selection to array

Re: Min/Max for Date Fields in 4D

> Le 12 oct. 2017 à 18:10, David Adams via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > Alan, > > [...] > > * Since you already have a sorted selection, why no use that and SELECTION > RANGE TO ARRAY? So, use SELECTION RANGE TO ARRAY to get a single value from > the first record, that's

Re: Min/Max for Date Fields in 4D

Alan, I haven't followed this thread closely, so apologies if I say something silly. * Is it important to optimize this operation past the sensible solution you've already tried? * Since you already have a sorted selection, why no use that and SELECTION RANGE TO ARRAY? So, use SELECTION RANGE

Re: Min/Max for Date Fields in 4D

Distinct values ? sort (resulting) array get first and last element On Wed, 11 Oct 2017 17:30:01 -0400, Alan Tilson via 4D_Tech wrote: > Greetings everyone, > > Is there a way to get the earliest and latest dates from a selection of > records similar to using Min/Max for numerical data? > > I

Re: Min/Max for Date Fields in 4D

> Le 12 oct. 2017 à 13:07, Keisuke Miyako via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > for what it's worth, it works on Japanese version too. I added a comment in livedoc for doc team, time will tell… -- Arnaud de Montard

Re: Min/Max for Date Fields in 4D

for what it's worth, it works on Japanese version too. but the selection must not be empty, because in that case you would be attempting to assign undefined to date. 2017/10/12 18:12、Arnaud de Montard via 4D_Tech <4d_tech@lists.4d.com> のメール： That screen copy is a

Re: Min/Max for Date Fields in 4D

> Le 12 oct. 2017 à 00:39, Alan Tilson a écrit : > > Arnaud, > Max & Min don't work for date fields or date arrays it seems. I get values > like 5.67746e+17? Is there a way to translate this to a date? Date field is 8 bytes since v11 (most probably date+time), I suppose

Re: Min/Max for Date Fields in 4D

-08:00) To: 4D_Tech <4d_tech@lists.4d.com> Cc: Timothy Penner <tpen...@4d.com> Subject: RE: Min/Max for Date Fields in 4D Hello Timothy, Great idea, but can this work on the current selection? Thanks, Alan sent from my Note 4 On Oct 11, 2017 8:32 PM, "Timothy Penner via 4D_Tech"

RE: Min/Max for Date Fields in 4D

Hello Timothy, Great idea, but can this work on the current selection? Thanks, Alan sent from my Note 4 On Oct 11, 2017 8:32 PM, "Timothy Penner via 4D_Tech" <4d_tech@lists.4d.com> wrote: What about SQL? It works for me (only tested v16)... // find the Min C_DATE($blankDate)

RE: Min/Max for Date Fields in 4D

What about SQL? It works for me (only tested v16)... // find the Min C_DATE($blankDate) C_DATE($foundDate) Begin SQL SELECT MIN(DateField) FROM SomeTable WHERE DateField <> :$blankDate INTO :$foundDate; End SQL // find the MAX C_DATE($foundDate) Begin SQL SELECT MAX(DateField) FROM SomeTable

Re: Min/Max for Date Fields in 4D

It probably depends on what the server is doing, and the load it’s under, etc.. > On Oct 11, 2017, at 6:55 PM, Alan Tilson via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > It seems that sorting the table is faster than using an array, even after > returning the selection to it's original order,

Re: Min/Max for Date Fields in 4D

Hello everyone, It seems that sorting the table is faster than using an array, even after returning the selection to it's original order, only taking a few ticks on 55k records! Below is my code should anyone else come across this. I tried it Execute on Server but the method didn't see the current

Re: Min/Max for Date Fields in 4D

Arnaud, Max & Min don't work for date fields or date arrays it seems. I get values like 5.67746e+17? Is there a way to translate this to a date? Thanks, Alan On Wed, Oct 11, 2017 at 6:36 PM, Arnaud de Montard via 4D_Tech < 4d_tech@lists.4d.com> wrote: > > > Le 11 oct. 2017 à 23:30, Alan Tilson

Re: Min/Max for Date Fields in 4D

> Le 11 oct. 2017 à 23:30, Alan Tilson via 4D_Tech <4d_tech@lists.4d.com> a > écrit : > > Greetings everyone, > > Is there a way to get the earliest and latest dates from a selection of > records similar to using Min/Max for numerical data? $max_d:=Max([aTable]aDate)

Re: Min/Max for Date Fields in 4D

Hello David, I'd rather not sort the selection since it's generally already sorted. But I could do this, although it seems like a lot of overhead compared to the min/max functions: Longint array from selection order by(date field;>) $Date_min:=date field Last record $Date_mix:=date field create

RE: Min/Max for Date Fields in 4D

Alan, Sort the selection by the Date field, take the first Date, and then goto selected record (table;records in selection(table)), and get that Date. This avoids loading much, except the date field sort. Hth! David Ringsmuth From: Alan Tilson via 4D_Tech Sent: Wednesday, October 11, 2017

Re: Min/Max for Date Fields in 4D

Douglas, Thank you for responding. I believe Scan Index works on all the records in a Table, not on the Current Selection? What I'm looking for is a way to easily extract the earliest and latest dates contained in a field in a selection of records. Any other ideas? Thanks again, Alan On Wed, Oct

Re: Min/Max for Date Fields in 4D

Alan: If the field's indexed, how about Scan index? -- Douglas von Roeder 949-336-2902 On Wed, Oct 11, 2017 at 2:30 PM, Alan Tilson via 4D_Tech < 4d_tech@lists.4d.com> wrote: > Greetings everyone, > > Is there a way to get the earliest and latest dates from a selection of > records similar to