Re: [GENERAL] query by partial timestamp

2013-01-10 Thread Gavan Schneider
On Wednesday, January 9, 2013 at 04:42, Michael Nolan wrote: On 1/8/13, Gavan Schneider wrote: 2. SELECT ... WHERE '2011-01-01'::TIMESTAMP <= col_of_type_timestamp ANDcol_of_type_timestamp <= '2011-12-31'::TIMESTAMP; This won't quite work, because '2011-12-31'::

Re: [GENERAL] query by partial timestamp

2013-01-09 Thread hubert depesz lubaczewski
On Tue, Jan 08, 2013 at 04:19:59PM -0600, Kirk Wythers wrote: > I have a column of type TIMESTAMP, I'd like to query all records from 2011. > If it were text I could use a partial such as: > > WHERE > text ~ '2011' > > There must be a simple way to pull the year part out of a timestamp for

Re: [GENERAL] query by partial timestamp

2013-01-09 Thread Michael Nolan
It is probably not the most efficient, but I often use this syntax, which reads better. Select . where col_type_timestamp::date between '2011-01-01' and '2011-12-31' This will use a timestamp index. -- Mike Nolan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] query by partial timestamp

2013-01-09 Thread Michael Nolan
On 1/8/13, Gavan Schneider wrote: > 2. SELECT ... WHERE > '2011-01-01'::TIMESTAMP <= col_of_type_timestamp > ANDcol_of_type_timestamp <= > '2011-12-31'::TIMESTAMP; This won't quite work, because '2011-12-31'::TIMESTAMP is the same as 2011-12-31 00:00:0

Re: [GENERAL] query by partial timestamp

2013-01-09 Thread Steve Crawford
On 01/08/2013 06:15 PM, Kirk Wythers wrote: On Jan 8, 2013, at 6:48 PM, Tom Lane > wrote: The OP didn't suggest how many years his data covers, but it's quite possible that pulling a full year's worth of data will read enough of the table that there's no point in wo

Re: [GENERAL] query by partial timestamp

2013-01-09 Thread Nathan Clayton
On Jan 8, 2013 6:15 PM, "Kirk Wythers" wrote: > > > On Jan 8, 2013, at 6:48 PM, Tom Lane wrote: > >> The OP didn't >> suggest how many years his data covers, but it's quite possible that >> pulling a full year's worth of data will read enough of the table that >> there's no point in worrying abou

Re: [GENERAL] query by partial timestamp

2013-01-08 Thread Kirk Wythers
On Jan 8, 2013, at 6:48 PM, Tom Lane wrote: > The OP didn't > suggest how many years his data covers, but it's quite possible that > pulling a full year's worth of data will read enough of the table that > there's no point in worrying about whether an index could be used > anyway. There are onl

Re: [GENERAL] query by partial timestamp

2013-01-08 Thread Tom Lane
Gavan Schneider writes: > From my perspective there are at least three ways to attack > this problem: > (I have not tested these, so apologies for the stupid syntax errors.) > 1. SELECT ... WHERE 2011 = extract(YEAR FROM col_of_type_timestamp); > 2. SELECT ... WHERE > '2011-01-01'::

Re: [GENERAL] query by partial timestamp

2013-01-08 Thread Gavan Schneider
On Tuesday, January 8, 2013 at 09:26, Raymond O'Donnell wrote: On 08/01/2013 22:19, Kirk Wythers wrote: I have a column of type TIMESTAMP, I'd like to query all records from 2011. If it were text I could use a partial such as: WHERE text ~ '2011' There must be a simple way to pull the year pa

Re: [GENERAL] query by partial timestamp

2013-01-08 Thread Steve Crawford
On 01/08/2013 02:19 PM, Kirk Wythers wrote: I have a column of type TIMESTAMP, I'd like to query all records from 2011. If it were text I could use a partial such as: WHERE text ~ '2011' There must be a simple way to pull the year part out of a timestamp format. Thanks in advance.

Re: [GENERAL] query by partial timestamp

2013-01-08 Thread Rob Sargent
On 01/08/2013 03:39 PM, Raymond O'Donnell wrote: On 08/01/2013 22:26, Raymond O'Donnell wrote: On 08/01/2013 22:19, Kirk Wythers wrote: I have a column of type TIMESTAMP, I'd like to query all records from 2011. If it were text I could use a partial such as: WHERE text ~ '2011' There must be

Re: [GENERAL] query by partial timestamp

2013-01-08 Thread Raymond O'Donnell
On 08/01/2013 22:26, Raymond O'Donnell wrote: > On 08/01/2013 22:19, Kirk Wythers wrote: >> I have a column of type TIMESTAMP, I'd like to query all records from >> 2011. If it were text I could use a partial such as: >> >> WHERE text ~ '2011' >> >> There must be a simple way to pull the year part

Re: [GENERAL] query by partial timestamp

2013-01-08 Thread Raymond O'Donnell
On 08/01/2013 22:19, Kirk Wythers wrote: > I have a column of type TIMESTAMP, I'd like to query all records from > 2011. If it were text I could use a partial such as: > > WHERE text ~ '2011' > > There must be a simple way to pull the year part out of a timestamp > format. Thanks in advance. You

[GENERAL] query by partial timestamp

2013-01-08 Thread Kirk Wythers
I have a column of type TIMESTAMP, I'd like to query all records from 2011. If it were text I could use a partial such as: WHERE text ~ '2011' There must be a simple way to pull the year part out of a timestamp format. Thanks in advance. -- Sent via pgsql-general mailing list (pgsq