pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuerying date_time for date only ?
5+ messages / 4 participants
[nested] [flat]
* Querying date_time for date only ?
@ 2005-12-20 12:59 Aarni Ruuhimäki <aarni@kymi.com>
2005-12-20 13:19 ` Re: Querying date_time for date only ? Michael Burke <michael@engtech.ca>
2005-12-22 16:01 ` Re: Querying date_time for date only ? Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Aarni Ruuhimäki @ 2005-12-20 12:59 UTC (permalink / raw)
To: pgsql-sql
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
--------------
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Querying date_time for date only ?
2005-12-20 12:59 Querying date_time for date only ? Aarni Ruuhimäki <aarni@kymi.com>
@ 2005-12-20 13:19 ` Michael Burke <michael@engtech.ca>
2005-12-20 13:33 ` Re: Querying date_time for date only ? Aarni Ruuhimäki <aarni@kymi.com>
2005-12-20 15:05 ` Re: Querying date_time for date only ? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 5+ messages in thread
From: Michael Burke @ 2005-12-20 13:19 UTC (permalink / raw)
To: pgsql-sql; aarni@kymi.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Querying date_time for date only ?
2005-12-20 12:59 Querying date_time for date only ? Aarni Ruuhimäki <aarni@kymi.com>
2005-12-20 13:19 ` Re: Querying date_time for date only ? Michael Burke <michael@engtech.ca>
@ 2005-12-20 13:33 ` Aarni Ruuhimäki <aarni@kymi.com>
1 sibling, 0 replies; 5+ messages in thread
From: Aarni Ruuhimäki @ 2005-12-20 13:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Querying date_time for date only ?
2005-12-20 12:59 Querying date_time for date only ? Aarni Ruuhimäki <aarni@kymi.com>
2005-12-20 13:19 ` Re: Querying date_time for date only ? Michael Burke <michael@engtech.ca>
@ 2005-12-20 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-12-20 15:05 UTC (permalink / raw)
To: Michael Burke <michael@engtech.ca>; +Cc: pgsql-sql; aarni@kymi.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Querying date_time for date only ?
2005-12-20 12:59 Querying date_time for date only ? Aarni Ruuhimäki <aarni@kymi.com>
@ 2005-12-22 16:01 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2005-12-22 16:01 UTC (permalink / raw)
To: aarni@kymi.com; +Cc: pgsql-sql
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
(1 row)
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, Pennsylvania 19073
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-12-22 16:01 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-20 12:59 Querying date_time for date only ? Aarni Ruuhimäki <aarni@kymi.com>
2005-12-20 13:19 ` Michael Burke <michael@engtech.ca>
2005-12-20 13:33 ` Aarni Ruuhimäki <aarni@kymi.com>
2005-12-20 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-22 16:01 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox