X-CGP-ClamAV-Result: CLEAN X-VirusScanner: Niversoft's CGPClamav Helper v1.22.2a (ClamAV engine v0.102.2) X-Junk-Score: 0 [] X-KAS-Score: 0 [] From: "Jesse Tayler" Received: from mail-qk1-f176.google.com ([209.85.222.176] verified) by selbstdenker.ag (CommuniGate Pro SMTP 6.3.7) with ESMTPS id 26733591 for webobjects-dev@wocommunity.org; Wed, 03 Nov 2021 17:36:01 +0100 Received-SPF: none receiver=post.selbstdenker.com; client-ip=209.85.222.176; envelope-from=jtayler@oeinc.com Received: by mail-qk1-f176.google.com with SMTP id p17so1785827qkj.0 for ; Wed, 03 Nov 2021 09:36:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=oeinc-com.20210112.gappssmtp.com; s=20210112; h=from:mime-version:subject:date:references:to:in-reply-to:message-id; bh=iIoQRAPeyc6DmX2co5G31Qh42z9VfprT5hB6U1dYWhY=; b=urDwDPvx5Hxh34QoGKQalSlfq8twVc/ObGx2npBAjZKfbJedT0KI7Y7wuXdYvnNeFU URBYvtxafkO4Xtz0dsiMfB0H7O9Dki1l7VrHZK3JQL0szBk2J1ZkScFotKun6umMXLrZ DlhFbC01fhLOUGGpuIFrnl9gaAsOERsOWhURUdFQ8Da0RF+OiEsuulBjFaD6I4AQGGE9 BUEZ2rcdrfizjdBx1pKfxmtBikqOtR1NUGX/W0+o5KoduLNPcetJZHTVUe47u09gwgq7 kLfq3LhDdY5kFzjOGAGMS9KpJ05XufIkU/xZIg8LYtY4iooh9B2x06aJtt5aRPUEjn1g 58lA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:mime-version:subject:date:references:to :in-reply-to:message-id; bh=iIoQRAPeyc6DmX2co5G31Qh42z9VfprT5hB6U1dYWhY=; b=qAuBfs/qai8EFBzK+x8Rz06uVt7YIQS2xit9N8/EDeEzRpDT0oF3TAb+og49rGnxEy GRS2olCviwXxLreEzwKxOYBvz6na7o6zzAycRHKgybZVnIKUHDVFUY/hCSVEO51xXcV3 mAckQN/s6qyLDlTVWRyD1s0OHiUH+rBlZAp9QI0uHRvNHD3YAqclZIoNnXcDcR0y5/8u p444ObbfswZclrD60cqI7c8BVQ/gOagJX2CCoUeGkf/xFClWOQIHTSPZp8DDvuP+DfbT gAdsByLjYlXKtgys8Ch7gEom14mS5lV9sBA95Ve8lvx52Rlm+dUx2zfnHJrB17w4Z+W1 SbCA== X-Gm-Message-State: AOAM531L090SwxvhoZwe/hxkXggWLzC35LrQUE9UWV+GT7JNFdllZgyP liD/edRr3VMS7BqFYTkip700LBAibannjg== X-Google-Smtp-Source: ABdhPJyHL7Se1hNtiigZWY75auOT1w7/zmNNy8CX153tgNkH3lCGL3sUXW8aDDRYiP/bn8kjBAuY5g== X-Received: by 2002:a37:2e81:: with SMTP id u123mr36236120qkh.156.1635957340099; Wed, 03 Nov 2021 09:35:40 -0700 (PDT) Return-Path: Received: from [192.168.1.16] ([69.169.5.176]) by smtp.gmail.com with ESMTPSA id bl40sm1883886qkb.38.2021.11.03.09.35.39 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 03 Nov 2021 09:35:39 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_044D00F1-05EF-4F7F-B3A7-1F4A61BD1C07" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.60.0.2.2\)) Subject: Re: [WO-DEV] How would you create such an EOQualifier representing Postgresql date_trunc(...)? Date: Wed, 3 Nov 2021 12:35:37 -0400 References: To: WebObjects & WOnder Development In-Reply-To: Message-Id: <099EBAAC-3ECD-43C2-B779-2C97E8450448@oeinc.com> X-Mailer: Apple Mail (2.3654.60.0.2.2) --Apple-Mail=_044D00F1-05EF-4F7F-B3A7-1F4A61BD1C07 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I=E2=80=99d bet you can call your function, but I=E2=80=99d guess = you=E2=80=99d be in raw rows I=E2=80=99m not sure I=E2=80=99m fairly certain that I create a date-range with a start at = 12:00 or whatever and end where I need, knowing if an event has ended, = knowing if an event is all-day I=E2=80=99m not certain about lowest level performance issues, but = Timestamps seem to lend themselves reasonably well to this after x but = before y query method, if I recall... > On Nov 3, 2021, at 12:21 PM, Markus Ruggiero (rucotec) = wrote: >=20 >=20 >> On 3 Nov 2021, at 14:25, Jesse Tayler > wrote: >>=20 >> I generally use ERXKeys and fancy methods called dot() and these good = ideas let you code into the compiler the structure of whatever query you = like to do. >>=20 >> I don=E2=80=99t see anything unusual about your qualifier? Want an = example? >=20 > I know about ERXKey and friends and use these often. The issue here is = that date_trunc() is a function that is executed on the db server. In my = case with the =E2=80=98day=E2=80=99 parameter it truncates the value to = a granularity of =E2=80=98day=E2=80=99 by stripping off every detail = that is smaller than a day. Thus the comparison selects all entries = where the CHECK_IN_TIME (which is a date/time field) is on or after the = REQUESTED date/time or any time on the same day. The trunc function = makes the comparison independent of anything hour/minute. >=20 > To do this with ERXKey etc I would have to select ALL objects and = filter in memory. This would kill my app as there are waaay to many = requests. >=20 >>=20 >>> On Nov 3, 2021, at 8:48 AM, Markus Ruggiero (rucotec) = > = wrote: >>>=20 >>> No one? >>> I can live with raw rows but would have been great if anyone knows >>>=20 >>> ---markus--- >>>=20 >>>> On 1 Nov 2021, at 12:54, Markus Ruggiero (rucotec) = > = wrote: >>>>=20 >>>> Need to find objects satisfying the following condition: >>>>=20 >>>> CHECK_IN_TIME must be at least on the same day or later than = REQUESTED. Both values are stored as NSTimestamp with a 15 minutes = precision. >>>>=20 >>>> In Postgres I would write >>>>=20 >>>> ...where date_trunc('day', CHECK_IN_TIME) >=3D date_trunc('day', = REQUESTED) >>>>=20 >>>> How can I create a qualifier for this? Is this possible at all or = do I have to resort to raw rows for SQL? >>>>=20 >>>> Thanks >>>> ---markus--- >>>>=20 >>>>=20 >>>>=20 >>>> Markus Ruggiero >>>>=20 >>>> rucotec GmbH web https://rucotec.ch = >>>> Steinenvorstadt 79 email markus.ruggiero@rucotec.ch = >>>> 4051 Basel / Switzerland mobile +41 79 508 4701 >>>>=20 >>>>=20 >>>>=20 >>>>=20 >>>>=20 >>>>=20 >>>>=20 >>>>=20 >>>=20 >>>=20 >>=20 >=20 >=20 --Apple-Mail=_044D00F1-05EF-4F7F-B3A7-1F4A61BD1C07 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 I=E2=80= =99d bet you can call your function, but I=E2=80=99d guess you=E2=80=99d = be in raw rows I=E2=80=99m not sure

