| From: | Basil Bourque <basil(dot)list(at)me(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Query on DATETIME for a date (the whole day) |
| Date: | 2011-10-20 01:08:00 |
| Message-ID: | 88B9E9E1-FCE2-492A-8EB9-1DB1F21148E2@me.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
For a "timestamp with time zone" column, how do I find all rows where the value is within the beginning and ending of a single date?
I've studied:
http://www.postgresql.org/docs/current/static/datatype-datetime.html
http://www.postgresql.org/docs/current/static/functions-datetime.html
But I'm overwhelmed. I can think of several complicated ways involving tearing apart dates into string parts to construct beginning and ending times of day. Surely there must be a simpler way.
Something like this imaginary command "DATE_OF" where moment_of_birth_ is a "timestamp with time zone" column to find New Year's Day babies:
SELECT name_, moment_of_birth_
FROM baby_
WHERE DATE_OF(moment_of_birth_) = '2011-01-01';
Perhaps use Postgres' casting ability?
SELECT name_, moment_of_birth_
FROM baby_
WHERE moment_of_birth_::date = '2011-01-01'::date ;
What's the best/simplest/efficient way to do this?
And I'm concerned about local date time. I want to find by the user's local beginning and end of the day, not UTC.
--Basil Bourque
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2011-10-20 02:32:27 | Re: Query on DATETIME for a date (the whole day) |
| Previous Message | Rikard | 2011-10-20 00:24:42 | Can triggers update other tables? |