Horton

Thread

  1. How do we get the Client-Time and Server-Time from psql ?

    raghavendra t <raagavendra.rao@gmail.com> — 2010-06-02T09:48:20Z

    Hi All,

How do i get the client time and server time. I am connecting remotely. If i
give SELECT CURRENT_TIME;,it shows the server time. How do we get the client
time ?

Thanks in Advance

Regards
Raghavendra

  2. Re: How do we get the Client-Time and Server-Time from psql ?

    Raymond O'Donnell <rod@iol.ie> — 2010-06-02T09:56:26Z

    On 02/06/2010 10:48, raghavendra t wrote:
> Hi All,
> 
> How do i get the client time and server time. I am connecting
> remotely. If i give SELECT CURRENT_TIME;,it shows the server time.
> How do we get the client time ?

I don't think you can, from within a query anyway - it's always going to
run on the server side. You'll have to do it from within your application.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

  3. Re: How do we get the Client-Time and Server-Time from psql ?

    Stephen Frost <sfrost@snowman.net> — 2010-06-02T11:46:47Z

    * raghavendra t (raagavendra.rao@gmail.com) wrote:
> How do i get the client time and server time. I am connecting remotely. If i
> give SELECT CURRENT_TIME;,it shows the server time. How do we get the client
> time ?

uh, \! date 
?

	Stephen

  4. Re: How do we get the Client-Time and Server-Time from psql ?

    Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-02T17:36:41Z

    On Wednesday 02 June 2010 13.46:47 Stephen Frost wrote:
> * raghavendra t (raagavendra.rao@gmail.com) wrote:
> > How do i get the client time and server time. I am connecting remotely.
> > If i give SELECT CURRENT_TIME;,it shows the server time. How do we get
> > the client time ?
> 
> uh, \! date
> ?

Which is a feature of the psql cmdline frontend.  Unless Raghavendra either 
only browses the db wit psql or does ugly stuff like driving the psql via 
pipes, the \! escape is not available in applications using pg.
cheers
-- vbi

-- 
Today is Pungenday, the 7th day of Confusion in the YOLD 3176

  5. Re: How do we get the Client-Time and Server-Time from psql ?

    Steve Crawford <scrawford@pinpointresearch.com> — 2010-06-02T18:07:51Z

    On 06/02/2010 02:48 AM, raghavendra t wrote:
> Hi All,
>
> How do i get the client time and server time. I am connecting 
> remotely. If i give SELECT CURRENT_TIME;,it shows the server time. How 
> do we get the client time ?
>
Time is the same everywhere but displayed in different ways according to 
local time-zones. In order to deal with non-synchronized clients or 
server-side specific values like differentiating between CURRENT_TIME 
which returns the time at start of transaction throughout the life of 
the transaction and timeofday() which _does_ change throughout a 
transaction you should use "SET TIMEZONE". For example my server is on 
the West Coast but I can display time for East Coasters:


steve=# show timezone;
  TimeZone
-----------
  localtime

steve=# select current_time;
        timetz
--------------------
  11:06:14.745969-07

steve=# set timezone to 'est5edt';
SET
steve=# show timezone;
  TimeZone
----------
  EST5EDT

steve=# select current_time;
        timetz
--------------------
  14:06:29.456009-04

Cheers,
Steve

  6. Re: How do we get the Client-Time and Server-Time from psql ?

    raghavendra t <raagavendra.rao@gmail.com> — 2010-06-02T22:29:20Z

    Hi All,

Thank you for the update.

Suppose am at the server-end, how could i get the client-time. Its really
troublesome when compared with the timezone column in a table.

Regards
Raghavendra

On Wed, Jun 2, 2010 at 11:37 PM, Steve Crawford <
scrawford@pinpointresearch.com> wrote:

> On 06/02/2010 02:48 AM, raghavendra t wrote:
>
>> Hi All,
>>
>> How do i get the client time and server time. I am connecting remotely. If
>> i give SELECT CURRENT_TIME;,it shows the server time. How do we get the
>> client time ?
>>
>> Time is the same everywhere but displayed in different ways according to
> local time-zones. In order to deal with non-synchronized clients or
> server-side specific values like differentiating between CURRENT_TIME which
> returns the time at start of transaction throughout the life of the
> transaction and timeofday() which _does_ change throughout a transaction you
> should use "SET TIMEZONE". For example my server is on the West Coast but I
> can display time for East Coasters:
>
>
> steve=# show timezone;
>  TimeZone
> -----------
>  localtime
>
> steve=# select current_time;
>       timetz
> --------------------
>  11:06:14.745969-07
>
> steve=# set timezone to 'est5edt';
> SET
> steve=# show timezone;
>  TimeZone
> ----------
>  EST5EDT
>
> steve=# select current_time;
>       timetz
> --------------------
>  14:06:29.456009-04
>
> Cheers,
> Steve
>
>

  7. Re: How do we get the Client-Time and Server-Time from psql ?

    Steve Crawford <scrawford@pinpointresearch.com> — 2010-06-03T00:40:45Z

    On 06/02/2010 03:29 PM, raghavendra t wrote:
> ...
> Suppose am at the server-end, how could i get the client-time. Its 
> really troublesome when compared with the timezone column in a table.
>
Not sure what you are getting at. You are always connected to the server 
_through_ a client and you _tell_ the server your timezone preferences.

As long as you are using timestamptz and as long as the client properly 
sets its preferred time-zone prior to issuing queries or gives 
fully-qualified timestamps with TZ, then you are fine.

Alternately, you can use (for libpq clients) the PGTZ environment variable.

Or you can set things on a per-user basis:
alter user foo set timezone to 'SOMETZ';

Or for things like web-apps where the client-side of the connection to 
the database is probably through a single database-user and the actual 
users are all over the place you can set up a table of user-preferences 
and set the timezone appropriately.

Cheers,
Steve

  8. Re: How do we get the Client-Time and Server-Time from psql ?

    raghavendra t <raagavendra.rao@gmail.com> — 2010-06-04T17:50:12Z

    Thank you Very Much Steve.

On Thu, Jun 3, 2010 at 6:10 AM, Steve Crawford <
scrawford@pinpointresearch.com> wrote:

> On 06/02/2010 03:29 PM, raghavendra t wrote:
>
>> ...
>>
>> Suppose am at the server-end, how could i get the client-time. Its really
>> troublesome when compared with the timezone column in a table.
>>
>> Not sure what you are getting at. You are always connected to the server
> _through_ a client and you _tell_ the server your timezone preferences.
>
> As long as you are using timestamptz and as long as the client properly
> sets its preferred time-zone prior to issuing queries or gives
> fully-qualified timestamps with TZ, then you are fine.
>
> Alternately, you can use (for libpq clients) the PGTZ environment variable.
>
> Or you can set things on a per-user basis:
> alter user foo set timezone to 'SOMETZ';
>
> Or for things like web-apps where the client-side of the connection to the
> database is probably through a single database-user and the actual users are
> all over the place you can set up a table of user-preferences and set the
> timezone appropriately.
>
> Cheers,
> Steve
>
>