I=E2=80=99m fairly certain that I = create a date-range with a start at 12:00 or whatever and end where I = need, knowing if an event has ended, knowing if an event is = all-day

I=E2=80=99= m not certain about lowest level performance issues, but Timestamps seem = to lend themselves reasonably well to this after x but before y query = method, if I recall...

On Nov 3, 2021, at 12:21 PM, = Markus Ruggiero (rucotec) <webobjects-dev@wocommunity.org> wrote:


On 3 Nov = 2021, at 14:25, Jesse Tayler <webobjects-dev@wocommunity.org> wrote:

I generally use = ERXKeys and fancy methods called dot() and these good ideas let you code = into the compiler the structure of whatever query you like to do.

I don=E2=80=99t see = anything unusual about your qualifier? Want an example?

I know about ERXKey and friends and use these often. = The issue here is that date_trunc() is a function that is executed on = the db server. In my case with the =E2=80=98day=E2=80=99 parameter it = truncates the value to a granularity of =E2=80=98day=E2=80=99 by = stripping off every detail that is smaller than a day. Thus the = comparison selects all entries where the CHECK_IN_TIME (which is a = date/time field) is on or after the REQUESTED date/time or any time on = the same day. The trunc function makes the comparison independent of = anything hour/minute.

To do this with ERXKey etc I would have to select ALL objects = and filter in memory. This would kill my app as there are waaay to many = requests.


On Nov = 3, 2021, at 8:48 AM, Markus Ruggiero (rucotec) <webobjects-dev@wocommunity.org> wrote:

No one?
I= can live with raw rows but would have been great if anyone = knows

---markus---

On 1 Nov = 2021, at 12:54, Markus Ruggiero (rucotec) <webobjects-dev@wocommunity.org> wrote:

Need to find objects = satisfying the following condition:

CHECK_IN_TIME must be at least on the = same day or later than REQUESTED. Both values are stored as NSTimestamp = with a 15 minutes precision.

In Postgres I would write

...where date_trunc('day', CHECK_IN_TIME) >=3D = date_trunc('day', REQUESTED)

How can I create a qualifier for this? = Is this possible at all or do I have to resort to raw rows for = SQL?

Thanks
---markus---



Markus = Ruggiero

rucotec GmbH              =           web https://rucotec.ch
Steinenvorstadt 79          =       email markus.ruggiero@rucotec.ch
4051 Basel / = Switzerland         mobile +41 79 508 4701














= --Apple-Mail=_044D00F1-05EF-4F7F-B3A7-1F4A61BD1C07--