From ramang@smartdatainc.com Wed Jan  28 05:23:59 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id EA548D1D461
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Jan 2004 05:23:59 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 18044-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Jan 2004 01:23:28 -0400 (AST)
Received: from smartdatainc.com (unknown [203.129.220.82])
	by svr1.postgresql.org (Postfix) with ESMTP id 00545D1CCA2
	for <pgsql-sql@postgresql.org>; Wed, 28 Jan 2004 01:23:25 -0400 (AST)
Received: from raman ([192.168.199.212]) by smartdatainc.com
 ([192.168.199.92])
	with SMTP (MDaemon.PRO.v6.5.2.R)
	for <pgsql-sql@postgresql.org>; Wed, 28 Jan 2004 10:52:36 +0530
Message-ID: <003c01c3e55e$54b12d50$d4c7a8c0@raman>
From: "Raman" <ramang@smartdatainc.com>
To: <pgsql-sql@postgresql.org>
Subject: Query TIME ZONE
Date: Wed, 28 Jan 2004 10:49:37 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0039_01C3E58C.6C5110A0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-MDRemoteIP: 192.168.199.212
X-Return-Path: ramang@smartdatainc.com
X-MDaemon-Deliver-To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=HTML_40_50,
	HTML_FONT_COLOR_RED
X-Spam-Level: 
X-Archive-Number: 200401/227
X-Sequence-Number: 16620

This is a multi-part message in MIME format.

------=_NextPart_000_0039_01C3E58C.6C5110A0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi All,
I have a query regarding Time Zone Interval

QUERY:
"select current_time AT TIME ZONE INTERVAL '+5:30'"=20
it is running fine

BUT
When I take the value of interval from DB table like:

"select current_time AT TIME ZONE INTERVAL time_difference from customer_ev=
ents"

here : time_difference - is my varchar column in the table customer_events.=
 This gives me errors " parse error at or near "time_difference" at charact=
er 43 "

I tried to type cast it time_difference::text etc but it in vain.

Pls help me ... its realy urgent.

Thanks in advance

Raman Garg






------=_NextPart_000_0039_01C3E58C.6C5110A0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi All,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I have a query regarding Time Zone=20
Interval</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>QUERY:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>"select current_time AT TIME ZONE INTERVAL=
 '+5:30'"=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>it is running fine</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>BUT</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>When I&nbsp;take the value of interval fro=
m DB=20
table like:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>"select current_time AT TIME ZONE INTERVAL=
=20
time_difference from customer_events"</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV>here : time_difference - is my varchar column in the table customer_ev=
ents.=20
This gives me errors "<FONT color=3D#ff0000> parse error at or near=20
"time_difference" at character 43 </FONT>"</DIV>
<DIV>&nbsp;</DIV>
<DIV>I tried to type cast it time_difference::text etc but it in vain.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Pls help me ... its realy urgent.</DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks in advance</DIV>
<DIV><STRONG></STRONG>&nbsp;</DIV>
<DIV><STRONG>Raman Garg</STRONG></DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV></FONT>&nbsp;</DIV></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0039_01C3E58C.6C5110A0--




From tgl@sss.pgh.pa.us Wed Jan  28 05:59:03 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 643F5D1B4DC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Jan 2004 05:59:03 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 29155-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Jan 2004 01:58:33 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 3AFA7D1B7E5
	for <pgsql-sql@postgresql.org>; Wed, 28 Jan 2004 01:58:32 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i0S5wWTX008029;
	Wed, 28 Jan 2004 00:58:32 -0500 (EST)
To: "Raman" <ramang@smartdatainc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query TIME ZONE 
In-reply-to: <003c01c3e55e$54b12d50$d4c7a8c0@raman> 
References: <003c01c3e55e$54b12d50$d4c7a8c0@raman>
Comments: In-reply-to "Raman" <ramang@smartdatainc.com>
	message dated "Wed, 28 Jan 2004 10:49:37 +0530"
