Re: [SQL] time interval math

2012-02-13 Thread Jasen Betts
On 2012-02-09, Edward W. Rouse wrote: >> the operation abs() is meaninless on the type interval >> eg: what is abs( '1 month - 32 days + 24 hours'::interval ) > > If you need to add 30 intervals together, then +- is not meaningless. if you stop reading after one line you miss the answer. --

Re: [SQL] time interval math

2012-02-09 Thread Edward W. Rouse
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Jasen Betts > Sent: Thursday, February 09, 2012 6:37 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] time interval math > > On 2012-02-08

Re: [SQL] time interval math

2012-02-09 Thread Jasen Betts
On 2012-02-08, Edward W. Rouse wrote: > I'm still working on getting this to work, but the summary is this: > > I am getting several (many) intervals of hour, minutes and seconds. I need a > sum of the absolute value these intervals, similar to the SUM(ABS()) > function for numbers; and I need to

Re: [SQL] time interval math

2012-02-08 Thread Tim Landscheidt
"Edward W. Rouse" wrote: > Hehe, thanks, I played around and ended up with this: > round(SUM(extract('epoch' from (time_out - time_in > I will have to do the division outside of the query, but that's really a > minor issue. You can always use subqueries. > Knowing the total i

Re: [SQL] time interval math

2012-02-08 Thread Edward W. Rouse
3600 + ":" + a / 60 + ":" + a % 60) > -Original Message- > From: Steve Crawford [mailto:scrawf...@pinpointresearch.com] > Sent: Wednesday, February 08, 2012 3:26 PM > To: Edward W. Rouse > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] time inter

Re: [SQL] time interval math

2012-02-08 Thread Steve Crawford
On 02/08/2012 12:01 PM, Edward W. Rouse wrote: I'm still working on getting this to work, but the summary is this: I am getting several (many) intervals of hour, minutes and seconds. I need a sum of the absolute value these intervals, similar to the SUM(ABS()) function for numbers; and I need to

[SQL] time interval math

2012-02-08 Thread Edward W. Rouse
I'm still working on getting this to work, but the summary is this: I am getting several (many) intervals of hour, minutes and seconds. I need a sum of the absolute value these intervals, similar to the SUM(ABS()) function for numbers; and I need to divide this sum by an integer (bigint). Getting

Re: [SQL] Time of executed query

2007-03-06 Thread Rodrigo De León
On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequi

Re: [SQL] Time of executed query

2007-03-06 Thread Rodrigo De León
On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequi

Re: [SQL] Time of executed query

2007-03-06 Thread Shoaib Mir
EXPLAIN ANALYZE does give the query execution time at the end of its output. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: EXPLAIN ANALYZE Is perfect but i have no idea of how to use it. My resultset is retrieving my rows.

Re: [SQL] Time of executed query

2007-03-06 Thread Bart Degryse
EXPLAIN ANALYZE is only for tuning purposes. Read the manual (http://www.postgresql.org/docs/8.1/interactive/sql-explain.html) to get more insight >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-06 14:55 >>> EXPLAIN ANALYZE Is perfect but i have no idea of how to use it. My results

Re: [SQL] Time of executed query

2007-03-06 Thread Ezequias Rodrigues da Rocha
EXPLAIN ANALYZE Is perfect but i have no idea of how to use it. My resultset is retrieving my rows. Another question that cames with the Analyze. PgAdmin return the time to retrieve the data or all time to fill the grid on the "SQL Editor". -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blo

Re: [SQL] Time of executed query

2007-03-06 Thread Bart Degryse
Assuming you're using stored procedures... Start procedure with logging current time, name of procedure,... Perform whatever must be performed End procedure with logging current time, name of procedure,... Query logging table to get time spent Alternative, still assuming you're using stored proce

Re: [SQL] Time of executed query

2007-03-06 Thread Shoaib Mir
Did you try EXPLAIN ANALYZE ? --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/6/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: I preffer that the database give me this information. I don't know if it is possible becouse if we retrieve many rows and if we want to put this re

Re: [SQL] Time of executed query

2007-03-06 Thread Ezequias Rodrigues da Rocha
I preffer that the database give me this information. I don't know if it is possible becouse if we retrieve many rows and if we want to put this result in a new column the same time will be replicated many times and consuming more processing. Any other suggestion ? Ezequias 2007/3/6, Bart Degrys

Re: [SQL] Time of executed query

2007-03-06 Thread Bart Degryse
note the time just before your operation starts note the time just after it ends show timeafter - timebefore >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-06 14:20 >>> Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my appl

[SQL] Time of executed query

2007-03-06 Thread Ezequias Rodrigues da Rocha
Hi list, It is possible to retrieve the time of a SQL statement leads to execute ? I would like to put in my application how much time each operation leads to finish. Any suggestion ? -- Ezequias Rodrigues da Rocha http://ezequiasrocha.blogspot.com/ use Mozilla Firefox:http://br.mozdev.org/fire

Re: [SQL] Time interval sums

2006-10-02 Thread Tom Lane
"A. R. Van Hook" <[EMAIL PROTECTED]> writes: > How does one sum interval sums? > the following does not work: > select sum(stop-start::interval) as total from startstop Define "does not work"? What did you get, what were you hoping for? Personally I'd drop the unnecessary cast, but AFAICS there

[SQL] Time interval sums

2006-10-02 Thread A. R. Van Hook
How does one sum interval sums? the following does not work: select sum(stop-start::interval) as total from startstop where sid= 545 and 2006 = date_part('year', day) and 9 = date_part('month', day) however se

Re: [SQL] time

2005-11-10 Thread Michael Fuhr
On Thu, Nov 10, 2005 at 08:29:07PM +0100, Tadej Kanizar wrote: > I have a table with a column of type TIMESTAMP. > In output, I need to format it.. what's the best way to do it? > So, for instance, how could I format it so that it would output as YY-MM-DD > HH:MM? See to_char() in the "Data Type F

Re: [SQL] time

2005-11-10 Thread Tadej Kanizar
PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: 10. november 2005 20:14 To: Michael Fuhr Cc: Rod Taylor; Judith Altamirano Figueroa; pgsql-sql@postgresql.org Subject: Re: [SQL] time Michael Fuhr <[EMAIL PROTECTED]> writes: > Am I missing something? Is there a reason not

Re: [SQL] time

2005-11-10 Thread Michael Fuhr
On Thu, Nov 10, 2005 at 02:13:43PM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > Am I missing something? Is there a reason not to simply cast the > > timestamp value to time? > > I think the OP was trying to use the functional cast syntax > time(now()) > which worked

Re: [SQL] time

2005-11-10 Thread Tom Lane
Michael Fuhr <[EMAIL PROTECTED]> writes: > Am I missing something? Is there a reason not to simply cast the > timestamp value to time? > test=> select now()::time; >now > - > 11:19:19.892125 > (1 row) > test=> select cast(now() as time); >now >

Re: [SQL] time

2005-11-10 Thread Michael Fuhr
[Please copy the mailing list on replies.] On Thu, Nov 10, 2005 at 12:24:17PM -0600, Judith Altamirano Figueroa wrote: > excuse me and how can I just get the hour, minute and second The time type takes an optional precision: test=> select now()::time; now - 11:36:3

Re: [SQL] time

2005-11-10 Thread Rod Taylor
On Thu, 2005-11-10 at 11:21 -0700, Michael Fuhr wrote: > On Thu, Nov 10, 2005 at 01:11:27PM -0500, Rod Taylor wrote: > > rbt=# select cast(now() - date_trunc('day', now()) as time); > > time > > - > > 13:10:42.495579 > > (1 row) > > Am I missing something? Is there a reason

Re: [SQL] time

2005-11-10 Thread Michael Fuhr
On Thu, Nov 10, 2005 at 01:11:27PM -0500, Rod Taylor wrote: > rbt=# select cast(now() - date_trunc('day', now()) as time); > time > - > 13:10:42.495579 > (1 row) Am I missing something? Is there a reason not to simply cast the timestamp value to time? test=> select cast(no

Re: [SQL] time

2005-11-10 Thread Rod Taylor
On Thu, 2005-11-10 at 09:03 -0600, Judith Altamirano Figueroa wrote: > Hello everybody!!!, I'd like to know if there another way to get the > time from a timestamp type, because in earliest versions I just get > the time like time(fecha) and now this returns an error, thanks in > advanced!!! r

[SQL] time

2005-11-10 Thread Judith Altamirano Figueroa
Hello everybody!!!, I'd like to know if there another way to get the time from a timestamp type, because in earliest versions I just get the time like time(fecha) and now this returns an error, thanks in advanced!!!

Re: [SQL] time

2005-11-10 Thread Judith Altamirano Figueroa
Hello everybody!!!, I'd like to know if there another way to get the time from a timestamp type, because in earliest versions I just get the time like time(fecha) and now this returns an error, thanks in advanced!!!

Re: [SQL] Time differences between rows, not columns?

2005-08-30 Thread Anthony Molinaro
To: Anthony Molinaro; pgsql-sql@postgresql.org Subject: RE: [SQL] Time differences between rows, not columns? Importance: High Thanks! Amit,You say "I want to know how much time it took for a bill to be accepted after it was submitted" So, do you want between 10 and 40, not 10 and 20

Re: [SQL] Time differences between rows, not columns?

2005-08-30 Thread Amit_Wadhwa
query? Or the max() query? Thanks a lot for your time! Regards, Amit -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Molinaro Sent: Tuesday, August 30, 2005 4:59 PM To: Wadhwa, Amit; pgsql-sql@postgresql.org Subject: Re: [SQL] Time differences

Re: [SQL] Time differences between rows, not columns?

2005-08-30 Thread Anthony Molinaro
I am concerned about is the technique. hope that helps, Anthony -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 5:45 AM To: pgsql-sql@postgresql.org Subject: [SQL] Time differences between rows, not colu

[SQL] Time differences between rows, not columns?

2005-08-30 Thread Amit_Wadhwa
Using postgresSQL 8.0 for windows, running on windows 2003 server 16gb RAM, 3Ghz dual p4. Language: Java - JDBC postgres driver: postgresql-8.0-310.jdbc3.jar I have an application with 3 tables (in this context that is) Table bills bill_id NOT NULL serial ... And other columns Table bill

Re: [SQL] time of constraint checking

2004-10-20 Thread Stephan Szabo
On Wed, 20 Oct 2004, Markus Bertheau wrote: > http://www.postgresql.org/docs/current/static/sql-createtable.html says, > down at the explanation of DEFERRABLE, that constraints are checked > after every command. Why does the following not work then: > > CREATE TABLE foo ( > pos INT UNIQUE > );

Re: [SQL] time of constraint checking

2004-10-20 Thread Markus Bertheau
Ð ÐÑÐ, 20.10.2004, Ð 17:58, Markus Bertheau ÐÐÑÐÑ: > I also noticed, that the docs don't state whether INITIALLY IMMEDIATE or > INITIALLY DEFERRED is the default. I just overlooked that, sorry, it is stated. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)-

[SQL] time of constraint checking

2004-10-20 Thread Markus Bertheau
Hi, http://www.postgresql.org/docs/current/static/sql-createtable.html says, down at the explanation of DEFERRABLE, that constraints are checked after every command. Why does the following not work then: CREATE TABLE foo ( pos INT UNIQUE ); INSERT INTO foo (pos) VALUES (1); INSERT INTO foo

Re: [SQL] TIME ZONE SQL

2004-02-05 Thread Tom Lane
"Raman Garg" <[EMAIL PROTECTED]> writes: > Actually my "between" is creating some problems and is not giving me results > so what I have done is . IN MY WHERE CLAUSE OF QUERY: I suspect that this revised clause will give you problems too, namely selecting rows you don't want. I think what may act

Re: [SQL] TIME ZONE SQL

2004-02-05 Thread Richard Huxton
On Thursday 05 February 2004 14:59, Raman Garg wrote: > Hi Richard, > > What I am having is > > CREATE TABLE "customer_events" ( > "event_id" numeric (10) NOT NULL, > "customer_id" numeric (10) NOT NULL, > "event_name" varchar (100) , > "event_datetime" date , > "start_time" time , > "repeat_untill

Re: [SQL] TIME ZONE SQL

2004-02-05 Thread Raman Garg
one(-7:00) Well, it worked for me now.. maybe some logic of neagative time zone is there due to which our time calculation make the difference of two time greater. :-? Thanks for your descriptive and nice explanation... Regards, -- Raman - Original Message - From: "Richard Huxton" &l

Re: [SQL] TIME ZONE SQL

2004-02-05 Thread Richard Huxton
On Thursday 05 February 2004 08:28, Raman wrote: > Hi Richard, > Follwing are the Results that I get > WHEN I run "between" query like > > ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN > (start_time::time - send_before_time::time) > and start_time::time) I think the issue i

Re: [SQL] TIME ZONE SQL

2004-02-05 Thread Raman
an Garg -- Raman - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Raman" <[EMAIL PROTECTED]>; "pgsql-sql" <[EMAIL PROTECTED]> Sent: Thursday, February 05, 2004 1:33 AM Subject: Re: [SQL] TIME ZONE SQL > On Wed

Re: [SQL] TIME ZONE SQL

2004-02-04 Thread Richard Huxton
On Wednesday 04 February 2004 17:57, Raman wrote: > > This query runs fine when i have > time_difference value like +5:30 +5:00 i.e. works fine for positive values > but failes for negative values i.e. -7:00, -6:00 etc > > I don't know WHY WHY... pls help > I am helpless. Can you give example outp

[SQL] TIME ZONE SQL

2004-02-04 Thread Raman
Hi Pls see this Query I have following fields in my table "customer_events" a) time_difference (which has values like -05:00 , +05:30, +00:00 etc) b) start_time (has value like 11:05, 10:00 etc) c) send_before_time (has value like 00:05, 00:10 etc) select ((CURRENT_TIME(0) AT TIME ZONE "interva

Re: [SQL] time series data

2004-01-29 Thread azwa
help me to solve the above problem . thanks in advance. Tomasz Myrta <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/27/2004 10:56 AM CET         To:        [EMAIL PROTECTED]         cc:        [EMAIL PROTECTED]         Subject:        Re: [SQL] time series data Dnia 2004-01-2

Re: [SQL] time series data

2004-01-27 Thread Tomasz Myrta
Dnia 2004-01-27 02:42, Użytkownik [EMAIL PROTECTED] napisał: Hi, thanks for the info..btw can u pls explain a little bit detail since i can't get thru yr solution. thanks in advance insert into time_table (time_key,year_id,month_id,month_desc,day_id) select newid, extract('year' from you

Re: [SQL] time series data

2004-01-27 Thread azwa
    cc:                 Subject:        [SQL] time series data Hi,    i want to create time table  & the structure as below :                 Column  Type  Modifiers time_key yr_id month_id month_desc day_id  integer integer integer text integer  not null default n

Re: [SQL] time series data

2004-01-21 Thread Tomasz Myrta
Dnia 2004-01-21 10:37, Użytkownik Tomasz Myrta napisał: Use this integer sequence and interval datatype to get date result: your_date='1994-01-01'::date+'1 day'::integer * time_key ^^^ Sorry, use interval here. Regards, Tomasz Myrta -

Re: [SQL] time series data

2004-01-21 Thread Tomasz Myrta
Dnia 2004-01-21 09:53, Użytkownik [EMAIL PROTECTED] napisał: time_key integer not null default nextval('time_seq'::text) > Example of data in time table: > Time_keyyr_idmonth_idmonth_desc day_id > 1999 1 Jan 1 > 1999 1 Jan 2 > 2000

[SQL] time series data

2004-01-21 Thread azwa
Hi,     i want to create time table  & the structure as below :                 Column  Type  Modifiers time_key yr_id month_id month_desc day_id  integer  integer  integer  text  integer  not null default nextval('time_seq'::text) Example of data in time table: Time_key  yr_id   mon

Re: [SQL] time precision.

2003-07-30 Thread Stephan Szabo
On Wed, 30 Jul 2003, Anagha Joshi wrote: > Hi All, > I'm using PG-7.2.4 on Solaries. > When I do: > > template1=# select time(6576); > ERROR: TIME(6576) precision must be between 0 and 13 > > Where am I wrong? IIRC time(n) refers to the type time with a precision of n and n is limited as stated

Re: [SQL] time precision.

2003-07-30 Thread Christoph Haller
> I'm using PG-7.2.4 on Solaries. > When I do: > > template1# select time(6576); > ERROR: TIME(6576) precision must be between 0 and 13 > > Where am I wrong? What's the purpose of this function? And where did you find it in the docs? I didn't see it before. Regards, Christoph

[SQL] time precision.

2003-07-30 Thread Anagha Joshi
Hi All, I’m using PG-7.2.4 on Solaries. When I do:   template1=# select time(6576); ERROR:  TIME(6576) precision must be between 0 and 13   Where am I wrong? Pls. help   Thanks, Anagha

Re: [SQL] time delay function

2003-07-23 Thread Robert Treat
On Tue, 2003-07-22 at 04:22, Christoph Haller wrote: > > > > Pseudo code: > > > > begin trans > > select * from table1 > > WAIT FOR 20 SECS > > update table1 set blah = 'blah' > > end transcation > > > > In pgplsql, Im looking for something like a function that I can use to > make the process to wa

Re: [SQL] time delay function

2003-07-22 Thread Peter Eisentraut
Girish Bajaj writes: > In pgplsql, Im looking for something like a function that I can use to > make the process to wait for 20 secs before continuing to execute the > next sql statment? There is no built-in support for that, but you could write your own function in C that accomplishes that, for

Re: [SQL] time delay function

2003-07-22 Thread Christoph Haller
> > Pseudo code: > > begin trans > select * from table1 > WAIT FOR 20 SECS > update table1 set blah = 'blah' > end transcation > > In pgplsql, Im looking for something like a function that I can use to make the process to wait for 20 secs before con tinuing to execute the next sql statment? > AFAIK

[SQL] time delay function

2003-07-22 Thread Girish Bajaj
Pseudo code: begin trans select * from table1 WAIT FOR 20 SECS update table1 set blah = 'blah' end transcation In pgplsql, Im looking for something like a function that I can use to make the process to wait for 20 secs before continuing to execute the next sql statment? Thanks, Girish -

Re: [SQL] time date interval...

2003-07-12 Thread Bruno Wolff III
On Sat, Jul 12, 2003 at 09:00:19 +0200, Erik Thiele <[EMAIL PROTECTED]> wrote: > hi > > I am having problems with understanding of date/time/interval handling > in postgresql and sql in general. > > a,b are TIMESTAMP WITHOUT TIME ZONE > c is INTERVAL > > mathematics tell me: > > a-b = (a+c) -

[SQL] time date interval...

2003-07-12 Thread Erik Thiele
hi I am having problems with understanding of date/time/interval handling in postgresql and sql in general. a,b are TIMESTAMP WITHOUT TIME ZONE c is INTERVAL mathematics tell me: a-b = (a+c) - (b+c) is this also true in sql? if for example c is "1 year", then depending on the value of a and b

Re: [SQL] TIME vs. TIMESTAMP data type

2003-02-06 Thread Julian Scarfe
On 6/2/03 11:04, "Ludwig Lim" <[EMAIL PROTECTED]> wrote: > Are there cases when a TIME data type is a better > choice over the TIMESTAMP data type? Surely this depends on the nature of the data that you want to represent? If you're researching into sleep patterns and want to represent the time

Re: [SQL] TIME vs. TIMESTAMP data type

2003-02-06 Thread Ludwig Lim
Hi Tomasz: --- Tomasz Myrta <[EMAIL PROTECTED]> wrote: > > Probably you are right, but you can cast into > timestamp before using these functions. > Do you really need to care amount of storage? I was just thinking if both TIMESTAMP and TIME have use the same amount of space (I was think TIME

Re: [SQL] TIME vs. TIMESTAMP data type

2003-02-06 Thread Tomasz Myrta
Ludwig Lim wrote: Hi: Are there cases when a TIME data type is a better choice over the TIMESTAMP data type? It seems that PostgreSQL (I'm using 7.2.3) encourage its users to use TIMESTAMP over TIME data type. I said this because of the following: a) More functions for DATE and TIMES

[SQL] TIME vs. TIMESTAMP data type

2003-02-06 Thread Ludwig Lim
Hi: Are there cases when a TIME data type is a better choice over the TIMESTAMP data type? It seems that PostgreSQL (I'm using 7.2.3) encourage its users to use TIMESTAMP over TIME data type. I said this because of the following: a) More functions for DATE and TIMESTAMP data types su

Re: [SQL] Time difference without intervals

2002-11-10 Thread Tom Lane
Roberto Mello <[EMAIL PROTECTED]> writes: > On Fri, Nov 08, 2002 at 04:11:05PM -0500, Tom Lane wrote: >> I think your complaint is not that you get an interval, but that you >> want it displayed differently. See to_char() ... > It was not a complaint in any way. I apologize if I came across like

Re: [SQL] Time difference without intervals

2002-11-10 Thread Roberto Mello
On Fri, Nov 08, 2002 at 04:11:05PM -0500, Tom Lane wrote: > Roberto Mello <[EMAIL PROTECTED]> writes: > > Is there any way to make a timestamp difference operation not return an > > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > > or whatnot. > > I think your complain

Re: [SQL] Time difference without intervals

2002-11-09 Thread Bruno Wolff III
On Fri, Nov 08, 2002 at 16:11:05 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Roberto Mello <[EMAIL PROTECTED]> writes: > > Is there any way to make a timestamp difference operation not return an > > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > > or whatnot. > > I

Re: [SQL] Time difference without intervals

2002-11-08 Thread Tom Lane
Roberto Mello <[EMAIL PROTECTED]> writes: > Is there any way to make a timestamp difference operation not return an > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > or whatnot. I think your complaint is not that you get an interval, but that you want it displayed diff

Re: [SQL] Time difference without intervals

2002-11-08 Thread Bruno Wolff III
On Fri, Nov 08, 2002 at 11:20:02 -0700, Roberto Mello <[EMAIL PROTECTED]> wrote: > > Is there any way to make a timestamp difference operation not return an > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > or whatnot. When you take a difference of two timestamps, y

[SQL] Time difference without intervals

2002-11-08 Thread Roberto Mello
I saw this question on another PG mailing list, and I don't know how to answer. I've tried casting the interval to a timestamp, but that doesn't seem to be supported in 7.2. Does anyone have an answer? Is there any way to make a timestamp difference operation not return an interval? I'd like to ge

[SQL] time limit of a database connection

2002-10-30 Thread jack
I use a window client to conncet to a postgreSQL server with windows ODBC. It seems if I haven't done anything on the connection for hours, the connection will be dropped. Is that possible to set the time limit of a connection? Thank you in advance! Jack ---(end of broadc

[SQL] time difference

2002-06-27 Thread q u a d r a
I have a query that returns ?column? - 6 days 01:56:14 (1 row) How can I get it as total number of hours? Instead of days and hours, I need hours only (all in all, so 24hours * 6 days + 01:56:14) Can you help me please? Thanks ---(end of bro

[SQL] time difference

2001-05-25 Thread Seema Noor
  i want to calculate the session time. my query is thisselect now() - '2001-05-19 12:09:00+5;it gives the difference in date and difference in time separately. if thecurrent time is greater than the second time then it gives simply theirdifference but i want to calcutate the session time f

[SQL] Time penalty on VIEWS on VIEWS

2000-11-05 Thread hubert depesz lubaczewski
Hi, My situation: VIEW A is grouping information from 2 tables (B,C). I have to make another VIEW with additional WHERE statement compared in VIEW A. I can make this as a VIEW groupping tables B and C, or makeing VIEW of VIEW A. I know that second sollution is more flexible but there should be so

[SQL] Time Results Conversion

2000-08-27 Thread Brian C. Doyle
Hello all, I have now upgraded to 7.0.2 and am very pleased with it. I do have a question about a result that I am getting. When I sum an interval field will get "1 01:01:01" representing "25 hours 1 minute 1 second" The result that I need is just the sum of the hours minutes and se

Re: [SQL] Time Help

2000-08-22 Thread Brian C. Doyle
In Conclusion: 7.0.2 by default outputs the time span as 00:00:00 no matter what format you put in. There is no need to convert it! Thanks to Mark for all your help!! At 11:03 AM 8/22/00 -0400, Mark Volpe wrote: >SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs'::timespan; > > ?column? >---

Re: [SQL] Time Help

2000-08-22 Thread Mark Volpe
SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs'::timespan; ?column? -- 02:10:06 Mark "Brian C. Doyle" wrote: > > Mark, > > On your 7.0 box would you do: > > SELECT '0:00:00'::time + '@ 2 hours 10 mins 6 secs':timespan; > > For me and see if it will convert it! Need to decide

Re: [SQL] Time Help

2000-08-22 Thread Mark Volpe
I tried it on a box with postgres 6.5.3 and I got the result you did. On postgres 7.0 I get 02:10:06 for both results. You should upgrade to 7.0 - it is _much_ better. Mark "Brian C. Doyle" wrote: > > Mark, > > I tried that and had to change it to: > > SELECT '0:00:00'::timespan + '02:10:06':

Re: [SQL] Time Help

2000-08-22 Thread Mark Volpe
I'm not sure at all what you are asking, but I'm thinking you're trying to convert a "timespan" to a "time". Try adding it to a time like this: SELECT '0:00:00'::time + '02:10:06'::timespan; Mark "Brian C. Doyle" wrote: > > Hello all, > > I have a query result of @ 2 hours 10 mins 6 secs and I

Re: [SQL] Time Help

2000-08-22 Thread Brian C. Doyle
Mark, I tried that and had to change it to: SELECT '0:00:00'::timespan + '02:10:06'::timespan; To get any response. the response i got was @ 2 hours 10 mins 6 secs Still in the wrong format If is use : SELECT '0:00:00'::time + '02:10:06'::timespan; It get No such function 'time_timespan'

[SQL] Time Help

2000-08-22 Thread Brian C. Doyle
Hello all, I have a query result of @ 2 hours 10 mins 6 secs and I would like to change that to 02:10:06. Currently the field is listed as "timespan" This allows me to input date as 02:10:06 or 4h 10m 6s or even 2hrs 10 min 6 sec which are all the formats that I will be entering the time for

Re: [SQL] Time Aggregates

2000-08-02 Thread David Lloyd-Jones
"Thomas Lockhart" <[EMAIL PROTECTED]> replied to: "Itai Zukerman" <[EMAIL PROTECTED]> > > I'm currently doing this: > > SELECT symbol, date_trunc('minute', posted), > > min(price), max(price), avg(price) > > FROM trade > > GROUP BY symbol, date_trunc('minute', posted); > > to get a

Re: [SQL] Time Aggregates

2000-08-02 Thread Itai Zukerman
> > SELECT symbol, date_trunc('minute', posted), > > min(price), max(price), avg(price) > > FROM trade > > GROUP BY symbol, date_trunc('minute', posted); Hmmm... I'm not sure how to go about doing this for, say, 5 minute intervals. Basically, I want a function: date_round( time

Re: [SQL] Time Aggregates

2000-08-02 Thread Thomas Lockhart
> I'm currently doing this: > SELECT symbol, date_trunc('minute', posted), > min(price), max(price), avg(price) > FROM trade > GROUP BY symbol, date_trunc('minute', posted); > to get a list of minute-averages of trade prices. I get the feeling > that this is bad form, that I should

[SQL] Time Aggregates

2000-08-02 Thread Itai Zukerman
Hi, I'm currently doing this: SELECT symbol, date_trunc('minute', posted), min(price), max(price), avg(price) FROM trade GROUP BY symbol, date_trunc('minute', posted); to get a list of minute-averages of trade prices. I get the feeling that this is bad form, that I should be doi