Thread: Query on DATETIME for a date (the whole day)

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

Basil Bourque <basil.list@me.com> writes:
> For a "timestamp with time zone" column, how do I find all rows where the value is within the beginning and ending of
asingle date? 

If you compare a date to a timestamp, the date is taken to mean midnight
of its day.  So you need something along the line of

    WHERE timestampcol BETWEEN dateval AND dateval+1

> 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.

Well, you're not being too clear about what you need here, but in what
I suggest above, "midnight" will be interpreted according to the current
"timezone" setting.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> For a "timestamp with time zone" column, how do I find all rows where the value is within the beginning and ending
ofa single date? 

> If you compare a date to a timestamp, the date is taken to mean midnight
> of its day.  So you need something along the line of

>     WHERE timestampcol BETWEEN dateval AND dateval+1

To be exact:

|       WHERE timestampcol >= dateval AND timestampcol < dateval+1

because otherwise the start of the next day is included in
the previous and therefore potentially counted twice.

Tim

On 04/11/11 15:02, Tim Landscheidt wrote:
> Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>
>>> For a "timestamp with time zone" column, how do I find all rows where the value is within the beginning and ending
ofa single date? 
>> If you compare a date to a timestamp, the date is taken to mean midnight
>> of its day.  So you need something along the line of
>>     WHERE timestampcol BETWEEN dateval AND dateval+1
> To be exact:
>
> |       WHERE timestampcol>= dateval AND timestampcol<  dateval+1
>
> because otherwise the start of the next day is included in
> the previous and therefore potentially counted twice.
>
> Tim
>
>
Hmm...

Most people don't realize that midnight is 12am, or 00:00:00, the very
first moment in the day!  :-)