[SQL] Timestamp
Hi ! In my database I've a field "departure" with timestamp without time zone data type. eg : departure 2003-11-01 14:29:46 Now I want to select it as in this format 01-11-2003 14:29:46 How should I do it?. Thank you for any help. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Timestamp
Dnia 2003-10-22 10:02, Użytkownik Abdul Wahab Dahalan napisał: Hi ! In my database I've a field "departure" with timestamp without time zone data type. eg : departure 2003-11-01 14:29:46 Now I want to select it as in this format 01-11-2003 14:29:46 How should I do it?. You can find it in documentation - 6.7. Data Type Formatting Functions select to_char(departure,'DD-MM- HH24:MI:SS') ... Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Timestamp
On Wed, 22 Oct 2003, Abdul Wahab Dahalan wrote: > Hi ! > In my database I've a field "departure" with timestamp without time zone > data type. > eg : > departure > 2003-11-01 14:29:46 Maybe SET DateStyle TO 'German' ; SELECT replace(departure,'.','-') from ; is close to what you want, but better leave it default and format the date (input/output) from your app. > > Now I want to select it as in this format 01-11-2003 14:29:46 How should > I do it?. > > Thank you for any help. > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] see a current query
Hello i'am logged in as superuser (pgsql) trying to SELECT * FROM pg_stat_activity; and seeing NULLs instead of current_query column& What is it? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Timestamp
Check this out http://www.postgresql.org/docs/7.3/static/functions-formatting.html to_char(departure, 'MM-DD- HH24:MI:SS') as newformatdeparture Ted --- Abdul Wahab Dahalan <[EMAIL PROTECTED]> wrote: > Hi ! > In my database I've a field "departure" with > timestamp without time zone > data type. > eg : > departure > 2003-11-01 14:29:46 > > Now I want to select it as in this format 01-11-2003 > 14:29:46 How should > I do it?. > > Thank you for any help. > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] see a current query
On Wed, 2003-10-22 at 05:26, sad wrote: > Hello > > i'am logged in as superuser (pgsql) > trying to > SELECT * FROM pg_stat_activity; > and seeing NULLs instead of current_query column& You need to change the stats settings in postgresql.conf. By default the current query is not enabled as it does cause a performance loss. signature.asc Description: This is a digitally signed message part
[SQL] Query planner: current_* vs. explicit date
PsotgreSQL 7.2.4: Query planner is behaving strangely. It operates differently for explicit dates and derived dates... any ideas on why? ( and why I might not have noticed this before... ) CREATE TABLE trans_table ( id serial, user_name varchar(50), trans_type varchar(50), trans_data varchar(50), trans_date timestamptz, trans_uuid uniqueidentifier, CONSTRAINT trans_table_pkey PRIMARY KEY (id) ) WITH OIDS; ... Insert lots of data ... CREATE INDEX trans_table_date_idx ON trans_table USING btree (trans_date); CREATE INDEX trans_table_user_date_idx ON trans_table USING btree (user_name,trans_date); CREATE INDEX trans_table_uuid_idx ON trans_table USING btree (trans_uuid); VACUUM ANALYZE trans_table; EXPLAIN SELECT id FROM trans_table WHERE trans_date >= current_date::timestamp; Seq Scan on trans_table (cost=0.00..177369.52 rows=315267 width=4) EXPLAIN SELECT id FROM trans_table WHERE trans_date >= '10/22/2003 00:00:00 AM'::timestamp; Index Scan using trans_table_date_idx on trans_table (cost=0.00..1474.69 rows=417 width=4) CG __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Expressional Indexes
Josh Berkus <[EMAIL PROTECTED]> writes: >> I'm not particularly happy with the phrase "expressional index", and >> would like to think of something else to use before the 7.4 docs go >> final. Any ideas? > Hmmm ... well, actually, I like "Expressional Indexes". What's wrong > with it? Mainly that "expressional" is a made-up word. I have been considering using "calculated index" or "computed index" but dunno if that really conveys anything. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Query planner: current_* vs. explicit date
I'd guess that the planner doesn't know what current_date::timestamp is ahead of time, so it chooses a seq scan. On Wed, 22 Oct 2003, Chris Gamache wrote: > PsotgreSQL 7.2.4: > > Query planner is behaving strangely. It operates differently for explicit dates > and derived dates... any ideas on why? ( and why I might not have noticed this > before... ) > > CREATE TABLE trans_table ( > id serial, > user_name varchar(50), > trans_type varchar(50), > trans_data varchar(50), > trans_date timestamptz, > trans_uuid uniqueidentifier, > CONSTRAINT trans_table_pkey PRIMARY KEY (id) > ) WITH OIDS; > > ... Insert lots of data ... > > CREATE INDEX trans_table_date_idx ON trans_table USING btree (trans_date); > CREATE INDEX trans_table_user_date_idx ON trans_table USING btree > (user_name,trans_date); > CREATE INDEX trans_table_uuid_idx ON trans_table USING btree (trans_uuid); > > VACUUM ANALYZE trans_table; > > EXPLAIN SELECT id FROM trans_table WHERE trans_date >= current_date::timestamp; > Seq Scan on trans_table (cost=0.00..177369.52 rows=315267 width=4) > > EXPLAIN SELECT id FROM trans_table WHERE trans_date >= '10/22/2003 00:00:00 > AM'::timestamp; > Index Scan using trans_table_date_idx on trans_table (cost=0.00..1474.69 > rows=417 width=4) > > CG > > __ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Expressional Indexes
Tom, Jumping lists, NOVICE is unlikely to have an opinion about this. > I'm not particularly happy with the phrase "expressional index", and > would like to think of something else to use before the 7.4 docs go > final. Any ideas? Hmmm ... well, actually, I like "Expressional Indexes". What's wrong with it? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Expressional Indexes
Tom, > Mainly that "expressional" is a made-up word. So? We're in the tech biz, Tom. New-coined words are expected. And that way nobody will expect it to mean something else, since we made it up. -- H. Dumpty, Q.E.D. > I have been considering using "calculated index" or "computed index" > but dunno if that really conveys anything. Well, "Expression Indexes" is the most accurate. Or "Expression-Based Indexes." -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query planner: current_* vs. explicit date
Scott, Chris, > I'd guess that the planner doesn't know what current_date::timestamp is > ahead of time, so it chooses a seq scan. Yes, this is a known problem. There was a single-query workaround, but I can't remember it right now. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Expressional Indexes
Josh Berkus wrote: > Tom, > > > Mainly that "expressional" is a made-up word. > > So? We're in the tech biz, Tom. New-coined words are expected. And that > way nobody will expect it to mean something else, since we made it up. > -- H. Dumpty, Q.E.D. > > > I have been considering using "calculated index" or "computed index" > > but dunno if that really conveys anything. > > Well, "Expression Indexes" is the most accurate. Or "Expression-Based > Indexes." Computed index sound too much like there is computation done during the index lookup, which there isn't, and it sounds like it would behave differently from a normal index, which it doesn't. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Expressional Indexes
Tom Lane writes: > Mainly that "expressional" is a made-up word. At least it's better than "functional index", because I had always wondered where the dysfunctional indexes went. :) I like "expression index". -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query planner: current_* vs. explicit date
Josh Berkus <[EMAIL PROTECTED]> writes: >> I'd guess that the planner doesn't know what current_date::timestamp is >> ahead of time, so it chooses a seq scan. > Yes, this is a known problem. There was a single-query workaround, but I > can't remember it right now. Right; the problem from the planner's point of view is that it sees WHERE datecol >= unknownvalue and it has no idea how much of the table will be fetched by this query. Its default guess is that a fair fraction of the table will be fetched (one-third, I think) and that dissuades it from using an indexscan. This seems reasonable to me, since the worst-case behavior if it were to default to an indexscan could be awful. The easiest workaround is to convert the query into a range query, for example WHERE datecol >= current_date AND datecol <= current_date + 1000; being careful that both comparison values are nonconstant (don't use 'infinity'::timestamp, for instance, even though that might seem like a reasonable thing to do). The planner still has no idea how many rows will be fetched exactly, but it does realize that this is a range condition, and its default assumption about the number of matching rows is small enough to encourage indexscan use. Of course this workaround assumes that you can pick an upper bound that you are sure is past the end of the available values, but that's usually not hard in the sort of context where you would have thought that the one-sided inequality test is a sane thing to do anyway. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [NOVICE] Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
Michael, > My final thought would be to continue using "functional index", noting > that operators are a special class of function. Out of the above > description of "value expression", I believe only functions and > operators are allowed as expressions in the CREATE INDEX syntax, > correct? (Besides referring to a column.) Well argued. The problem is Peter's point, which you weren't cc'd on: >At least it's better than "functional index", because I had always >wondered where the dysfunctional indexes went. :) -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
