[SQL] Formatting current_time output
Hi. Anyone have any tips on how I can approximate the following: SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL Returned: 10:58 AM I've found lpad(current_time,5); which gets me 1/3 of the way. Is there a function I haven't found? TIA --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 -- -- SQL Clinic - An Open Source Clinical Record www.sqlclinic.net ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] order by x DESC, y ASC indexing problem
Hello, I saw a posting regarding this issue in august, with no solution... How to have the planner use an index in the case of a query like : SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; (X is a date and Y a varchar) What would that index be? Is there a function I can use, to invert x (the date), so that I can make a query / index set like : CREATE INDEX INDEX1 ON TABLE 1 (INVERT(X), Y ASC); SELECT * FROM TABLE1 ORDER BY INVERT(X) ASC, Y ASC; Wouldn't it be great to have a mySQL, SAPDB-like syntax of the sort : CREATE INDEX INDEX1 ON TABLE 1 (X DESC, Y ASC); Thanks, vincent ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] order by x DESC, y ASC indexing problem
Vincent, > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; > > (X is a date and Y a varchar) > > What would that index be? > > Is there a function I can use, to invert x (the date), so that I can > make a query / index set like : PostgreSQL's sorting mechanism can use an index either ascending or descending; it does not make a difference. However, most of the time ORDER BY does not use an index at all because a seq scan is faster. Usually, ORDER BY uses an index only when combined with related criteria and/or the LIMIT clause. -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] epoch to date
Hi all, Maybe I missed something obvious, but is there a built-in function to convert epoch to date? I couldn't find it in the documentation for extract and friends. Thanks, -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + All in all, you're just another brick on the wall... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Formatting current_time output
Thomas, > SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle > SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL > > Returned: 10:58 AM > > I've found lpad(current_time,5); which gets me 1/3 of the way. > Is there a function I haven't found? Um, what's wrong with: SELECT to_char(current_time, 'HH12:MI AM'); ? (See "Formatting Functions" under "Functions and Operators" in the User's Guide) -Josh Berkus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Formatting current_time output
SELECT to_char(now(), 'HH24:MI AM'); (in 7.2.1) == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Formatting current_time output
On 7.2.2, you can use select to_char(now(), 'HH12:MI AM'); (using current_time raises an error, but now() works fine). Charlie Josh Berkus wrote: >Thomas, > > > >>SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle >>SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL >> >>Returned: 10:58 AM >> >>I've found lpad(current_time,5); which gets me 1/3 of the way. >>Is there a function I haven't found? >> >> > >Um, what's wrong with: >SELECT to_char(current_time, 'HH12:MI AM'); >? > >(See "Formatting Functions" under "Functions and Operators" in the >User's Guide) > >-Josh Berkus > > >---(end of broadcast)--- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html > > -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Formatting current_time output
On Thu, 3 Oct 2002, Josh Berkus wrote: > Thomas, > > > SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle > > SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL > > > > Returned: 10:58 AM > > > > I've found lpad(current_time,5); which gets me 1/3 of the way. > > Is there a function I haven't found? > > Um, what's wrong with: > SELECT to_char(current_time, 'HH12:MI AM'); Not a thing! Except that I think you mean 'current_date'. ;-) Cheers Josh (and thanks alot)... BTW the JOIN you sent the other day worked fine. Thanks again, --- Thomas Good e-mail: [EMAIL PROTECTED] Programmer/Analyst phone: (+1) 718.818.5528 Residential Services fax: (+1) 718.818.5056 Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359 -- -- SQL Clinic - An Open Source Clinical Record www.sqlclinic.net ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Formatting current_time output
On Thu, Oct 03, 2002 at 10:46:19AM -0400, Thomas Good wrote: > > SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle > SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL SELECT TO_CHAR(now(),'HH:MI AM'); -- PostgreSQL :-) -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] epoch to date
On Thu, 3 Oct 2002, Roberto Mello wrote: > Hi all, > > Maybe I missed something obvious, but is there a built-in function to > convert epoch to date? I couldn't find it in the documentation for extract > and friends. well to compute epoch timestamp # SELECT (now() -(extract(epoch from now())::int4)/86400)::datetime ; if thats what you asked :) > > Thanks, > > -Roberto > > -- > +|Roberto Mello -http://www.brasileiro.net/ |--+ > + Computer Science Graduate Student, Utah State University + > + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + > All in all, you're just another brick on the wall... > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] drop constraint primary key
project=# select version(); version - PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) Running above, am trying to drop the primary key, but get the follwing error. project=# alter table class_teacher_rlt drop constraint class_teacher_rlt_pkey restrict; ERROR: ALTER TABLE / DROP CONSTRAINT: class_teacher_rlt_pkey does not exist project=# \d class_teacher_rlt Table "class_teacher_rlt" Column| Type | Modifiers --+--+-- -- class_id | integer | not null user_id | integer | not null school_id| integer | not null start_date | timestamp with time zone | not null default now() end_date | timestamp with time zone | class_action_type_id | integer | not null default 1 action_detail| character varying(100) | teacher_role_id | integer | ind_prim_teacher | boolean | not null default 'f'::bool ind_lead_teacher | boolean | default 'f' status_id| integer | default 1 created_date | timestamp with time zone | not null default 'now' last_modified| timestamp with time zone | Primary key: class_teacher_rlt_pkey Any ideas? Thanks. Marie ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] drop constraint primary key
"Marie G. Tuite" <[EMAIL PROTECTED]> writes: > Running above, am trying to drop the primary key, but get the follwing > error. > project=# alter table class_teacher_rlt drop constraint > class_teacher_rlt_pkey restrict; > ERROR: ALTER TABLE / DROP CONSTRAINT: class_teacher_rlt_pkey does not exist In 7.2 DROP CONSTRAINT only works for CHECK-type constraints; you'll have to drop the underlying index directly to get rid of a primary-key-type constraint. 7.3 does allow DROP CONSTRAINT for this. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Updating from select
Hi Manfred, Such a silly thing blocked me, and I was about to write a stored procedure. Thanks a lot !! BTW, does anyone knows about a RH7.2 or newer RPM of v7.3? Do I need any other tools not included in RH7.2 to compile it? Thrasher Manfred Koizar wrote: > On Wed, 02 Oct 2002 19:11:19 +0200, Thrasher <[EMAIL PROTECTED]> > wrote: > >>UPDATE trans_log t SET t.cost = >> (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id) > > > Thrasher, try it without the table alias t: > > UPDATE trans_log SET cost = >(SELECT SUM(p.cost) FROM products_log p > WHERE p.trans = trans_log.id) > > Servus > Manfred > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] order by x DESC, y ASC indexing problem
On Thu, Oct 03, 2002 at 22:07:40 -0400, Vincent-Olivier Arsenault <[EMAIL PROTECTED]> wrote: > Hello, > > I saw a posting regarding this issue in august, with no solution... Then you didn't read the entire thread. You can create a new operator class to get the desired behavior. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] order by x DESC, y ASC indexing problem
On Thu, Oct 03, 2002 at 19:30:59 -0700, Josh Berkus <[EMAIL PROTECTED]> wrote: > Vincent, > > > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; > > > > (X is a date and Y a varchar) > > > > What would that index be? > > > > Is there a function I can use, to invert x (the date), so that I can > > make a query / index set like : > > PostgreSQL's sorting mechanism can use an index either ascending or > descending; it does not make a difference. Note this is referring to a multicolumn index. To use all columns of a multicolumn index the sort directions all need to be the same. However you are right to point out that this may not be a big deal for many applications where using the index on the first column gets the majority of the speed up. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] order by x DESC, y ASC indexing problem
Vincent-Olivier Arsenault <[EMAIL PROTECTED]> writes: > How to have the planner use an index in the case of a query like : > SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; A normal index on (X,Y) is useless for this query, because neither scan direction in the index corresponds to the sort ordering you are asking for. In theory you could build a custom "reverse sort order" operator class for X's datatype, and then make an index using the reverse opclass for X and the normal opclass for Y. Or the other way round (normal sort order for X and reverse for Y). In practice, as Josh notes nearby, this is a waste of time for the query as given: whole-table sorts usually are better done by sorting not by indexscanning. If you are doing a partial scan like SELECT ... ORDER BY ... LIMIT some-small-number then it might be worth the trouble to set up a custom-order index. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
