Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

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 independent machine that does communicate with
the server over the LAN.

Cold database, 271k records, longint unique field:
DV approach 22 ticks
Sort approach 12 ticks, but the data should have been warmed by first test.

Run #2
DV approach 22 ticks, unchanged
Sort approach 5 ticks, not what I expected!
4D Min/Max functions 16 ticks

Cold index I believe, date field, 271, records
DV approach 3 ticks
Sort approach 3 ticks

Run #2 date field
DV 3 ticks
Sort 4 ticks

All this is being run on an old XP box over the LAN.

So unless someone sees a hole in my testing, it seems the sorting approach
is entirely satisfactory for both unique fields and fields with lots of
recurring values like dates.

Alan

On Thu, Oct 12, 2017 at 7:36 PM, Alan Tilson  wrote:

> 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 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 the record sorting approach makes me
>> suppose the test is not in C/S but standalone. I wouldn't be surprised by a
>> much greater difference in C/S.
>>
>> --
>> Arnaud de Montard
>>
>>
>> **
>> 4D Internet Users Group (4D iNUG)
>> FAQ:  http://lists.4d.com/faqnug.html
>> Archive:  http://lists.4d.com/archives.html
>> Options: http://lists.4d.com/mailman/options/4d_tech
>> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>> **
>
>
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

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 that
utilizes the LAN and post my results.
Thank you for your input!
Alan

On Thu, Oct 12, 2017 at 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 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 the record sorting approach makes me suppose
> the test is not in C/S but standalone. I wouldn't be surprised by a much
> greater difference in C/S.
>
> --
> Arnaud de Montard
>
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
>
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[John DeSoi via 4D_Tech]

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 seconds 
in single user mode. 4D has to load a lot of index pages. Does not seem to be a 
problem for cluster b-tree indexed fields.

John DeSoi, Ph.D.



> On Oct 12, 2017, at 3:37 PM, Alan Tilson via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> But the longint field has unique values so I ran the whole test with it,
> and found:
> 34 ticks for the DV approach
> 8 ticks for the sorting approach
> 23 ticks again for the Min/Max approach
> so the sorting approach seems better for fields that are unique or have
> mostly distinct values.
> 
> Also, I do have a Cluster B-tree index on the date field...
> 
> I should stop now...

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

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 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 the record sorting approach makes me suppose
> the test is not in C/S but standalone. I wouldn't be surprised by a much
> greater difference in C/S.
>
> --
> Arnaud de Montard
>
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Arnaud de Montard via 4D_Tech]

> 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 the record sorting approach makes me suppose the 
test is not in C/S but standalone. I wouldn't be surprised by a much greater 
difference in C/S.  

-- 
Arnaud de Montard 


**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Arnaud de Montard via 4D_Tech]

> 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 




**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Arnaud de Montard via 4D_Tech]

> 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, the main slowness factor is 
latency, much more than the volume. In other words, better to send the truck 
once instead of 100 times the moped. 


> I suspect this is getting a bit silly, but another idea is to have an
> method set to "Execute on server" that does the work and packs the results
> in an object and returns it. Yeah, that might work.

Needs to pass the selection to the EoS method:
LONGINT ARRAY FROM SELECTION(..;record_al)
method_EoS(->record_al;..)
then create the selection on server, get bigger/smaller, etc. 

-- 
Arnaud de Montard 



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

​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 functions I was wishing would work
with date fields...

But the longint field has unique values so I ran the whole test with it,
and found:
34 ticks for the DV approach
8 ticks for the sorting approach
23 ticks again for the Min/Max approach
so the sorting approach seems better for fields that are unique or have
mostly distinct values.

Also, I do have a Cluster B-tree index on the date field...

I should stop now...

Adios,
Alan

On Thu, Oct 12, 2017 at 4:14 PM, Alan Tilson  wrote:

> ​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 approach.​ And the
> sorting process ran in 7 ticks the first time while the s2a process took 99
> ticks with the index loaded. So the sorting approach definitely wins there.
>
> Running again with the data warmed up and changing the sorted S2A to
> unsorted DV, the results are a bit surprising. The sorting approach took 4
> ticks this time and the distinct values approach, drum r, took
> 1 tick to get Min & Max Dates from 240k records!
>
> So I am converting to the DV, no sorting approach!
>
> My code which allows me to extract Min, Max or both:
>
> $pField:=$1
> DISTINCT VALUES($pField->;arDates)
> If (Defined ($2))
>
> $pDate_min:=$2
>
> $pDate_min->:=arDates{1}
>
> End if
> If (Defined ($3))
>
> $pDate_max:=$3
>
> $pDate_max->:=arDates{Size of array(arDates)}
>
> End if
>
>
> The Defined method is just $0:=Not(Undefined($1))
>
> Thanks again, 8o)
> Alan
>
>
> On Thu, Oct 12, 2017 at 4:00 PM, David Adams via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
>
>> 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 (1 call), sort
>> array,
>> > read first and last items in array.
>> >
>>
>> Agreed. I'd want to know that there is any optimization needed past FIRST
>> RECORD and LAST RECORD before diving into anything else. And, past that,
>> 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 suspect this is getting a bit silly, but another idea is to have an
>> method set to "Execute on server" that does the work and packs the results
>> in an object and returns it. Yeah, that might work.
>> **
>> 4D Internet Users Group (4D iNUG)
>> FAQ:  http://lists.4d.com/faqnug.html
>> Archive:  http://lists.4d.com/archives.html
>> Options: http://lists.4d.com/mailman/options/4d_tech
>> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>> **
>>
>
>
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

