X-CGP-ClamAV-Result: CLEAN X-VirusScanner: Niversoft's CGPClamav Helper v1.22.2a (ClamAV engine v0.102.2) From: "Markus Ruggiero (rucotec)" Received: from miniserver.rucotec.ch ([213.189.151.242] verified) by post.selbstdenker.com (CommuniGate Pro SMTP 6.3.7) with ESMTPS id 26733498 for webobjects-dev@wocommunity.org; Wed, 03 Nov 2021 17:22:05 +0100 Received-SPF: none receiver=post.selbstdenker.com; client-ip=213.189.151.242; envelope-from=markus.ruggiero@rucotec.ch Received: from localhost (localhost [127.0.0.1]) by miniserver.rucotec.ch (Postfix) with ESMTP id 7EB983162CF6 for ; Wed, 3 Nov 2021 17:21:43 +0100 (CET) Received: from miniserver.rucotec.ch ([127.0.0.1]) by localhost (miniserver.rucotec.ch [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rhbfRhPegK4p for ; Wed, 3 Nov 2021 17:21:42 +0100 (CET) Received: from smtpclient.apple (unknown [192.168.56.47]) by miniserver.rucotec.ch (Postfix) with ESMTPSA id 260AF3162CE4 for ; Wed, 3 Nov 2021 17:21:42 +0100 (CET) Content-Type: multipart/signed; boundary="Apple-Mail=_E5314479-DCDA-4BCB-AD54-96F4AF195DC3"; protocol="application/pkcs7-signature"; micalg=sha-256 Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\)) Subject: Re: [WO-DEV] How would you create such an EOQualifier representing Postgresql date_trunc(...)? Date: Wed, 3 Nov 2021 17:21:41 +0100 References: To: WebObjects & WOnder Development In-Reply-To: Message-Id: <4E446B61-5396-42D4-BEBB-564968A829CF@rucotec.ch> X-Mailer: Apple Mail (2.3654.120.0.1.13) --Apple-Mail=_E5314479-DCDA-4BCB-AD54-96F4AF195DC3 Content-Type: multipart/alternative; boundary="Apple-Mail=_6D9DB756-7420-4B9D-BB0B-54E09E0215F9" --Apple-Mail=_6D9DB756-7420-4B9D-BB0B-54E09E0215F9 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > 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? 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. >=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 --Apple-Mail=_6D9DB756-7420-4B9D-BB0B-54E09E0215F9 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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=_6D9DB756-7420-4B9D-BB0B-54E09E0215F9-- --Apple-Mail=_E5314479-DCDA-4BCB-AD54-96F4AF195DC3 Content-Disposition: attachment; filename=smime.p7s Content-Type: application/pkcs7-signature; name=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExDzANBglghkgBZQMEAgEFADCABgkqhkiG9w0BBwEAAKCCCzkw ggUhMIIECaADAgECAhBDXz2PBS4rcSTMoUCPbeA+MA0GCSqGSIb3DQEBCwUAMIGWMQswCQYDVQQG EwJHQjEbMBkGA1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRgwFgYD VQQKEw9TZWN0aWdvIExpbWl0ZWQxPjA8BgNVBAMTNVNlY3RpZ28gUlNBIENsaWVudCBBdXRoZW50 aWNhdGlvbiBhbmQgU2VjdXJlIEVtYWlsIENBMB4XDTE5MDUyMjAwMDAwMFoXDTIyMDUyMTIzNTk1 OVowKzEpMCcGCSqGSIb3DQEJARYabWFya3VzLnJ1Z2dpZXJvQHJ1Y290ZWMuY2gwggEiMA0GCSqG SIb3DQEBAQUAA4IBDwAwggEKAoIBAQDIAEK8S00IWrMmIpBkw5CcIS9RfaNGWyJOxskmtuYoHWE4 +QYfOO5tlWt4O5F6bTYsRWp1PpLirrdLhQoYIFp0P5Mi0nsBjNVP0zR0jNpDZreNcLcP7wmuIkUY C0fzxFgnRieFFgaXFm5yf46rqAJMVry/uR/KqwvY1d2F2gOb4DmntPp7TJtDsVyWQDtB82Uep+EO 9j71phQuMUb5TyA4aOdSb6UfCev1RgEw8vXrTdf+1rLzRZvIR1syfsqcLVmIO9WSl9mTH7IlZQhx SmTLqSTvTLssRGprVK8dhJl0nDvFuKknUGNxwCwON1ojJdZPgUJi1K+VvkCqDlI61czNAgMBAAGj ggHTMIIBzzAfBgNVHSMEGDAWgBQJwPL8C9qU21/+K9+omULPyeCtADAdBgNVHQ4EFgQUFRQz2DQf fM2xz9tI/Gy05p6XXc0wDgYDVR0PAQH/BAQDAgWgMAwGA1UdEwEB/wQCMAAwHQYDVR0lBBYwFAYI KwYBBQUHAwQGCCsGAQUFBwMCMEAGA1UdIAQ5MDcwNQYMKwYBBAGyMQECAQEBMCUwIwYIKwYBBQUH AgEWF2h0dHBzOi8vc2VjdGlnby5jb20vQ1BTMFoGA1UdHwRTMFEwT6BNoEuGSWh0dHA6Ly9jcmwu c2VjdGlnby5jb20vU2VjdGlnb1JTQUNsaWVudEF1dGhlbnRpY2F0aW9uYW5kU2VjdXJlRW1haWxD QS5jcmwwgYoGCCsGAQUFBwEBBH4wfDBVBggrBgEFBQcwAoZJaHR0cDovL2NydC5zZWN0aWdvLmNv bS9TZWN0aWdvUlNBQ2xpZW50QXV0aGVudGljYXRpb25hbmRTZWN1cmVFbWFpbENBLmNydDAjBggr BgEFBQcwAYYXaHR0cDovL29jc3Auc2VjdGlnby5jb20wJQYDVR0RBB4wHIEabWFya3VzLnJ1Z2dp ZXJvQHJ1Y290ZWMuY2gwDQYJKoZIhvcNAQELBQADggEBACeso6Iombd/K2MXqk+u5cFNAi3kNRv5 t1WMA2YodqQxz/i/H9OxagG8Ukk5IcHgrikpy0dlOgJ9nOGEWJYrzZbLWZCMd7I8NQpT28vnkgKM 51tzAARg7mHu+SPKwvnDfYB8CFNSQ+Wlkq8wJHL9ALuTeGCRdnRmNtit8o/sRV25KoH6+0U2k08U TO5J+bzVendvcfygdf3bPp3+imRIlXJihwb2fg6OOMqVCnUFHKrdJULZ+SWSWNclriu5GbM1pKql d5dIrApf9M9b9XbY89XgHdFHA5bx7taL1Ie1msC23llmUVSYTHjNOqNrprgfKXvao1taAQvpDhzx BQXzO6YwggYQMIID+KADAgECAhBNlCwQ1DvglAnFgS06KwZPMA0GCSqGSIb3DQEBDAUAMIGIMQsw CQYDVQQGEwJVUzETMBEGA1UECBMKTmV3IEplcnNleTEUMBIGA1UEBxMLSmVyc2V5IENpdHkxHjAc BgNVBAoTFVRoZSBVU0VSVFJVU1QgTmV0d29yazEuMCwGA1UEAxMlVVNFUlRydXN0IFJTQSBDZXJ0 aWZpY2F0aW9uIEF1dGhvcml0eTAeFw0xODExMDIwMDAwMDBaFw0zMDEyMzEyMzU5NTlaMIGWMQsw CQYDVQQGEwJHQjEbMBkGA1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3Jk MRgwFgYDVQQKEw9TZWN0aWdvIExpbWl0ZWQxPjA8BgNVBAMTNVNlY3RpZ28gUlNBIENsaWVudCBB dXRoZW50aWNhdGlvbiBhbmQgU2VjdXJlIEVtYWlsIENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A MIIBCgKCAQEAyjztlApB/975Rrno1jvm2pK/KxBOqhq8gr2+JhwpKirSzZxQgT9tlC7zl6hn1fXj So5MqXUfItMltrMaXqcESJuK8dtK56NCSrq4iDKaKq9NxOXFmqXX2zN8HHGjQ2b2Xv0v1L5Nk1MQ PKA19xeWQcpGEGFUUd0kN+oHox+L9aV1rjfNiCj3bJk6kJaOPabPi2503nn/ITX5e8WfPnGw4VuZ 79Khj1YBrf24k5Ee1sLTHsLtpiK9OjG4iQRBdq6Z/TlVx/hGAez5h36bBJMxqdHLpdwIUkTqT8se 3ed0PewDch/8kHPo5fZl5u1B0ecpq/sDN/5sCG52Ds+QU5O5EwIDAQABo4IBZDCCAWAwHwYDVR0j BBgwFoAUU3m/WqorSs9UgOHYm8Cd8rIDZsswHQYDVR0OBBYEFAnA8vwL2pTbX/4r36iZQs/J4K0A MA4GA1UdDwEB/wQEAwIBhjASBgNVHRMBAf8ECDAGAQH/AgEAMB0GA1UdJQQWMBQGCCsGAQUFBwMC BggrBgEFBQcDBDARBgNVHSAECjAIMAYGBFUdIAAwUAYDVR0fBEkwRzBFoEOgQYY/aHR0cDovL2Ny bC51c2VydHJ1c3QuY29tL1VTRVJUcnVzdFJTQUNlcnRpZmljYXRpb25BdXRob3JpdHkuY3JsMHYG CCsGAQUFBwEBBGowaDA/BggrBgEFBQcwAoYzaHR0cDovL2NydC51c2VydHJ1c3QuY29tL1VTRVJU cnVzdFJTQUFkZFRydXN0Q0EuY3J0MCUGCCsGAQUFBzABhhlodHRwOi8vb2NzcC51c2VydHJ1c3Qu Y29tMA0GCSqGSIb3DQEBDAUAA4ICAQBBRHUAqznCFfXejpVtMnFojADdF9d6HBA4kMjjsb0XMZHz tuOCtKF+xswhh2GqkW5JQrM8zVlU+A2VP72Ky2nlRA1GwmIPgou74TZ/XTarHG8zdMSgaDrkVYzz 1g3nIVO9IHk96VwsacIvBF8JfqIs+8aWH2PfSUrNxP6Ys7U0sZYx4rXD6+cqFq/ZW5BUfClN/rhk 2ddQXyn7kkmka2RQb9d90nmNHdgKrwfQ49mQ2hWQNDkJJIXwKjYA6VUR/fZUFeCUisdDe/0ABLTI +jheXUV1eoYV7lNwNBKpeHdNuO6Aacb533JlfeUHxvBz9OfYWUiXu09sMAviM11Q0DuMZ5760CdO 2VnpsXP4KxaYIhvqPqUMWqRdWyn7crItNkZeroXaecG03i3mM7dkiPaCkgocBg0EBYsbZDZ8bsG3 a08LwEsL1Ygz3SBsyECa0waq4hOf/Z85F2w2ZpXfP+w8q4ifwO90SGZZV+HR/Jh6rEaVPDRF/CEG VqR1hiuQOZ1YL5ezMTX0ZSLwrymUE0pwi/KDaiYB15uswgeIAcA6JzPFf9pLkAFFWs1QNyN++niF hsM47qodx/PL+5jR87myx5uYdBEQkkDc+lKB1Wct6ucXqm2EmsaQ0M95QjTmy+rDWjkDYdw3Ms6m SWE3Bn7i5ZgtwCLXgAIe5W8mybM2JzGCA8QwggPAAgEBMIGrMIGWMQswCQYDVQQGEwJHQjEbMBkG A1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAwDgYDVQQHEwdTYWxmb3JkMRgwFgYDVQQKEw9TZWN0 aWdvIExpbWl0ZWQxPjA8BgNVBAMTNVNlY3RpZ28gUlNBIENsaWVudCBBdXRoZW50aWNhdGlvbiBh bmQgU2VjdXJlIEVtYWlsIENBAhBDXz2PBS4rcSTMoUCPbeA+MA0GCWCGSAFlAwQCAQUAoIIB6TAY BgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0yMTExMDMxNjIxNDFaMC8G CSqGSIb3DQEJBDEiBCAICvWublUBA4fHvkmiWs+6QXObXjAOMaP2Jv4Wv3i1lDCBvAYJKwYBBAGC NxAEMYGuMIGrMIGWMQswCQYDVQQGEwJHQjEbMBkGA1UECBMSR3JlYXRlciBNYW5jaGVzdGVyMRAw DgYDVQQHEwdTYWxmb3JkMRgwFgYDVQQKEw9TZWN0aWdvIExpbWl0ZWQxPjA8BgNVBAMTNVNlY3Rp Z28gUlNBIENsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgU2VjdXJlIEVtYWlsIENBAhBDXz2PBS4r cSTMoUCPbeA+MIG+BgsqhkiG9w0BCRACCzGBrqCBqzCBljELMAkGA1UEBhMCR0IxGzAZBgNVBAgT EkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMHU2FsZm9yZDEYMBYGA1UEChMPU2VjdGlnbyBM aW1pdGVkMT4wPAYDVQQDEzVTZWN0aWdvIFJTQSBDbGllbnQgQXV0aGVudGljYXRpb24gYW5kIFNl Y3VyZSBFbWFpbCBDQQIQQ189jwUuK3EkzKFAj23gPjANBgkqhkiG9w0BAQsFAASCAQAC4vHnjXvh 5iolWRugdnH9lekoKzD2qu8QQQbR4Uf0SfV+ip3JTL3TUHHW4MHQIunrztR8ldjeokU3ZCORokZ4 lGEci55HFan/T9XSnr3tf4GWPer3WHwimntiuYZETJlwlAm8KhOlsGYrBiH1hgiFksw7cYjj3chr xSM9aL8t92AUIcMSL7PY/4HE9GRBlUpHPcCG8F5xCZIEVEnJhVCEZD0/TxE+h5QXgE7+kjXsgEHF zSlDDAYfUL4mFVA40sPnqnLypBqCCFaiFsHxWQ8EOnyWLOXJ0gMWqqSEZKfsuwxx2x3hRTTEA59b t9IF6Wj+tVoV6Tmndawc8NfMsfBkAAAAAAAA --Apple-Mail=_E5314479-DCDA-4BCB-AD54-96F4AF195DC3--