[SQL] First day of month, last day of month
Hi all, I want to select data from a table according to a date column in my table. I want to select all the rows which have date in the current month. So is there a way to get from the actual date the first day and the last day of the month or is there a better method to retrieve all the rows with dates in the current month. Thanks to all
Re: [SQL] First day of month, last day of month
Nacef LABIDI wrote: is there a better method to retrieve all the rows with dates in the current month. select * from mytable where extract(month from mydate) = extract(month from now()) and extract(year from mydate) = extract(year from now()); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
select * from mytable where date_trunc('month', mydate) = date_trunc('month',
now());
>>> Frank Bax <[EMAIL PROTECTED]> 2008-04-24 15:06 >>>
Nacef LABIDI wrote:
> is there a better method to retrieve all
> the rows with dates in the current month.
select * from mytable where extract(month from mydate) = extract(month
from now()) and extract(year from mydate) = extract(year from now());
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
am Thu, dem 24.04.2008, um 14:59:47 +0200 mailte Nacef LABIDI folgendes:
> Hi all,
>
> I want to select data from a table according to a date column in my table. I
> want to select all the rows which have date in the current month. So is there
> a
> way to get from the actual date the first day and the last day of the month or
test=*# select date_trunc('month', current_date), date_trunc('month',
current_date)+'1month'::interval-'1day'::interval;
date_trunc |?column?
+
2008-04-01 00:00:00+02 | 2008-04-30 00:00:00+02
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
Frank Bax wrote:
Nacef LABIDI wrote:
is there a better method to retrieve all the rows with dates in the
current month.
select * from mytable where extract(month from mydate) = extract(month
from now()) and extract(year from mydate) = extract(year from now());
Sorry; I was not thinking clearly - date_trunc is better for this:
select * from mytable where date_trunc('month',mydate) =
date_trunc('month',now());
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How I can check a substring is a valid number in postgresql ?
Dear All, How I can check a substring is a valid number in postgresql ? example: I have a query select max(substring(code,2,length(code))::INT) from emp where substring(code,0,2) = 'A'; code A0001 A0002 ABC005 S0002 This query fails because of ABC002, how I can avoid this error , if there is a any way to check substring(code,1,length(code)) is a number ,then i can correct the query by put it in where condition. I want to get the result as 2(ie want to avoid checking ABC005 is there is any built in function to check the substring is a number in postgresql? Iam using postgres 8.1.8 pls help me thanks in advance: Anoop
Re: [SQL] How I can check a substring is a valid number in postgresql ?
On Thu, 24 Apr 2008, "Anoop G" <[EMAIL PROTECTED]> writes: > How I can check a substring is a valid number in postgresql ? SELECT col ~ '[0-9]+'; Regards. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
Frank Bax wrote:
Frank Bax wrote:
Nacef LABIDI wrote:
is there a better method to retrieve all the rows with dates in the
current month.
select * from mytable where extract(month from mydate) = extract(month
from now()) and extract(year from mydate) = extract(year from now());
Sorry; I was not thinking clearly - date_trunc is better for this:
select * from mytable where date_trunc('month',mydate) =
date_trunc('month',now());
I have some code that uses extract() for this sort of thing. Would you
mind explaining how date_trunc() is better for this?
Most of my extract() results end up in drop-down boxes in HTML.
Thanks.
Colin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
Don't know whether it's better, but it's shorter.
With extract you have to make the extraction for both year and month (unless
cases where either one doesn't matter)
With date_trunc('month', ...) you throw away anything smaller than month in one
step.
I suppose having to call the function date_trunc twice and extract 4 times in
the given example could make
the date_trunc version slightly faster.
Just wondering how many times you would have to do it before noticing the
"speedup".
>>> Colin Wetherbee <[EMAIL PROTECTED]> 2008-04-24 16:15 >>>
Frank Bax wrote:
> Frank Bax wrote:
>> Nacef LABIDI wrote:
>>> is there a better method to retrieve all the rows with dates in the
>>> current month.
>>
>> select * from mytable where extract(month from mydate) = extract(month
>> from now()) and extract(year from mydate) = extract(year from now());
>
> Sorry; I was not thinking clearly - date_trunc is better for this:
>
> select * from mytable where date_trunc('month',mydate) =
> date_trunc('month',now());
I have some code that uses extract() for this sort of thing. Would you
mind explaining how date_trunc() is better for this?
Most of my extract() results end up in drop-down boxes in HTML.
Thanks.
Colin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
On Apr 24, 2008, at 9:15 AM, Colin Wetherbee wrote:
Frank Bax wrote:
Frank Bax wrote:
Nacef LABIDI wrote:
is there a better method to retrieve all the rows with dates in
the current month.
select * from mytable where extract(month from mydate) =
extract(month from now()) and extract(year from mydate) =
extract(year from now());
Sorry; I was not thinking clearly - date_trunc is better for this:
select * from mytable where date_trunc('month',mydate) =
date_trunc('month',now());
I have some code that uses extract() for this sort of thing. Would
you mind explaining how date_trunc() is better for this?
Most of my extract() results end up in drop-down boxes in
HTML.
extract will pull specific date unit value out of a given date/
timestamp/interval. date_trunc will "round" a given date/timestamp
down to the given unit.
extract(month from now()) -> 4
date_trunc('month', now()) -> 2008-04-01 00:00:00-05
I typically find date_trunc much more useful but I may just think that
because I've been writing partitioning code a lot lately.
Erik Jones
DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
Actually I want to select all rows whith dates between first day of the
month 00:00:00 and last date of the month 23:59:59
On Thu, Apr 24, 2008 at 4:15 PM, Colin Wetherbee <[EMAIL PROTECTED]>
wrote:
> Frank Bax wrote:
>
> > Frank Bax wrote:
> >
> > > Nacef LABIDI wrote:
> > >
> > > > is there a better method to retrieve all the rows with dates in the
> > > > current month.
> > > >
> > >
> > > select * from mytable where extract(month from mydate) = extract(month
> > > from now()) and extract(year from mydate) = extract(year from now());
> > >
> >
> > Sorry; I was not thinking clearly - date_trunc is better for this:
> >
> > select * from mytable where date_trunc('month',mydate) =
> > date_trunc('month',now());
> >
>
> I have some code that uses extract() for this sort of thing. Would you
> mind explaining how date_trunc() is better for this?
>
> Most of my extract() results end up in drop-down boxes in HTML.
>
> Thanks.
>
> Colin
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Re: [SQL] First day of month, last day of month
Well, that's what it does afaikt.
>>> "Nacef LABIDI" <[EMAIL PROTECTED]> 2008-04-24 16:43 >>>
Actually I want to select all rows whith dates between first day of the month
00:00:00 and last date of the month 23:59:59
On Thu, Apr 24, 2008 at 4:15 PM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:
Frank Bax wrote:
Frank Bax wrote:
Nacef LABIDI wrote:
is there a better method to retrieve all the rows with dates in the current
month.
select * from mytable where extract(month from mydate) = extract(month from
now()) and extract(year from mydate) = extract(year from now());
Sorry; I was not thinking clearly - date_trunc is better for this:
select * from mytable where date_trunc('month',mydate) =
date_trunc('month',now());
I have some code that uses extract() for this sort of thing. Would you mind
explaining how date_trunc() is better for this?
Most of my extract() results end up in drop-down boxes in HTML.
Thanks.
Colin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
On Thursday 24 April 2008 10:47, Bart Degryse wrote: > Well, that's what it does afaikt. And what does afaikt mean? -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 [EMAIL PROTECTED] www.turbocorp.com
Re: [SQL] First day of month, last day of month
On Thu, Apr 24, 2008 at 8:43 AM, Nacef LABIDI <[EMAIL PROTECTED]> wrote:
> Actually I want to select all rows whith dates between first day of the
> month 00:00:00 and last date of the month 23:59:59
Then you can just use date_trunc on the values in the database. Plus
if you're using timestamp WITHOUT timezone, you can index on it.
create index table_datefield_month_trunc on table
(date_trunc('month',datefield));
select * from table where
date_trunc('month',timestampfield)='2007-10-01 00:00:00';
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
On Thu, Apr 24, 2008 at 8:52 AM, Terry Lee Tucker <[EMAIL PROTECTED]> wrote: > > On Thursday 24 April 2008 10:47, Bart Degryse wrote: > > > Well, that's what it does afaikt. > > And what does afaikt mean? As Far As I Kan Tell? ??? I'm used to AFAIR, As Far As I Rekall... :) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
sory, afaikt vas djust a tiping eror. mi apollogys >>> "Scott Marlowe" <[EMAIL PROTECTED]> 2008-04-24 16:56 >>> On Thu, Apr 24, 2008 at 8:52 AM, Terry Lee Tucker <[EMAIL PROTECTED]> wrote: > > On Thursday 24 April 2008 10:47, Bart Degryse wrote: > > > Well, that's what it does afaikt. > > And what does afaikt mean? As Far As I Kan Tell? ??? I'm used to AFAIR, As Far As I Rekall... :) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe > > Then you can just use date_trunc on the values in the > database. Plus if you're using timestamp WITHOUT timezone, > you can index on it. > Did not understand this. Are you saying timestamps WITH timezone are NOT indexable or you mean that you cant build a partial index on a timestamp-with-time-zone returning function? Regards, Fernando. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
On Apr 24, 2008, at 9:52 AM, Terry Lee Tucker wrote: On Thursday 24 April 2008 10:47, Bart Degryse wrote: > Well, that's what it does afaikt. afaikt -> as far as I can tell. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
On Apr 24, 2008, at 9:56 AM, Scott Marlowe wrote: On Thu, Apr 24, 2008 at 8:52 AM, Terry Lee Tucker <[EMAIL PROTECTED] > wrote: On Thursday 24 April 2008 10:47, Bart Degryse wrote: Well, that's what it does afaikt. And what does afaikt mean? As Far As I Kan Tell? ??? I'm used to AFAIR, As Far As I Rekall... :) Or AFAICS, As Far As I Can See Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
On Thu, Apr 24, 2008 at 9:10 AM, Fernando Hevia <[EMAIL PROTECTED]> wrote: > > De: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe > > > > > Then you can just use date_trunc on the values in the > > database. Plus if you're using timestamp WITHOUT timezone, > > you can index on it. > > > > Did not understand this. Are you saying timestamps WITH timezone are NOT > indexable or you mean that you cant build a partial index on a > timestamp-with-time-zone returning function? Correct, timestamptz or timestamp with timezone (timestamptz is the shorter alias) are not indexable because functions in an index must be immutable, and date_trunc on a timestamptz is not. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How I can check a substring is a valid number in postgresql ?
This regular expression works for numeric/float values as well as integers. It allows for exponents. tvalue ~ E'^[+-]?[0-9]+(.[0-9]*)?([Ee][+-]?[0-9]+)?\$' TJ O'Donnell http://www.gnova.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
On Thu, Apr 24, 2008 at 9:16 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Thu, Apr 24, 2008 at 9:10 AM, Fernando Hevia <[EMAIL PROTECTED]> wrote:
> > > De: [EMAIL PROTECTED]
> > > [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe
> >
> > >
> > > Then you can just use date_trunc on the values in the
> > > database. Plus if you're using timestamp WITHOUT timezone,
> > > you can index on it.
> > >
> >
> > Did not understand this. Are you saying timestamps WITH timezone are NOT
> > indexable or you mean that you cant build a partial index on a
> > timestamp-with-time-zone returning function?
>
> Correct, timestamptz or timestamp with timezone (timestamptz is the
> shorter alias) are not indexable because functions in an index must be
> immutable, and date_trunc on a timestamptz is not.
Note that if you are storing your time stamp as timestamptz, you can
use the "at time zone 'xyz'" construct to create an index, and as long
as you retrieve them with the same construct you'll get to use the
index.
create index test_ts_month_trunc on testtable (date_trunc('month',(ts
at time zone 'MST')));
select * from testtable where date_trunc('month',(ts at time zone
'MST'))='2007-10-01 00:00:00'::timestamp;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
> -Mensaje original-
> De: Scott Marlowe [mailto:[EMAIL PROTECTED]
>
>
> Note that if you are storing your time stamp as timestamptz,
> you can use the "at time zone 'xyz'" construct to create an
> index, and as long as you retrieve them with the same
> construct you'll get to use the index.
>
> create index test_ts_month_trunc on testtable
> (date_trunc('month',(ts at time zone 'MST')));
> select * from testtable
> where date_trunc('month',(ts at time zone 'MST'))='2007-10-01
00:00:00'::timestamp;
>
I see the point. Thanks for the elaboration.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] extracting words
On Wed, 23 Apr 2008 07:18:44 +0200 "A. Kretschmer" <[EMAIL PROTECTED]> wrote: > am Tue, dem 22.04.2008, um 22:25:04 +0100 mailte Tarlika Elisabeth > Schmitz folgendes: > > TABLE product: product_pk, product_name, department_fk > > TABLE product: department_pk, department_name > > > > ... > > > > > > I need to extract the words from department_name and product_name > > (words are separated by spaces) ... > > A little function (author: David Fetter) > > -- split a string to rows, by David Fetter > CREATE OR REPLACE FUNCTION split_to_rows(TEXT, TEXT) RETURNS SETOF > TEXT STRICT > LANGUAGE sql > AS $$ > SELECT (string_to_array($1, $2))[s.i] > FROM generate_series( > 1, > array_upper(string_to_array($1, $2), 1) > ) AS s(i); > $$; > > > And now: > > test=*# select * from product ; > id | val1 | val2 > +--+--- > 1 | cakes & desserts | apple crumble > 2 | cakes & desserts | cheese cake > 3 | starters & soups | french onion soup > (3 rows) > > test=*# select id, split_to_rows(replace(val1 || ' ' || val2,' > &',''),' ') as col1 from product; id | col1 > +-- > 1 | cakes > 1 | desserts > 1 | apple > 1 | crumble > 2 | cakes > 2 | desserts > 2 | cheese > 2 | cake > 3 | starters > 3 | soups > 3 | french > 3 | onion > 3 | soup > (13 rows) Many thanks! This is ingenious! Before I ask any stupid questions such as "what does replace (text, ' &', '') do?": where can I find a more detailed description of the string functions? I would like to treat all white space (new-line, tab, space) as word separator. Will the above work on 7.4? -- Best Regards, Tarlika Elisabeth Schmitz A: Because it breaks the logical sequence of discussion Q: Why is top posting bad? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
It would be rather :
test=*# select date_trunc('month', current_date), date_trunc('month',
current_date)+'1month':
>
> :interval-'1sec'::interval;
"2008-04-01 00:00:00+02";"2008-04-30 23:59:59+02"
On Thu, Apr 24, 2008 at 3:15 PM, A. Kretschmer <
[EMAIL PROTECTED]> wrote:
> am Thu, dem 24.04.2008, um 14:59:47 +0200 mailte Nacef LABIDI folgendes:
> > Hi all,
> >
> > I want to select data from a table according to a date column in my
> table. I
> > want to select all the rows which have date in the current month. So is
> there a
> > way to get from the actual date the first day and the last day of the
> month or
>
> test=*# select date_trunc('month', current_date), date_trunc('month',
> current_date)+'1month'::interval-'1day'::interval;
> date_trunc |?column?
> +
> 2008-04-01 00:00:00+02 | 2008-04-30 00:00:00+02
>
>
>
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
