Postgres Pro
Downloads
Home   >   mailing lists

Thread: timeofday() and now() issue..

timeofday() and now() issue..

From
Rajesh Kumar Mallah
Date:

SELECT CAST (timeofday() as timestamp with time zone) - CAST (now() as timestamp with time zone);
+-----------------+
|    ?column?     |
+-----------------+
| 03:30:00.000295 |
+-----------------+

shudn't it return something close to 0 ?

like
SELECT CAST (timeofday() as timestamp ) - CAST (now() as timestamp with time zone);
+-----------------+
|    ?column?     |
+-----------------+
| 00:00:00.000405 |
+-----------------+



regds
Mallah



-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

Re: timeofday() and now() issue..

From
Tom Lane
Date:
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> SELECT CAST (timeofday() as timestamp with time zone) - CAST (now() as timestamp with time zone);
> shudn't it return something close to 0 ?

Works for me ... what's your platform, PG version, and timezone?
And what are you getting from the intermediate steps in that expression?
        regards, tom lane

Re: timeofday() and now() issue..

From
Rajesh Kumar Mallah
Date:
On Friday 02 May 2003 8:27 pm, Tom Lane wrote:
> Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> > SELECT CAST (timeofday() as timestamp with time zone) - CAST (now() as
> > timestamp with time zone); shudn't it return something close to 0 ?
>
> Works for me ... what's your platform, PG version, and timezone?
> And what are you getting from the intermediate steps in that expression?
>
>             regards, tom lane


Dunno if details given below would suffice,
please lemme know if you want anything specific

Regds
mallah.

tradein_clients=# SELECT version();
+-------------------------------------------------------------+
|                           version                           |
+-------------------------------------------------------------+
| PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96 |
+-------------------------------------------------------------+
(1 row)

Time: 2183.62 ms


tradein_clients=#  SELECT CAST (timeofday() as timestamp with time zone) ;
+----------------------------------+
|            timeofday             |
+----------------------------------+
| 2003-05-03 00:02:25.386096+05:30 |
+----------------------------------+
(1 row)

Time: 2537.58 ms
tradein_clients=#
tradein_clients=#  SELECT CAST ( now() as timestamp with time zone) ;
+----------------------------------+
|               now                |
+----------------------------------+
| 2003-05-02 20:32:34.564175+05:30 |
+----------------------------------+
(1 row)

Time: 673.78 ms
tradein_clients=#  SELECT timeofday();
+-------------------------------------+
|              timeofday              |
+-------------------------------------+
| Fri May 02 20:34:13.634355 2003 IST |
+-------------------------------------+
(1 row)

Time: 647.73 ms








-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

Re: timeofday() and now() issue..

From
Tom Lane
Date:
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> tradein_clients=#  SELECT timeofday();
> +-------------------------------------+
> |              timeofday              |
> +-------------------------------------+
> | Fri May 02 20:34:13.634355 2003 IST |
> +-------------------------------------+

Hmm.  Looking in datetime.c, I see that Postgres believes "IST" is
Israel Standard Time, GMT+2.  So that explains the discrepancy,
if you think it's GMT+5.5.  What exactly are you doing to set the
system timezone?
        regards, tom lane

Re: timeofday() and now() issue..

From
Rajesh Kumar Mallah
Date:

I *think* i do this in Redhat Linux.
TZ='Asia/Calcutta'; export TZ

Yeah *MANY A TIMES* i felt like raising this 
issue that IST is indian standard times not
Israel Standart Time as mentioned in 
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=x11875.html

hmm ignore my ignorance  , i am not knowledgable
abt timezones.

regds
mallah.


On Friday 02 May 2003 8:46 pm, Tom Lane wrote:
> Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> > tradein_clients=#  SELECT timeofday();
> > +-------------------------------------+
> >
> > |              timeofday              |
> >
> > +-------------------------------------+
> >
> > | Fri May 02 20:34:13.634355 2003 IST |
> >
> > +-------------------------------------+
>
> Hmm.  Looking in datetime.c, I see that Postgres believes "IST" is
> Israel Standard Time, GMT+2.  So that explains the discrepancy,
> if you think it's GMT+5.5.  What exactly are you doing to set the
> system timezone?
>
>             regards, tom lane

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

Re: timeofday() and now() issue..

From
"A.Bhuvaneswaran"
Date:
> I *think* i do this in Redhat Linux.
> TZ='Asia/Calcutta'; export TZ
> 
> Yeah *MANY A TIMES* i felt like raising this 
> issue that IST is indian standard times not
> Israel Standart Time as mentioned in 
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=x11875.html

Finally, what is the conclusion? Can't we use timeofday in IST, indian 
standard time (+5:30)?

regards,
bhuvaneswaran

Re: timeofday() and now() issue..

From
Rajesh Kumar Mallah
Date:
Dear Tom,

Can you please tell us if its something 
to be fixed in PostgreSQL or in our systems
configurations ?

Becoz this probelm will keep hauting all
ppl who have set their Tz to Indian Standard Time.

Btw Is there no international standard on it?

Regds
Mallah.

On Saturday 03 May 2003 10:43 am, A.Bhuvaneswaran wrote:
> > I *think* i do this in Redhat Linux.
> > TZ='Asia/Calcutta'; export TZ
> >
> > Yeah *MANY A TIMES* i felt like raising this
> > issue that IST is indian standard times not
> > Israel Standart Time as mentioned in
> > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=x11875.ht
> >ml
>
> Finally, what is the conclusion? Can't we use timeofday in IST, indian
> standard time (+5:30)?
>
> regards,
> bhuvaneswaran

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

Re: timeofday() and now() issue..

From
Rajesh Kumar Mallah
Date:
On Friday 02 May 2003 8:46 pm, Tom Lane wrote:
> Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> > tradein_clients=#  SELECT timeofday();
> > +-------------------------------------+
> >
> > |              timeofday              |
> >
> > +-------------------------------------+
> >
> > | Fri May 02 20:34:13.634355 2003 IST |
> >
> > +-------------------------------------+
>
> Hmm.  Looking in datetime.c, I see that Postgres believes "IST" is
> Israel Standard Time, GMT+2.  So that explains the discrepancy,
> if you think it's GMT+5.5.  What exactly are you doing to set the
> system timezone?


Tom 

Do i have to only change POS(8) to POS(22)
in line below in datetime.c ?
{"ist", TZ, POS(8)},            /* Israel */

so that IST is interpreted as Indian Time Zone?

so some other places too ?


Regds
mallah.



>
>             regards, tom lane

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.