Обсуждение: Querying date_time for date only ?

Hello List,

I have a time stamp without time zone field, YYYY-MM-DD hh:mm:ss, in my table. 
I want to also find something just for a particular day regardless of the 
time. 

(Pg)SQL way to do this ?

TIA,

Aarni
-- 
--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core** linux system
--------------

On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote:
> Hello List,
>
> I have a time stamp without time zone field, YYYY-MM-DD hh:mm:ss, in my
> table. I want to also find something just for a particular day regardless
> of the time.
>
> (Pg)SQL way to do this ?

You can try,

SELECT field::date FROM mytable;

to select only the date part.  Likewise, you can use field::time if you want
to disregard the date.

> TIA,
>
> Aarni

HTH.
Mike.

--
Michael Burke
michael@engtech.ca

On Tuesday 20 December 2005 15:19, Michael Burke wrote:
> On December 20, 2005 08:59 am, Aarni Ruuhimäki wrote:
> > Hello List,
> >
> > I have a time stamp without time zone field, YYYY-MM-DD hh:mm:ss, in my
> > table. I want to also find something just for a particular day regardless
> > of the time.
> >
> > (Pg)SQL way to do this ?
>
> You can try,
>
> SELECT field::date FROM mytable;
>
> to select only the date part.  Likewise, you can use field::time if you
> want to disregard the date.
>

That's neat, thanks.

I was just getting there with

WHERE ... AND EXTRACT('day' FROM res_date_time) = $day
AND EXTRACT('month' FROM res_date_time) = $month
AND ...,

which may be useful elsewhere.

> > TIA,
> >
> > Aarni
>
> HTH.
> Mike.

Merry Christmas to everyone,

Aarni

Michael Burke <michael@engtech.ca> writes:
> On December 20, 2005 08:59 am, Aarni Ruuhim�ki wrote:
>> I have a time stamp without time zone field, YYYY-MM-DD hh:mm:ss, in my
>> table. I want to also find something just for a particular day regardless
>> of the time.

> You can try,
> SELECT field::date FROM mytable;

The date_trunc() function can also be useful for this sort of thing,
particularly if you need to round off to something finer or coarser
than days.
http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
        regards, tom lane

Aarni Ruuhim�ki wrote:
> Hello List,
> 
> I have a time stamp without time zone field, YYYY-MM-DD hh:mm:ss, in
> my table.  I want to also find something just for a particular day
> regardless of the time.
> 
> (Pg)SQL way to do this ?

Yes.  You can use date_trunc():
test=> select date_trunc('day', '2004-01-04 04:02:03'::timestamp);     date_trunc--------------------- 2004-01-04
00:00:00(1row)
 

so it would be date_trunc('day', col) = '2004-01-05'.  The problem with
this is that you cannot use an index unless you create an expression
index on the date_trunc() function call.  Another option is to do
something like:
WHERE col >= '2004-01-04' AND col < '2004-01-05'

If the date isn't a constant, you have to use date_trunc() on those, and
add one day to the second comparison:
  WHERE col >= date_trunc('day', col2) AND     col < date_trunc('day', col2) + '1 day';

-- Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073