Date: Wed, 28 Jan 2004 00:58:32 -0500
Message-ID: <8028.1075269512@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/228
X-Sequence-Number: 16621

"Raman" <ramang@smartdatainc.com> writes:
> [ okay: ]
> "select current_time AT TIME ZONE INTERVAL '+5:30'"
> [ not okay: ]
> "select current_time AT TIME ZONE INTERVAL time_difference from customer_events"

"TIME ZONE INTERVAL" is not a SQL construct.  You have misunderstood the
interaction of two different SQL constructs:
	timestamp AT TIME ZONE timezonespec
	INTERVAL 'interval-literal'
One of the possible forms of "timezonespec" in the AT TIME ZONE operator
is an interval value, so your first example works fine.  Your second
example does not work because the INTERVAL 'foo' construct is only for
simple literal constants.

> here : time_difference - is my varchar column in the table customer_events.

Why are you using varchar rather than an interval column?  An interval
value would work directly in this construct and would provide some
checking that entered values are sane.

If you are absolutely intent on using varchar as the column datatype,
you can do a run-time cast like this:
	select current_time AT TIME ZONE "interval"(time_difference) from customer_events
but don't complain when the query fails because some rows contain
time_difference strings that don't look like legal interval values...

			regards, tom lane



From ramang@smartdatainc.com Wed Jan  28 07:03:34 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 46723D1D2C7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Jan 2004 07:03:34 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 46781-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Jan 2004 03:03:04 -0400 (AST)
Received: from smartdatainc.com (s224.n22.vds2000.com [66.84.22.224])
	by svr1.postgresql.org (Postfix) with ESMTP id 7860CD1D549
	for <pgsql-sql@postgresql.org>; Wed, 28 Jan 2004 03:03:02 -0400 (AST)
Received: from raman ([203.129.220.82])
	by smartdatainc.com (8.12.9/8.11.0) with SMTP id i0S6DmRM010785;
	Wed, 28 Jan 2004 01:13:51 -0500
X-Authentication-Warning: smartdatainc.com: smartdat owned process doing -bs
Message-ID: <008c01c3e565$7c2baac0$d4c7a8c0@raman>
From: "Raman Garg" <ramang@smartdatainc.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
References: <003c01c3e55e$54b12d50$d4c7a8c0@raman>
	<8028.1075269512@sss.pgh.pa.us>
Subject: Re: Query TIME ZONE 
Date: Wed, 28 Jan 2004 11:39:23 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200401/230
X-Sequence-Number: 16623

Thanks Tom it worked  for me...
yes I got it wrong.. thanks for the correction.

thanks a lot..
-- Raman

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Raman" <ramang@smartdatainc.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, January 28, 2004 11:28 AM
Subject: Re: [SQL] Query TIME ZONE


> "Raman" <ramang@smartdatainc.com> writes:
> > [ okay: ]
> > "select current_time AT TIME ZONE INTERVAL '+5:30'"
> > [ not okay: ]
> > "select current_time AT TIME ZONE INTERVAL time_difference from
customer_events"
>
> "TIME ZONE INTERVAL" is not a SQL construct.  You have misunderstood the
> interaction of two different SQL constructs:
> timestamp AT TIME ZONE timezonespec
> INTERVAL 'interval-literal'
> One of the possible forms of "timezonespec" in the AT TIME ZONE operator
> is an interval value, so your first example works fine.  Your second
> example does not work because the INTERVAL 'foo' construct is only for
> simple literal constants.
>
> > here : time_difference - is my varchar column in the table
customer_events.
>
> Why are you using varchar rather than an interval column?  An interval
> value would work directly in this construct and would provide some
> checking that entered values are sane.
>
> If you are absolutely intent on using varchar as the column datatype,
> you can do a run-time cast like this:
> select current_time AT TIME ZONE "interval"(time_difference) from
customer_events
> but don't complain when the query fails because some rows contain
> time_difference strings that don't look like legal interval values...
>
> regards, tom lane
>