[SQL] Wildcard in date field???
Hello, I need to make a query that will select items that have a date matching the current month for example. For June this means that any day between 2000-06-01 and 2000-06-30 are ok. To do that I need a wildcard like "%" to replace the actual day in the date field. Ex.: select * from item where date = '2000-06-%%'; but that doesn't work... What is the right way? Thanks! -- ~ Marc Andre Paquin
Re: [SQL] Wildcard in date field???
SELECT * FROM my_table WHERE date_part('month', col_name::datetime) = '06'
AND date_part('year', col_name::datetime) = '2000';
Looks for more detail in the manual for "date_part"
Dorin
At 03:52 PM 6/22/2000 -0400, Web Manager wrote:
>Hello,
>
>I need to make a query that will select items that have a date matching
>the current month for example.
>
>For June this means that any day between 2000-06-01 and 2000-06-30 are
>ok. To do that I need a wildcard like "%" to replace the actual day in
>the date field.
>
>Ex.: select * from item where date = '2000-06-%%';
>
>but that doesn't work... What is the right way?
>
>Thanks!
>--
>~
>Marc Andre Paquin
Re: [SQL] Wildcard in date field???
Web Manager wrote:
>
> I need to make a query that will select items that have a date matching
> the current month for example.
>
> For June this means that any day between 2000-06-01 and 2000-06-30 are
> ok. To do that I need a wildcard like "%" to replace the actual day in
> the date field.
>
> Ex.: select * from item where date = '2000-06-%%';
Multiple ways to do it, but here's one:
select *
from item
where date_part('month',mydate) = 6
and date_part('year',mydate) = 2000;
Regards,
Ed Loehr
Re: [SQL] eliminating duplicates in results..
SELECT DISTINCT On Wed, 21 Jun 2000, Francisco Hernandez wrote: > hello everyone, > i have a query like so: > > select item.type as ig_id,item.with_design,item_group.type as > group_name, 'true' as valid from item,item_group where item.description > ~* 'w/out' and item.type = item_group.ig_id; > > and i get duplicates.. how could i remove all the duplicate entrys using > SQL? > the duplicates are fine.. im just trying to migrate data from one table > to another.. with columns renamed and some added/removed.. >
[SQL] timespan casting
hi, thought this up while playing with time spans.. here is my table and data.. bignose=> \d foo Table "foo" Attribute | Type| Modifier ---+---+-- start | timestamp | stop | timestamp | bignose=> select start,stop, stop-start as start_stop from foo; start | stop | start_stop ++ 2000-06-22 20:37:12-03 | 2000-06-22 20:37:12-03 | 00:000 2000-06-22 20:40:40-03 | 2000-06-23 20:40:40-03 | 1 00:00 2000-06-22 20:40:53-03 | 2000-09-30 20:40:53-03 | 100 00:00 2000-06-22 20:41:08-03 | 2000-06-23 02:41:08-03 | 06:00 2000-06-22 20:41:30-03 | 2010-11-22 19:41:30-04 | 3805 00:00 (5 rows) now my question.. first of all is the first start_stop result a little off ? (talking about the 3 0's..) second is there a way i can do a select so it says something more human usable ie : 100 days 22 hours 32 minutes.. jeff
Re: [SQL] timespan casting
Jeff MacDonald wrote: > bignose=> select start,stop, stop-start as start_stop from foo; > start | stop | start_stop > ++ > 2000-06-22 20:37:12-03 | 2000-06-22 20:37:12-03 | 00:000 > > now my question.. first of all is the first start_stop result a little > off ? (talking about the 3 0's..) What's off about it?? Your start and stop appear identical. > second is there a way i can do a select so it says something more human > usable ie : 100 days 22 hours 32 minutes.. Try to_char(). http://www.postgresql.org/docs/postgres/x2976.htm Regards, Ed Loehr
Re: [SQL] Using substr with user defined types
Thus spake Tom Lane
> [EMAIL PROTECTED] (D'Arcy J.M. Cain) writes:
> > create function chkpass_rout(opaque)
> > returns opaque
> > as '/usr/pgsql/modules/chkpass.so'
> > language 'c';
>
> > Here is what happens.
>
> > soccer=> select chkpass_rout('hello'::chkpass);
> > ERROR: typeidTypeRelid: Invalid type - oid = 0
>
> Functions that you intend to invoke as ordinary functions shouldn't have
> inputs or outputs declared "opaque", because the expression evaluation
> code won't have any idea what to do. When you are building functions
> that will be the input or output converters for a datatype, you can read
> "opaque" as meaning "C string", so for example the input converter takes
> opaque and returns your type. But otherwise you don't want to be using
> opaque. Perhaps what you wanted here was
> "create function chkpass_rout(chkpass) returns text".
OK, I tried this.
load '/usr/pgsql/modules/chkpass.so';
--
-- Input and output functions and the type itself:
--
create function chkpass_in(opaque)
returns opaque
as '/usr/pgsql/modules/chkpass.so'
language 'c';
create function chkpass_out(opaque)
returns opaque
as '/usr/pgsql/modules/chkpass.so'
language 'c';
create type chkpass (
internallength = 16,
externallength = 13,
input = chkpass_in,
output = chkpass_out
);
create function raw(chkpass)
returns text
as '/usr/pgsql/modules/chkpass.so', 'chkpass_rout'
language 'c';
Then I did this.
darcy=> select 'hello'::chkpass;
?column?
--
:Rd1xqQo0.2V6.
(1 row)
darcy=> select raw('hello'::chkpass);
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
I added an fprintf to stderr as the first statement in chkpass_rout() which
doesn't print so I am pretty sure it isn't my function. The same thing
happens if I create a table with a chkpass type. Any ideas?
--
D'Arcy J.M. Cain| Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
[SQL] Merging two columns into one
Hi All, I have two columns in a table (areacode and phone number) that I'd like to merge into one (phone number) containing both sets of info. Could anyone suggest a simple way of achieving this? Thanks, Gary MacMinn
Re: [SQL] Merging two columns into one
On Fri, 23 Jun 2000, Gary MacMinn wrote: > Hi All, > > I have two columns in a table (areacode and phone number) that I'd like to merge into one (phone number) containing both sets of info. Could anyone suggest a simple way of achieving this? export the data to a file using the copy command, remove the delimiter with the unix command 'tr -d' import the file, now minus the delimeter using the copy command. -- Sincerely etc., NAME Christopher Sawtell - iOpen Technologies Ltd. CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL chris @ iopen . co . nz, csawtell @ xtra . co . nz WWWhttp://www.iopen.co.nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Re: [SQL] Using substr with user defined types
[EMAIL PROTECTED] (D'Arcy J.M. Cain) writes: > Any ideas? Not with that much info. Sooner or later you're going to have to show us your C code... regards, tom lane
Re: [SQL] Merging two columns into one
Christopher Sawtell wrote: >On Fri, 23 Jun 2000, Gary MacMinn wrote: >> Hi All, >> >> I have two columns in a table (areacode and phone number) that I'd like to > merge >into one (phone number) containing both sets of info. Could anyone suggest a >simple way of achieving this? > >export the data to a file using the copy command, >remove the delimiter with the unix command 'tr -d' >import the file, now minus the delimeter using the copy command. Surely tr will remove ALL the delimiters, so this is not helpful unless these columns are the only ones in the table. You would have to use awk or perl to process the exported file and delete the correct delimiter. For an SQL solution, how about: SELECT col1, col2,..., areacode || phone as phone, colx, coly,... INTO new_table FROM table; Then you can drop the old table and recreate it with the correct columns and import the data into it from new_table. (You could just rename new_table if it doesn't need to be created with constraints.) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Train up a child in the way he should go, and when he is old, he will not depart from it."Proverbs 22:6
[SQL] Re: [GENERAL] Sequences do not obey transactions...
On Wed, 21 Jun 2000, Haroldo Stenger wrote: > This issue has been asked & answered MANY times, once a week perhaps. I'll copy > here what a folk answered once Hmm.. Then if the email archive searchs on the web site had been working I would not have had to was the list's time. As for the comments by people that pgsql's sequence behavior is well documented, please tell me where! I have looked through the HTML does and the FAQ that comes with Pgsql 7.0.2 and found no mention of it. > "You can't. Sequences are not designed for continuity, they are designed for > uniqueness. Now that I think about it (again w/other people's explainations taken into account) pgsql's behavior now makes sense to me. I was just looking for a continuous sequence of unique numbers and thought a sequence might be handy. Guess not. :( Thanks for everyone's explaination. > Haroldo Stenger wrote: > > And I add one of my own: It is not really necessary to have continuity in nearly > > all apps. Your question is valid anyhow, but ask yourself: How does Oracle > > resolve this? How would I program it myself by hand? And there you'll understand > > the issue deeply. > > How funny it is to quote myself :9 I'd like to add this: When one has to number > paper forms (invoices for instance), one must be careful about holes in > sequences. What I do, is to have two sequences. One for the user, and one for > the system. When the user has confirmed all her data, I'll COPY to another table > the data, which NOW doesn't have a chance to cancel. Well, not so sure, but 99% > of aborts, are user aborts. So this may help. That is similar to what I was attempting to do. I am making an inventory database where each piece of computer equipment has a unique number assigned to it. A sequence would save me having to figure out what number was next. But if it had holes in the sequence, then I would end up wasting the sequential labels I had already printed. :( Given my situtation, I think I will just use the label sheet to tell me which number is next and enter it in from there. Low-tech, but should work. :) > Note to Bruce (or current FAQ mantainer): Please, add both the answer to the > very question, and this addition of my own to the FAQ. I would have loved to > find it somewhere, when I didn't know what to do. Yes, please do. I always search the documentation and email list archives (if available) before asking a question. Guess in this case I hit a question that fell through the cracks before making it into the FAQ. Once again, thanks for everyone's help. --- | "For to me to live is Christ, and to die is gain."| |--- Philippians 1:21 (KJV) | --- | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | ---