​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 approach.​ And the
sorting process ran in 7 ticks the first time while the s2a process took 99
ticks with the index loaded. So the sorting approach definitely wins there.

Running again with the data warmed up and changing the sorted S2A to
unsorted DV, the results are a bit surprising. The sorting approach took 4
ticks this time and the distinct values approach, drum r, took
1 tick to get Min & Max Dates from 240k records!

So I am converting to the DV, no sorting approach!

My code which allows me to extract Min, Max or both:

$pField:=$1
DISTINCT VALUES($pField->;arDates)
If (Defined ($2))

$pDate_min:=$2

$pDate_min->:=arDates{1}

End if
If (Defined ($3))

$pDate_max:=$3

$pDate_max->:=arDates{Size of array(arDates)}

End if


The Defined method is just $0:=Not(Undefined($1))

Thanks again, 8o)
Alan


On Thu, Oct 12, 2017 at 4:00 PM, David Adams via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> 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 (1 call), sort array,
> > read first and last items in array.
> >
>
> Agreed. I'd want to know that there is any optimization needed past FIRST
> RECORD and LAST RECORD before diving into anything else. And, past that,
> 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 suspect this is getting a bit silly, but another idea is to have an
> method set to "Execute on server" that does the work and packs the results
> in an object and returns it. Yeah, that might work.
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
>
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[David Adams via 4D_Tech]

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 (1 call), sort array,
> read first and last items in array.
>

Agreed. I'd want to know that there is any optimization needed past FIRST
RECORD and LAST RECORD before diving into anything else. And, past that,
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 suspect this is getting a bit silly, but another idea is to have an
method set to "Execute on server" that does the work and packs the results
in an object and returns it. Yeah, that might work.
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Arnaud de Montard via 4D_Tech]

> 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 your min. Then use SELECTION RANGE ARRAY again on
> the last record to get the max.

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 (1 call), sort array, read first 
and last items in array. 

Another good reason to use timestamps encoded in long integer instead of date 
fields: 4 bytes instead of 8, stores date + hour instead of date only, faster 
sort, Min/Max works…

-- 
Arnaud de Montard 



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[David Adams via 4D_Tech]

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 TO ARRAY to get a single value from
the first record, that's your min. Then use SELECTION RANGE ARRAY again on
the last record to get the max.

I can't think of anything else better. You would have to test it out to see
if the double call is even worth the trouble.

On Wed, Oct 11, 2017 at 11:30 AM, 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 using Min/Max for numerical data?
>
> I have sorted the data and captured the first and last dates but this seems
> rather a lot of work to get this info.
>
> I believe that I have also used Selection To Array to capture the dates in
> the array and then sorted the array.
>
> Surely there is something I've overlooked?
>
> Currently using v13.6.
>
> Thanks,
> Alan Tilson
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Chip Scheide via 4D_Tech]

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 have sorted the data and captured the first and last dates but this seems
> rather a lot of work to get this info.
> 
> I believe that I have also used Selection To Array to capture the dates in
> the array and then sorted the array.
> 
> Surely there is something I've overlooked?
> 
> Currently using v13.6.
> 
> Thanks,
> Alan Tilson
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
---
Gas is for washing parts
Alcohol is for drinkin'
Nitromethane is for racing 
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Arnaud de Montard via 4D_Tech]

> 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 



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Keisuke Miyako via 4D_Tech]

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 selection (CHERCHER=QUERY), but a date array works the 
same.
French-only-feature???



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Arnaud de Montard via 4D_Tech]

> 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 there is a 
way to convert such a number but never tried. 

I don't understand, here I get some consistent results (4d v14r5 and v15r3, 
macOS): 

That screen copy is a selection (CHERCHER=QUERY), but a date array works the 
same. 
French-only-feature???

-- 
Arnaud de Montard 


**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Timothy Penner via 4D_Tech]

No, SQL does not work with the selection.

I missed that requirement when I read the original post but I see it now.

-Tim


Sent from my Verizon, Samsung Galaxy smartphone




 Original message 
