You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Frank Herrmann <fr...@modernizingmedicine.com> on 2016/06/08 20:07:44 UTC
Ordering by date on a timestamp field
Hello all,
Is there a way in Cayenne, when adding an ordering to a query, to order by
only the date portion of a timestamp (including time) field? Basically, the
timestamp includes hour, minutes, seconds, etc. I only want to sort on the
date portion (month, day, year), or any other such criteria.
The reason for this is, if we include other fields to sort by, we would
want the second sort field to group on the date, not the full timestamp.
Thanks,
-Frank
--
FRANK HERRMANN
SOFTWARE ENGINEER
T: 561-880-2998 x1563
E: frank.herrmann@modmed.com
[image: [ Modernizing Medicine ]] <http://www.modmed.com/>
[image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine> [image:
[ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/> [image:
[ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
<http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
<http://instagram.com/modernizing_medicine>
Re: Ordering by date on a timestamp field
Posted by Andrus Adamchik <an...@objectstyle.org>.
Unfortunately you can't do that with ObjectSelect. SQLSelect of course would allow to use MySQL functions. So that's probably the way to go here.
Andrus
> On Jun 8, 2016, at 11:07 PM, Frank Herrmann <fr...@modernizingmedicine.com> wrote:
>
> Hello all,
>
> Is there a way in Cayenne, when adding an ordering to a query, to order by
> only the date portion of a timestamp (including time) field? Basically, the
> timestamp includes hour, minutes, seconds, etc. I only want to sort on the
> date portion (month, day, year), or any other such criteria.
>
> The reason for this is, if we include other fields to sort by, we would
> want the second sort field to group on the date, not the full timestamp.
>
> Thanks,
>
> -Frank
>
> --
> FRANK HERRMANN
> SOFTWARE ENGINEER
>
> T: 561-880-2998 x1563
>
> E: frank.herrmann@modmed.com
>
>
>
> [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
> [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine> [image:
> [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/> [image:
> [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
> Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
> <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
> <http://instagram.com/modernizing_medicine>
Re: Ordering by date on a timestamp field
Posted by Andrus Adamchik <an...@objectstyle.org>.
Ah sorry, that's 4.0. SQLTemplate is a less convenient (but fully functional) analog in 3.1
> On Jun 9, 2016, at 4:27 PM, Frank Herrmann <fr...@modernizingmedicine.com> wrote:
>
> Thanks everyone.
>
> Andrus, is SQLSelect in Cayenne 3.1, or is it new in 4.0.
>
> Thanks,
>
> -Frank
>
> On Thu, Jun 9, 2016 at 6:12 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> Hi Frank,
>> we solved this in one of our apps by creating a separate date-only column
>> in the DB and mapping it to java.time.LocalTime in our model class. Then we
>> just modified the setter for the dateTime value to also set the date-only
>> value.
>>
>> Cheers,
>> - hugi
>>
>> // Hugi Thordarson
>> // http://www.loftfar.is/
>> // s. 895-6688
>>
>>
>>
>>> On 8. jún. 2016, at 20:49, Frank Herrmann <
>> frank.herrmann@modernizingmedicine.com> wrote:
>>>
>>> Views in mysql do not take advantage of indexes. We would like to avoid
>>> views and would prefer a Cayenne solution. Also, having a view with a
>>> formula is not very efficient, especially when dealing with hundreds of
>>> thousands of records.
>>>
>>> -Frank
>>>
>>> On Wed, Jun 8, 2016 at 4:42 PM, Adam Boyle <ab...@valsphere.com> wrote:
>>>
>>>> I can't speak to what Cayenne is able to do solely when using an
>>>> ObjectQuery, but from a database perspective it sounds like you want to
>> be
>>>> using a view that provides the date-only version of your datetime
>> column.
>>>> Then you could query it just like a table and do your sort on the
>> date-only
>>>> column. You can probably use the Query object type in your project
>> model to
>>>> achieve this if you aren't keen on adding SQL objects to your model
>> outside
>>>> of Cayenne Modeler.
>>>>
>>>>
>>>> -Adam
>>>>
>>>> ________________________________
>>>> From: Frank Herrmann <fr...@modernizingmedicine.com>
>>>> Sent: Wednesday, June 8, 2016 4:07:44 PM
>>>> To: user@cayenne.apache.org
>>>> Subject: Ordering by date on a timestamp field
>>>>
>>>> Hello all,
>>>>
>>>> Is there a way in Cayenne, when adding an ordering to a query, to order
>> by
>>>> only the date portion of a timestamp (including time) field? Basically,
>> the
>>>> timestamp includes hour, minutes, seconds, etc. I only want to sort on
>> the
>>>> date portion (month, day, year), or any other such criteria.
>>>>
>>>> The reason for this is, if we include other fields to sort by, we would
>>>> want the second sort field to group on the date, not the full timestamp.
>>>>
>>>> Thanks,
>>>>
>>>> -Frank
>>>>
>>>> --
>>>> FRANK HERRMANN
>>>> SOFTWARE ENGINEER
>>>>
>>>> T: 561-880-2998 x1563
>>>>
>>>> E: frank.herrmann@modmed.com
>>>>
>>>>
>>>>
>>>> [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
>>>> [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine>
>>>> [image:
>>>> [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/>
>>>> [image:
>>>> [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image:
>> [
>>>> Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
>>>> <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
>>>> <http://instagram.com/modernizing_medicine>
>>>>
>>>
>>>
>>>
>>> --
>>> FRANK HERRMANN
>>> SOFTWARE ENGINEER
>>>
>>> T: 561-880-2998 x1563
>>>
>>> E: frank.herrmann@modmed.com
>>>
>>>
>>>
>>> [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
>>> [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine>
>> [image:
>>> [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/>
>> [image:
>>> [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
>>> Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
>>> <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
>>> <http://instagram.com/modernizing_medicine>
>>
>>
>
>
> --
> FRANK HERRMANN
> SOFTWARE ENGINEER
>
> T: 561-880-2998 x1563
>
> E: frank.herrmann@modmed.com
>
>
>
> [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
> [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine> [image:
> [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/> [image:
> [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
> Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
> <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
> <http://instagram.com/modernizing_medicine>
Re: Ordering by date on a timestamp field
Posted by Frank Herrmann <fr...@modernizingmedicine.com>.
Thanks everyone.
Andrus, is SQLSelect in Cayenne 3.1, or is it new in 4.0.
Thanks,
-Frank
On Thu, Jun 9, 2016 at 6:12 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> Hi Frank,
> we solved this in one of our apps by creating a separate date-only column
> in the DB and mapping it to java.time.LocalTime in our model class. Then we
> just modified the setter for the dateTime value to also set the date-only
> value.
>
> Cheers,
> - hugi
>
> // Hugi Thordarson
> // http://www.loftfar.is/
> // s. 895-6688
>
>
>
> > On 8. jún. 2016, at 20:49, Frank Herrmann <
> frank.herrmann@modernizingmedicine.com> wrote:
> >
> > Views in mysql do not take advantage of indexes. We would like to avoid
> > views and would prefer a Cayenne solution. Also, having a view with a
> > formula is not very efficient, especially when dealing with hundreds of
> > thousands of records.
> >
> > -Frank
> >
> > On Wed, Jun 8, 2016 at 4:42 PM, Adam Boyle <ab...@valsphere.com> wrote:
> >
> >> I can't speak to what Cayenne is able to do solely when using an
> >> ObjectQuery, but from a database perspective it sounds like you want to
> be
> >> using a view that provides the date-only version of your datetime
> column.
> >> Then you could query it just like a table and do your sort on the
> date-only
> >> column. You can probably use the Query object type in your project
> model to
> >> achieve this if you aren't keen on adding SQL objects to your model
> outside
> >> of Cayenne Modeler.
> >>
> >>
> >> -Adam
> >>
> >> ________________________________
> >> From: Frank Herrmann <fr...@modernizingmedicine.com>
> >> Sent: Wednesday, June 8, 2016 4:07:44 PM
> >> To: user@cayenne.apache.org
> >> Subject: Ordering by date on a timestamp field
> >>
> >> Hello all,
> >>
> >> Is there a way in Cayenne, when adding an ordering to a query, to order
> by
> >> only the date portion of a timestamp (including time) field? Basically,
> the
> >> timestamp includes hour, minutes, seconds, etc. I only want to sort on
> the
> >> date portion (month, day, year), or any other such criteria.
> >>
> >> The reason for this is, if we include other fields to sort by, we would
> >> want the second sort field to group on the date, not the full timestamp.
> >>
> >> Thanks,
> >>
> >> -Frank
> >>
> >> --
> >> FRANK HERRMANN
> >> SOFTWARE ENGINEER
> >>
> >> T: 561-880-2998 x1563
> >>
> >> E: frank.herrmann@modmed.com
> >>
> >>
> >>
> >> [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
> >> [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine>
> >> [image:
> >> [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/>
> >> [image:
> >> [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image:
> [
> >> Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
> >> <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
> >> <http://instagram.com/modernizing_medicine>
> >>
> >
> >
> >
> > --
> > FRANK HERRMANN
> > SOFTWARE ENGINEER
> >
> > T: 561-880-2998 x1563
> >
> > E: frank.herrmann@modmed.com
> >
> >
> >
> > [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
> > [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine>
> [image:
> > [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/>
> [image:
> > [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
> > Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
> > <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
> > <http://instagram.com/modernizing_medicine>
>
>
--
FRANK HERRMANN
SOFTWARE ENGINEER
T: 561-880-2998 x1563
E: frank.herrmann@modmed.com
[image: [ Modernizing Medicine ]] <http://www.modmed.com/>
[image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine> [image:
[ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/> [image:
[ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
<http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
<http://instagram.com/modernizing_medicine>
Re: Ordering by date on a timestamp field
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Hi Frank,
we solved this in one of our apps by creating a separate date-only column in the DB and mapping it to java.time.LocalTime in our model class. Then we just modified the setter for the dateTime value to also set the date-only value.
Cheers,
- hugi
// Hugi Thordarson
// http://www.loftfar.is/
// s. 895-6688
> On 8. jún. 2016, at 20:49, Frank Herrmann <fr...@modernizingmedicine.com> wrote:
>
> Views in mysql do not take advantage of indexes. We would like to avoid
> views and would prefer a Cayenne solution. Also, having a view with a
> formula is not very efficient, especially when dealing with hundreds of
> thousands of records.
>
> -Frank
>
> On Wed, Jun 8, 2016 at 4:42 PM, Adam Boyle <ab...@valsphere.com> wrote:
>
>> I can't speak to what Cayenne is able to do solely when using an
>> ObjectQuery, but from a database perspective it sounds like you want to be
>> using a view that provides the date-only version of your datetime column.
>> Then you could query it just like a table and do your sort on the date-only
>> column. You can probably use the Query object type in your project model to
>> achieve this if you aren't keen on adding SQL objects to your model outside
>> of Cayenne Modeler.
>>
>>
>> -Adam
>>
>> ________________________________
>> From: Frank Herrmann <fr...@modernizingmedicine.com>
>> Sent: Wednesday, June 8, 2016 4:07:44 PM
>> To: user@cayenne.apache.org
>> Subject: Ordering by date on a timestamp field
>>
>> Hello all,
>>
>> Is there a way in Cayenne, when adding an ordering to a query, to order by
>> only the date portion of a timestamp (including time) field? Basically, the
>> timestamp includes hour, minutes, seconds, etc. I only want to sort on the
>> date portion (month, day, year), or any other such criteria.
>>
>> The reason for this is, if we include other fields to sort by, we would
>> want the second sort field to group on the date, not the full timestamp.
>>
>> Thanks,
>>
>> -Frank
>>
>> --
>> FRANK HERRMANN
>> SOFTWARE ENGINEER
>>
>> T: 561-880-2998 x1563
>>
>> E: frank.herrmann@modmed.com
>>
>>
>>
>> [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
>> [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine>
>> [image:
>> [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/>
>> [image:
>> [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
>> Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
>> <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
>> <http://instagram.com/modernizing_medicine>
>>
>
>
>
> --
> FRANK HERRMANN
> SOFTWARE ENGINEER
>
> T: 561-880-2998 x1563
>
> E: frank.herrmann@modmed.com
>
>
>
> [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
> [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine> [image:
> [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/> [image:
> [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
> Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
> <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
> <http://instagram.com/modernizing_medicine>
Re: Ordering by date on a timestamp field
Posted by Frank Herrmann <fr...@modernizingmedicine.com>.
Views in mysql do not take advantage of indexes. We would like to avoid
views and would prefer a Cayenne solution. Also, having a view with a
formula is not very efficient, especially when dealing with hundreds of
thousands of records.
-Frank
On Wed, Jun 8, 2016 at 4:42 PM, Adam Boyle <ab...@valsphere.com> wrote:
> I can't speak to what Cayenne is able to do solely when using an
> ObjectQuery, but from a database perspective it sounds like you want to be
> using a view that provides the date-only version of your datetime column.
> Then you could query it just like a table and do your sort on the date-only
> column. You can probably use the Query object type in your project model to
> achieve this if you aren't keen on adding SQL objects to your model outside
> of Cayenne Modeler.
>
>
> -Adam
>
> ________________________________
> From: Frank Herrmann <fr...@modernizingmedicine.com>
> Sent: Wednesday, June 8, 2016 4:07:44 PM
> To: user@cayenne.apache.org
> Subject: Ordering by date on a timestamp field
>
> Hello all,
>
> Is there a way in Cayenne, when adding an ordering to a query, to order by
> only the date portion of a timestamp (including time) field? Basically, the
> timestamp includes hour, minutes, seconds, etc. I only want to sort on the
> date portion (month, day, year), or any other such criteria.
>
> The reason for this is, if we include other fields to sort by, we would
> want the second sort field to group on the date, not the full timestamp.
>
> Thanks,
>
> -Frank
>
> --
> FRANK HERRMANN
> SOFTWARE ENGINEER
>
> T: 561-880-2998 x1563
>
> E: frank.herrmann@modmed.com
>
>
>
> [image: [ Modernizing Medicine ]] <http://www.modmed.com/>
> [image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine>
> [image:
> [ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/>
> [image:
> [ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
> Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
> <http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
> <http://instagram.com/modernizing_medicine>
>
--
FRANK HERRMANN
SOFTWARE ENGINEER
T: 561-880-2998 x1563
E: frank.herrmann@modmed.com
[image: [ Modernizing Medicine ]] <http://www.modmed.com/>
[image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine> [image:
[ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/> [image:
[ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
<http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
<http://instagram.com/modernizing_medicine>
Re: Ordering by date on a timestamp field
Posted by Adam Boyle <ab...@valsphere.com>.
I can't speak to what Cayenne is able to do solely when using an ObjectQuery, but from a database perspective it sounds like you want to be using a view that provides the date-only version of your datetime column. Then you could query it just like a table and do your sort on the date-only column. You can probably use the Query object type in your project model to achieve this if you aren't keen on adding SQL objects to your model outside of Cayenne Modeler.
-Adam
________________________________
From: Frank Herrmann <fr...@modernizingmedicine.com>
Sent: Wednesday, June 8, 2016 4:07:44 PM
To: user@cayenne.apache.org
Subject: Ordering by date on a timestamp field
Hello all,
Is there a way in Cayenne, when adding an ordering to a query, to order by
only the date portion of a timestamp (including time) field? Basically, the
timestamp includes hour, minutes, seconds, etc. I only want to sort on the
date portion (month, day, year), or any other such criteria.
The reason for this is, if we include other fields to sort by, we would
want the second sort field to group on the date, not the full timestamp.
Thanks,
-Frank
--
FRANK HERRMANN
SOFTWARE ENGINEER
T: 561-880-2998 x1563
E: frank.herrmann@modmed.com
[image: [ Modernizing Medicine ]] <http://www.modmed.com/>
[image: [ Facebook ]] <http://www.facebook.com/modernizingmedicine> [image:
[ LinkedIn ]] <http://www.linkedin.com/company/modernizing-medicine/> [image:
[ YouTube ]] <http://www.youtube.com/user/modernizingmedicine> [image: [
Twitter ]] <https://twitter.com/modmed_EMA> [image: [ Blog ]]
<http://www.modmed.com/BlogBeyondEMR> [image: [ Instagram ]]
<http://instagram.com/modernizing_medicine>