Query on DATETIME for a date (the whole day) - Mailing list pgsql-novice

From Basil Bourque
Subject Query on DATETIME for a date (the whole day)
Date
Msg-id 88B9E9E1-FCE2-492A-8EB9-1DB1F21148E2@me.com
Whole thread Raw
Responses Re: Query on DATETIME for a date (the whole day)  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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
singledate? 

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
constructbeginning 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
NewYear'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

pgsql-novice by date:

Previous
From: Andy Halsall
Date:
Subject: Re: Functions returns to libpq with wrong column split
Next
From: Tom Lane
Date:
Subject: Re: Query on DATETIME for a date (the whole day)