Re: [SQL] subtract a day from the NOW function
Osvaldo, Thanks! This is a great solution. It definitely is very easy to read. I like to have my SQL as clean as my java code. I ended up using the following: SELECT some_timestamp FROM some_table WHERE some_timestamp::date > 'yesterday'::date; Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Osvaldo Kussama [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 4:41 PM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] subtract a day from the NOW function --- "Campbell, Lance" <[EMAIL PROTECTED]> escreveu: > Table > > Field "some_timestamp" is a timestamp. > > > > In a "WHERE" statement I need to compare a timestamp > field in a table > "some_timestamp" to now() - one day. > > > > Example: > > > > SELECT some_timestamp WHERE to_char(some_timestamp, > 'MMDD') > > (to_char(now(), 'MMDD') - 1 day); > > > > The statement "to_char(now(), 'MMDD') - 1 day)" > is obviously > incorrect. I just need to know how to form this in > a way that will > work. > > > > If there is an entirely different solution I am all > for it. Do note > that I started down this path because I want to > exclude the hour, > minutes and seconds found in the field > "some_timestamp" and in the > function now(). > Try: SELECT some_timestamp WHERE some_timestamp > 'yesterday'::timestamp; Look 8.5.1.5. Special Values at: http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html []s Osvaldo Novo Yahoo! Cadê? - Experimente uma nova busca. http://yahoo.com.br/oqueeuganhocomisso ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] show index from [table]
hi list, currently i am switching from mysql to pgsql, so i am a bit new to postgres' syntax. at the moment i am looking in postgres for something which is similar to SHOW INDEX FROM [table] in mysql. unfortunately i could not find anything satisfying relating to this issue. i have found out, that there is the -di option with psql. but actually i would need the information from within a (postgres) sql-query. is there a possibility to get information about the indices which have been created on a table? if there is not, it might be sufficient for me to get the create index strings, such like you get, when viewing a table in pgAdmin: -- Index: g_g114_b_idx -- DROP INDEX g_g114_b_idx; CREATE INDEX g_g114_b_idx ON g_g114 USING btree (b); and parse them manually. is that possible in any way? maybe there is something similar to SHOW CREATE TABLE (as in MySQL) in postgresql. thanks in advance, stefan ___ SMS schreiben mit WEB.DE FreeMail - einfach, schnell und kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] show index from [table]
Stefan Zweig <[EMAIL PROTECTED]> schrieb: > hi list, > > currently i am switching from mysql to pgsql, so i am a bit new to > postgres' syntax. You are welcome. > > at the moment i am looking in postgres for something which is similar > to SHOW INDEX FROM [table] in mysql. unfortunately i could not find > anything satisfying relating to this issue. > > i have found out, that there is the -di option with psql. Yes, right. > > but actually i would need the information from within a (postgres) > sql-query. is there a possibility to get information about the indices > which have been created on a table? Yes, of corse. Please start psql with the -E - option. Now you can see the underlying sql-statement for commands like \di. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] show index from [table]
Andreas Kretschmer napisał(a): Yes, right. but actually i would need the information from within a (postgres) sql-query. is there a possibility to get information about the indices which have been created on a table? Yes, of corse. Please start psql with the -E - option. Now you can see the underlying sql-statement for commands like \di. ... or even better: select * from pg_indexes where tablename = 'your_table'; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
