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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
