Thread: how to select a time frame on timestamp rows.
Hello,today i was trying to perform a query on a database using a time stamp field, i need to get all records which belong to year 2000, month 11, is there any other way to doit, or is this the pgsql way? , actually i'm using a query like this: select User_Name from tbacct where acct_timestamp like '2000-11%' group by User_Name; on MySQL i use this select User_Name from tbAcct where month (Acct_Timestamp) = 11 group by User_Name; (on mysql i was't worried about year yet) is anything like the month function from mysql on pg-sql? sorry if the question is to obvious, but i was reading the docs and can't find an answer. Regards!
bartschm@psi.com writes: > today i was trying to perform a query on a database using a time stamp > field, i need to get all records which belong to year 2000, month 11, > is there any other way to doit, or is this the pgsql way? , actually > i'm using a query like this: > select User_Name from tbacct where acct_timestamp like '2000-11%' group > by User_Name; select user_name from tbacct where extract(month from acct_timestamp) = 11 ... (SQL compliant) -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut wrote: Thanks Peter for the answer, so i assume i can also do select user_name from tbacct where extract(month from acct_timestamp) = 11 and extract(year from acct_timestamp) = 2000 ... > > bartschm@psi.com writes: > > > today i was trying to perform a query on a database using a time stamp > > field, i need to get all records which belong to year 2000, month 11, > > is there any other way to doit, or is this the pgsql way? , actually > > i'm using a query like this: > > select User_Name from tbacct where acct_timestamp like '2000-11%' group > > by User_Name; > > select user_name from tbacct where extract(month from acct_timestamp) = 11 ... > > (SQL compliant) > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Using INDEX on date/time values // Re: how to select a time frame on timestamp rows.
From
Alvar Freude
Date:
Hi,
Peter Eisentraut schrieb:
>
> > today i was trying to perform a query on a database using a time stamp
> > field, i need to get all records which belong to year 2000, month 11,
[...]
> select user_name from tbacct where extract(month from acct_timestamp) = 11 ...
is there any way of using an index for selecting some rows, e.g.
selecting all data from one month?
Explain sais, that the isn't used!
blaster=# explain select id from forum_data where extract(month from
date) = 1; NOTICE: QUERY PLAN: Seq Scan on forum_data (cost=0.00..59.74 rows=3 width=4)
also, I didn't found documentation about "extract" in the PG docs. ups!
;) wher is it, any hints?
In the mailing list archives I found the following hint:
select * from t1 where d >= (date_trunc('month', timestamp 'today') - interval '1 month') and d <
date_trunc('month',timestamp 'today');
it also dosn't use index according to explain ...
My test table has ~350 rows.
Is it possible to use indexes?
or is it better to use an indexed int-field with unixtime? (int8!)
Thanks & Ciao Alvar
--
Alvar C.H. Freude | alvar.freude@merz-akademie.de
Demo: http://www.online-demonstration.org/ | Mach mit!
Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/ | Blast/english
Hello, I was looking for a datatype to represent a single byte unsigned integer. The closest thing I can find looking through the online manual is a one byte char. Are there any side-effects of using a char datatype for this purpose? Is there a better datatype to use? Thanks in advance, Joe
Joe Conway writes: > I was looking for a datatype to represent a single byte unsigned integer. > The closest thing I can find looking through the online manual is a one byte > char. Are there any side-effects of using a char datatype for this purpose? Yes, it won't store single byte unsigned integers, only single byte signed characters. > Is there a better datatype to use? smallint with a check constraint -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Re: Using INDEX on date/time values // Re: how to select a time frame on timestamp rows.
From
Mike Castle
Date:
On Sun, Jan 14, 2001 at 04:46:08AM +0100, Alvar Freude wrote: > Peter Eisentraut schrieb: > > > > > today i was trying to perform a query on a database using a time stamp > > > field, i need to get all records which belong to year 2000, month 11, > [...] > > select user_name from tbacct where extract(month from acct_timestamp) = 11 ... > > is there any way of using an index for selecting some rows, e.g. > selecting all data from one month? What about select blah from foo where month >= 2000-11-01 and month < 2000-12-01 Fix up as appropriate. mrc -- Mike Castle Life is like a clock: You can work constantly dalgoda@ix.netcom.com and be right all the time,or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Hi, I've been searching the docs and been unable to find the answer to this -- is there a way to get the current database server host tcpip address, postmaster port, and database name from a SQL query? I'd like to access those from within a plpgsql function without having to create and populate some sort of identification table. Thanks, Joe