Some other postgres-friendly options from the IRC channel:
select([func.date(model.channel_events.c.stamp)], distinct=True)
or
select([cast(model.channel_events.c.stamp, Date)], distinct=True)
The latter should be portable anywhere, I think. Not sure about
the first beyond the 3 usual open source suspects.
Michael wrote:
>
> you can probably select on DISTINCT trunc("day", somedate)
>
> SA would do this like select([distinct(func.trunc("day",
> mytable.c.datecol))])
>
>
>
> On Sep 6, 2007, at 1:37 PM, Pedro Algarvio, aka, s0undt3ch wrote:
>
>>
>> How could one get only the unique dates from a datetime column,
>> disregarding the time part of the datetime object?
>>
>> I know I can do:
>>>>> s = model.sqla.select([model.channel_events.c.stamp],
>>>>> model.channel_events.c.channel_participation_id == 5)
>>>>> results = model.Session.execute(s).fetchall()
>>>>> for res in results:
>> ... print res
>> ...
>> (datetime.datetime(2007, 9, 4, 11, 44, 8, 199613,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8c9eb8c>),)
>> (datetime.datetime(2007, 9, 3, 19, 7, 51, 147560,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8c9ef8c>),)
>> (datetime.datetime(2007, 9, 4, 12, 5, 44, 654299,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8c9efcc>),)
>> (datetime.datetime(2007, 9, 4, 12, 7, 30, 279193,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8c9ee6c>),)
>> (datetime.datetime(2007, 9, 4, 12, 18, 48, 106636,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8c9ef2c>),)
>> (datetime.datetime(2007, 9, 4, 12, 20, 38, 63371,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8c9ec6c>),)
>> (datetime.datetime(2007, 9, 4, 12, 20, 40, 271526,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca30cc>),)
>> (datetime.datetime(2007, 9, 4, 12, 22, 55, 752780,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca304c>),)
>> (datetime.datetime(2007, 9, 4, 12, 57, 19, 88308,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca30ac>),)
>> (datetime.datetime(2007, 9, 4, 13, 26, 19, 226345,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca3bcc>),)
>> (datetime.datetime(2007, 9, 4, 10, 38, 13, 598636,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca3c2c>),)
>> (datetime.datetime(2007, 9, 4, 10, 47, 5, 961859,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca3c8c>),)
>> (datetime.datetime(2007, 9, 4, 10, 47, 48, 8467,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca3cec>),)
>> (datetime.datetime(2007, 9, 4, 11, 31, 55, 254280,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca3d4c>),)
>> (datetime.datetime(2007, 9, 4, 11, 41, 11, 196310,
>> tzinfo=<psycopg2.tz.FixedOffsetTimezone object at 0x8ca3dac>),)
>>>>> len(results)
>> 15
>>>>> len(set([date[0].date() for date in results]))
>> 2
>>>>>
>>
>> But I'm sure this can be done differently.
>>
>> If it makes a difference I'm using postgres as the backend.
>>
>> Best Regards,
>> --
>> Pedro Algarvio
>> __ ___ ___ _______ ___ __
>> | Y .' _| _ .-----.' _| |_ .-----.----.-----.
>> |. | | _| 1___| _ | _| _|__| _ | _| _ |
>> |. | |__| |____ |_____|__| |____|__|_____|__| |___ |
>> |: 1 | |: 1 | |_____|
>> |::.. . | |::.. . | ufs [AT] ufsoft [DOT] org
>> `-------' `-------' ufs [AT] sapo [DOT] pt
>>
>>
>> >
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