From: Alan Tilson <alantil...@gmail.com>
Date: 10/11/17 6:05 PM (GMT-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" 
<4d_tech@lists.4d.com<mailto:4d_tech@lists.4d.com>> wrote:
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
INTO :$foundDate;
End SQL


-Tim



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  
mailto:4d_tech-unsubscr...@lists.4d.com<mailto:4d_tech-unsubscr...@lists.4d.com>
**

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

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)
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
INTO :$foundDate;
End SQL


-Tim



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Min/Max for Date Fields in 4D

[Timothy Penner via 4D_Tech]

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
INTO :$foundDate;
End SQL


-Tim



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Jeffrey Kain via 4D_Tech]

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, only taking a few ticks on
> 55k records!

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

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 selection.
Thanks to everyone!
Alan

$pField:=$1
$pTable:=Table(Table($pField))
LONGINT ARRAY FROM SELECTION($pTable->;arCurSelRecs)
ORDER BY($pTable->;$pField->;>)
If (Defined ($2))

$pDate_min:=$2
$pDate_min->:=$pField->

End if
If (Defined ($3))

$pDate_max:=$3
LAST RECORD($pTable->)
$pDate_max->:=$pField->

End if
CREATE SELECTION FROM ARRAY($pTable->;arCurSelRecs)

On Wed, Oct 11, 2017 at 6:39 PM, Alan Tilson  wrote:

> 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 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)
>> $min_d:=Min([aTable]aDate)
>>
>> Or what am I missing here?
>>
>> --
>> Arnaud de Montard
>>
>>
>>
>> **
>> 4D Internet Users Group (4D iNUG)
>> FAQ:  http://lists.4d.com/faqnug.html
>> Archive:  http://lists.4d.com/archives.html
>> Options: http://lists.4d.com/mailman/options/4d_tech
>> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>> **
>>
>
>
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

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 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)
> $min_d:=Min([aTable]aDate)
>
> Or what am I missing here?
>
> --
> Arnaud de Montard
>
>
>
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
>
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Arnaud de Montard via 4D_Tech]

> 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)
$min_d:=Min([aTable]aDate)

Or what am I missing here?

-- 
Arnaud de Montard 



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

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 selection from array

Is that something like what you are proposing?
Thanks!
Alan

On Wed, Oct 11, 2017 at 5:47 PM, David Ringsmuth 
wrote:

> 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 <4d_tech@lists.4d.com>
> *Sent: *Wednesday, October 11, 2017 4:30 PM
> *To: *4D iNug Technical <4d_tech@lists.4d.com>
> *Cc: *Alan Tilson 
> *Subject: *Min/Max for Date Fields in 4D
>
>
>
> 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 have sorted the data and captured the first and last dates but this seems
>
> rather a lot of work to get this info.
>
>
>
> I believe that I have also used Selection To Array to capture the dates in
>
> the array and then sorted the array.
>
>
>
> Surely there is something I've overlooked?
>
>
>
> Currently using v13.6.
>
>
>
> Thanks,
>
> Alan Tilson
>
> **
>
> 4D Internet Users Group (4D iNUG)
>
> FAQ:  http://lists.4d.com/faqnug.html
>
> Archive:  http://lists.4d.com/archives.html
>
> Options: http://lists.4d.com/mailman/options/4d_tech
>
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
>
> **
>
>
>
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: Min/Max for Date Fields in 4D

[David Ringsmuth via 4D_Tech]

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 4:30 PM
To: 4D iNug Technical
Cc: Alan Tilson
Subject: Min/Max for Date Fields in 4D

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 have sorted the data and captured the first and last dates but this seems
rather a lot of work to get this info.

I believe that I have also used Selection To Array to capture the dates in
the array and then sorted the array.

Surely there is something I've overlooked?

Currently using v13.6.

Thanks,
Alan Tilson
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Alan Tilson via 4D_Tech]

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 11, 2017 at 5:36 PM, Douglas von Roeder via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> 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 using Min/Max for numerical data?
> >
> > I have sorted the data and captured the first and last dates but this
> seems
> > rather a lot of work to get this info.
> >
> > I believe that I have also used Selection To Array to capture the dates
> in
> > the array and then sorted the array.
> >
> > Surely there is something I've overlooked?
> >
> > Currently using v13.6.
> >
> > Thanks,
> > Alan Tilson
> > **
> > 4D Internet Users Group (4D iNUG)
> > FAQ:  http://lists.4d.com/faqnug.html
> > Archive:  http://lists.4d.com/archives.html
> > Options: http://lists.4d.com/mailman/options/4d_tech
> > Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> > **
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Min/Max for Date Fields in 4D

[Douglas von Roeder via 4D_Tech]

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 using Min/Max for numerical data?
>
> I have sorted the data and captured the first and last dates but this seems
> rather a lot of work to get this info.
>
> I believe that I have also used Selection To Array to capture the dates in
> the array and then sorted the array.
>
> Surely there is something I've overlooked?
>
> Currently using v13.6.
>
> Thanks,
> Alan Tilson
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**