Обсуждение: query question

I have a table:

michael=# \d healthnotes
                                    Table "public.healthnotes"
  Column |           Type           |                          Modifiers
--------+--------------------------
+-------------------------------------------------------------
  posted | timestamp with time zone | not null default
('now'::text)::timestamp(6) with time zone
  notes  | text                     |
Indexes: healthnotes_pkey primary key btree (posted)

Often there are multiple entries per day. I want to display the day
once, with all the entries on that day.

So do I select * from notes and group by date, then write a nested
for-loop in php that ignores the extra timestamp items? Seems a little
inelegant. Or can I select distinct timestamps somehow after converting
them to dates?

Not sure how to go about this.

Michael Hanna <zen@hwcn.org> writes:

> I have a table:
>
> michael=# \d healthnotes
>                                     Table "public.healthnotes"
>   Column |           Type           |                          Modifiers
> --------+--------------------------
> +-------------------------------------------------------------
>   posted | timestamp with time zone | not null default
> ('now'::text)::timestamp(6) with time zone
>
>   notes  | text                     |
> Indexes: healthnotes_pkey primary key btree (posted)
>
> Often there are multiple entries per day. I want to display the day  once,
> with all the entries on that day.

Try casting the timestamp to date,

select * from healthnotes where cast(posted as date) = 'your date here';

Regards,
Manuel.

Michael,

> Often there are multiple entries per day. I want to display the day
> once, with all the entries on that day.

Look in the docs under "Functions and Operators".  There is a function
specifically to truncate dates.   If you write a wrapper function around it,
you can even make it indexable ...

--
Josh Berkus
Aglio Database Solutions
San Francisco