Thread: Time Stamp

Hi All,

I want to get a "timestamp" of the queries that i run!
Is there a builtin command to do this/ does one need to write a
function/stored procedure!
Any pointers will help.

Thanks,
Hrishi

On Mon, 2005-03-21 at 09:29, Hrishikesh Deshmukh wrote:
> Hi All,
>
> I want to get a "timestamp" of the queries that i run!
> Is there a builtin command to do this/ does one need to write a
> function/stored procedure!
> Any pointers will help.

This is actually a pretty wide open question.  Do you want to know how
long it took to run the query, or when it ran?  Do you want a list of
all the times it ran, or just the latest?  Are you basically auditing db
access, or just checking to see how well it's running?

If you're just monitoring for performance et. al. then look at the
logging setting of log_min_duration_statement which tells the backend
how long a query needs to take to be logged.  you use the other settings
in the postgresql.conf file to force it to log every statement and its
duration.

Otherwise, you can use a trigger to force it to store the timestamp of
every row inserted, if that's what you need.  I think there's a basic
example in the online docs for server programming.  There are also many
examples posted to this list, so you could search the archives.

Hrishikesh Deshmukh wrote:
> Hi All,
>
> I want to get a "timestamp" of the queries that i run!
> Is there a builtin command to do this/ does one need to write a
> function/stored procedure!
> Any pointers will help.

You don't say where you want this "timestamp".

In psql look into "\timing" (see the man page)
To record this in the logs, see the configuration section of the manual,
specifically "Error Reporting and Logging"

--
   Richard Huxton
   Archonet Ltd

Hi,

I want to know how long did it take for my query to run!

Thanks,
Hrishi


On Mon, 21 Mar 2005 15:57:38 +0000, Richard Huxton <dev@archonet.com> wrote:
> Hrishikesh Deshmukh wrote:
> > Hi All,
> >
> > I want to get a "timestamp" of the queries that i run!
> > Is there a builtin command to do this/ does one need to write a
> > function/stored procedure!
> > Any pointers will help.
>
> You don't say where you want this "timestamp".
>
> In psql look into "\timing" (see the man page)
> To record this in the logs, see the configuration section of the manual,
> specifically "Error Reporting and Logging"
>
> --
>    Richard Huxton
>    Archonet Ltd
>

Hrishi,

Depending on what you are using the information for, you might want to think
about using EXPLAIN ANALYZE like:

EXPLAIN ANALYZE SELECT * FROM TEST_TABLE;

This will give you the output of the explain command (describing what the
query planner decided was the best plan) and the results of various timing
results.  It doesn't include the "Total" time, but it is pretty close.  If I
recall, you were interested in doing this from within R (from post in
another group).  In that specific situation, you can use R's timing
commands.  From within R, type:

 help.search('timing')
or
help.search('profile')

In general, though, it is probably most useful to get the timings for
queries from explain analyze, as it gives you a wealth of information that
you can then use to optimize the results.

Sean

----- Original Message -----
From: "Hrishikesh Deshmukh" <hdeshmuk@gmail.com>
To: "Richard Huxton" <dev@archonet.com>
Cc: "Postgresql-General" <pgsql-general@postgresql.org>
Sent: Monday, March 21, 2005 11:38 AM
Subject: Re: [GENERAL] Time Stamp


> Hi,
>
> I want to know how long did it take for my query to run!
>
> Thanks,
> Hrishi
>
>
> On Mon, 21 Mar 2005 15:57:38 +0000, Richard Huxton <dev@archonet.com>
> wrote:
>> Hrishikesh Deshmukh wrote:
>> > Hi All,
>> >
>> > I want to get a "timestamp" of the queries that i run!
>> > Is there a builtin command to do this/ does one need to write a
>> > function/stored procedure!
>> > Any pointers will help.
>>
>> You don't say where you want this "timestamp".
>>
>> In psql look into "\timing" (see the man page)
>> To record this in the logs, see the configuration section of the manual,
>> specifically "Error Reporting and Logging"
>>
>> --
>>    Richard Huxton
>>    Archonet Ltd
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>